Eureka! was Re: [OSM-dev] SQL optimisation

[Nick Hill]

I have written a test suite which creates a random gpx field then runs
queries on it. With an initial test using just over 1 million points and
a 20x20 integer grid, returning a record set of around 800 points per
tile, I am seeing an improvement of ten fold. With a finer grained tile 
set, I may see even better results.

I will create a 10 million point pattern then run queries on that. I'll
post my perl test suite here.

Nick Hill wrote:
> 
> 
> 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 ...
>