[Tile-serving] [osm2pgsql] Good cheaper indexing (#171)

[rbbb]

After some thoughts and code search, let me rephrase. I did some tiles following the switch2osm script and it was quite slow as there was no indexing on the db. It would be 'nice to have' help indexing the db for beginners.

I found the code that triggers the spatial indexing in mapnik, it's in the plugins/input/postgis directory. It will wrap the select statement as a subselect, and add a where clause 'where geometry in bounding box' (or replace the '!bbox!' string in the query with the bbox if there is one. Couldn't figure out how they added the bbox constraint, didn't think of subselect...

A doable way to have automatic indexing would be to have a binary column added to the pgsql table 'is_in_layer_major_road'. Add the column as binary, index it on geometry where added boolean not null. Setting the binary columns is UPDATE relevant table SET is_in_layer_major_road=true WHERE osm_id IN (SELECT osm_id from ('layer select statement')).
Setting up the column for update points is the same update with osm_id in incoming ids join the select statement. Checking the definitions are still consistent is checking the layer select statement against a known hash. 

Anyways, just a thought exercise, I don't know how useful it would be on planet DBs. This would be a mapnik+osm2pgsql dev so probably quite difficult.


---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/171#issuecomment-52714916
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20140819/c8c71eac/attachment.html>