[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