[OSM-dev] SQL optimisation

Nick Hill nick at nickhill.co.uk
Sat Apr 8 16:31:57 BST 2006



Nick Hill wrote:

> When querying the database, use a query like:
> select distinctrow latitude, longitude from gps_points where 
> latitude_int>(int(#{lat1}*100) and latitude_int<(int(#{lat2}*100) and 
> longitude_int>(int(#{lon1}*100) and longitude_int<(int(#{lon2}*100) and 
> latitude > #{lat1} and latitude < #{lat2} and longitude > #{lon1} and 
> longitude < #{lon2}
> 
When comparing latitude_int and longitude_int we are using whole numbers 
so need to either add/subtract 1 or use >= or <=

So the query becomes instead:
...latitude_int>(int(#{lat1}*100)-1) and 
latitude_int<(int(#{lat2}*100)+1) and longitude_int>(int(#{lon1}*100)-1) 
and longitude_int<(int(#{lon2}*100)+1) and ...




More information about the dev mailing list