From 349413a520d05cf9c55fe6870de6ea2f933a3248 Mon Sep 17 00:00:00 2001 From: Tom Hughes Date: Fri, 14 Sep 2007 11:16:25 +0000 Subject: [PATCH] Allow column names in area conditions to be qualified, and do so for the SWF queries which sometimes reference more than one table with columns named latitude and longitude. --- app/controllers/swf_controller.rb | 4 ++-- lib/osm.rb | 6 +++--- lib/quad_tile.rb | 6 +++--- 3 files changed, 8 insertions(+), 8 deletions(-) diff --git a/app/controllers/swf_controller.rb b/app/controllers/swf_controller.rb index b576aae5c..94a3f5b4c 100644 --- a/app/controllers/swf_controller.rb +++ b/app/controllers/swf_controller.rb @@ -51,14 +51,14 @@ class SwfController < ApplicationController " FROM gpx_files,gps_points "+ "WHERE gpx_files.id=gpx_id "+ " AND gpx_files.user_id=#{user.id} "+ - " AND "+OSM.sql_for_area(ymin,xmin,ymax,xmax)+ + " AND "+OSM.sql_for_area(ymin,xmin,ymax,xmax,"gps_points.")+ " AND (gps_points.timestamp IS NOT NULL) "+ "ORDER BY fileid DESC,ts "+ "LIMIT 10000" else sql="SELECT latitude*0.000001 AS lat,longitude*0.000001 AS lon,gpx_id AS fileid,UNIX_TIMESTAMP(timestamp) AS ts "+ " FROM gps_points "+ - "WHERE "+OSM.sql_for_area(ymin,xmin,ymax,xmax)+ + "WHERE "+OSM.sql_for_area(ymin,xmin,ymax,xmax,"gps_points.")+ " AND (gps_points.timestamp IS NOT NULL) "+ "ORDER BY fileid DESC,ts "+ "LIMIT 10000" diff --git a/lib/osm.rb b/lib/osm.rb index e5d3c7fbb..300d9fa7a 100644 --- a/lib/osm.rb +++ b/lib/osm.rb @@ -412,13 +412,13 @@ module OSM end # Return an SQL fragment to select a given area of the globe - def self.sql_for_area(minlat, minlon, maxlat, maxlon) - tilesql = QuadTile.sql_for_area(minlat, minlon, maxlat, maxlon) + def self.sql_for_area(minlat, minlon, maxlat, maxlon, prefix = nil) + tilesql = QuadTile.sql_for_area(minlat, minlon, maxlat, maxlon, prefix) minlat = (minlat * 1000000).round minlon = (minlon * 1000000).round maxlat = (maxlat * 1000000).round maxlon = (maxlon * 1000000).round - return "#{tilesql} AND latitude BETWEEN #{minlat} AND #{maxlat} AND longitude BETWEEN #{minlon} AND #{maxlon}" + return "#{tilesql} AND #{prefix}latitude BETWEEN #{minlat} AND #{maxlat} AND #{prefix}longitude BETWEEN #{minlon} AND #{maxlon}" end end diff --git a/lib/quad_tile.rb b/lib/quad_tile.rb index cb1b5a06e..6e4fb6d22 100644 --- a/lib/quad_tile.rb +++ b/lib/quad_tile.rb @@ -41,7 +41,7 @@ module QuadTile end end - def self.sql_for_area(minlat, minlon, maxlat, maxlon) + def self.sql_for_area(minlat, minlon, maxlat, maxlon, prefix) sql = Array.new single = Array.new @@ -49,11 +49,11 @@ module QuadTile if first == last single.push(first) else - sql.push("tile BETWEEN #{first} AND #{last}") + sql.push("#{prefix}tile BETWEEN #{first} AND #{last}") end end - sql.push("tile IN (#{single.join(',')})") if single.size > 0 + sql.push("#{prefix}tile IN (#{single.join(',')})") if single.size > 0 return "( " + sql.join(" OR ") + " )" end -- 2.43.2