[OSM-dev] Proposal: Database accelerator and dirty tile marker based on simple algorithm.
ranaldo at unina.it
Wed Sep 20 16:30:29 BST 2006
> We have to be careful here - steve said that applying MySQL spatial indexes
> to the existing lat,lon lookups didn't make it better, which isn't a
> surprise. Adding them to a tile address may (or may not - I'd have thought
> a B-Tree might actually be ideal, but you never know till you actually try)
> speed it up.
I imported a recent planet.osm in a postgresql db, added a box geometric
object to the nodes table, and an rtree index on it. Latitude and longitude
are indexed as btree. Some query and times:
number of nodes=13927740
time psql osm osm -c "select count(*) from nodes where lon >=0 and lon<=15 and
lat >=30 and lat <=45;"
time psql osm osm -c "select count(*) from nodes where b @
As you can see my speedup was about 7.5, but before vaccum analyzing the db,
it was 80 as the query planner did not know the best index to use first!
Someone my test this on mysql?
> You have to be careful. Quadtiles aren't fixed in size, but the smaller you
> make them, the more often you'll have to store the fact that a line segment
> or area has crossed a tile. By the time you get to 64 bits you've got about
Another advantage in using spatial index/data is in the overlapping operator
(&&), defined on geometric objects, for example segments overlapping a box :
time psql osm osm -c "select count(*) from segments where b &&
so with this you don't have to mark segments intersecting tiles.
> I think that's down to deciding upon which items you want in the query -
> so, instead of saying "give me all of tile xyz", you need to be able to say
> "give me tile xyz, where the "ways" match the following attributes")
If you do this on a small tile or on one bigger with small elements this would
be desiderable, howewer a simple "get all the motorway from the UK tile"
could be dramatic if you do not get them from a reduced layer.
More information about the dev