[OSM-dev] Proposal: Database accelerator and dirty tile marker based on simple algorithm.

[Nicola Ranaldo]

[...]
> 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;"

 count
-------
 70776
(1 row)


real    0m0.517ss
user    0m0.000s
sys     0m0.010s

time psql osm osm -c "select count(*) from nodes where b @ 
box(point(30,0),point(45,15))"
 count
-------
 70776
(1 row)


real    0m0.070s
user    0m0.000s
sys     0m0.000s


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 && 
box(point(180,0),point(-180,0));"

 count
-------
   119
(1 row)


real    0m0.012s
user    0m0.000s
sys     0m0.000s

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.

	Niko