[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