[OSM-dev] Too many slow queries in db

David Earl david at frankieandshadow.com
Tue Sep 4 11:39:41 BST 2007


On 04/09/2007 11:21, Tom Hughes wrote:
> The slow queries will essentially all be along the lines of:
> 
>   SELECT ... FROM current_nodes
>   WHERE latitude BETWEEN ... AND ...
>     AND longitude BETWEEN ... AND ...


The way I addressed this problem in the name finder was to create a 
"region number". Each region is about 111km square: 1 degree of latitude 
  and gradually decreasing amounts of longitude away from the equator. 
The number of each region is 1000 * lat + f(lon), so given a lat,lon it 
is easy to get the region number. It is also easy to compute the 
neighbouring region numbers.

The key thing then is that the region number column can be given an 
index; queries by lat,lon then become much more efficient. In the above 
case you would, of course, have to further cull which nodes you want, 
but at least you have a much smaller set to start with. Queries become
   SELECT ... WHERE region = n1 OR region = n2 ...

A smaller region than I used might also be desirable (say 0.2 degrees of 
latitude).

David





More information about the dev mailing list