[OSM-dev] Too many slow queries in db

Frederik Ramm frederik at remote.org
Tue Sep 4 11:55:27 BST 2007


Hi,

> 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.

Likewise in my history service where I need quick "give me nodes in  
area" lookups. I didn't even bother to change size away from the  
equator:

         my $bucket = (int($lat*2)+180) * 720 + int($lon*2) + 360;
         $nodeinsert->execute($id,int($lat*10000),int($lon*10000), 
$bucket);

On read, I have to compute the list of "buckets" and then make an SQL  
query with a "where bucket in(...)", plus of course the lat/lon  
limits themselves.

However this is really just a hack compared with proper two- 
dimensional indexing or the quadtiles thingie (note that the  
quadtiles approach does not only crate "tiles" on which you then have  
your classic lat/lon nodes - it can even make storing lat/lon  
completely obsolete).

Bye
Frederik

-- 
Frederik Ramm  ##  eMail frederik at remote.org  ##  N49°00.09' E008°23.33'






More information about the dev mailing list