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.

diff --git a/app/controllers/swf_controller.rb b/app/controllers/swf_controller.rb
index b576aae5c7c3242faba92f2fb7b5aa9d962a1be0..94a3f5b4ce1151a72cc49d923c88dabd2358ce64 100644 (file)
--- 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} "+
                                 " 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 "+
                                 "  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"
                                 "  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 e5d3c7fbb6b653172cf804bbe744be8021b39e52..300d9fa7a43224ac306d78098a41ccb2fafc7b78 100644 (file)
--- 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
   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
 
     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
   end
 end

diff --git a/lib/quad_tile.rb b/lib/quad_tile.rb
index cb1b5a06e1ee7886ce4ce23e7a8019ee399f9e11..6e4fb6d2248c243bfc15c245fa0e643501229abf 100644 (file)
--- a/lib/quad_tile.rb
+++ b/lib/quad_tile.rb
@@ -41,7 +41,7 @@ module QuadTile
     end
   end
 
     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
 
     sql = Array.new
     single = Array.new
 


@@ -49,11 +49,11 @@ module QuadTile
       if first == last
         single.push(first)
       else
       if first == last
         single.push(first)
       else

-        sql.push("tile BETWEEN #{first} AND #{last}")
+        sql.push("#{prefix}tile BETWEEN #{first} AND #{last}")

       end
     end
 
       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
 
     return "( " + sql.join(" OR ") + " )"
   end