[OSM-dev] getnodes index?

[Raphael Jacquot]

Christopher Schmidt wrote:
> I'm porting the getnodes query to Python for my planet stuff, and I was
> having the SQL queries take *much* longer than I expected. I then did an
> explain on the query, and it said that it wasn't using the
> longitude/latitude key when doing a query. Adding seperate indices for
> lat/lon made the query move from 7 minutes (for a .01 x .01 degree area)
> to less than 1sec. 
> 
> I'm assuming that this isn't the case on the main OSM server, but for
> me:
> 
> alter table nodes add index nodes_lon_idx (longitude);
> alter table nodes add index nodes_lat_idx (latitude);
> 
> Makes everything a lot faster. You might want to try this if you've got
> a local version of the OSM db.
> 

if your DB server supports spatial extensions (mysql 5.x or postgres 
8.x) using the dedicated data types like points and R-Tree indexes, it 
should make it even faster to index

for mysql:
http://dev.mysql.com/doc/refman/5.1/en/gis-geometry-class-hierarchy.html
http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-indexes.html

for postgresql
http://www.postgresql.org/docs/8.1/interactive/datatype-geometric.html
http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html
(using the GIST r-tree implementation)
see my example at:
http://www.navsys.org/cvs/pynavsys/pg-osm.sql?rev=1.4&view=auto