[OSM-dev] OSM on PostGIS

[Schuyler Erle]

Robert Munro's speculation about the possible benefits of running the
existing OSM database on PostgreSQL with spatial indexing etc. via
PostGIS got me started thinking this weekend. (Cheers, Robert, by the
way, for suggesting it.) You can see the pieces I've implemented here:

    http://freemap.in/~sderle/pg_osm/

The osm_server.sql file is a straight translation to PostgreSQL of the
combined create_database.sql and migrate.sql from the OSM Ruby port.
There are almost certainly bugs that need to be found and fixed.

The spatial.sql file contains all of the monkey business for setting
up the PostGIS spatial columns and indexes on the tables. To
current_nodes we add a single geometry column "geom" to hold points;
to current_segments and current_ways we add a  geometry column called
"bbox" to cache the bounding box of all of the points in the segment
or way.

spatial.sql also contains a series of PL/pgsql triggers called when
the current_nodes, current_segments, current_ways, or
current_way_segments tables change, that ensure that the geometry is
automatically set on current_nodes from the longitude and latitude,
and that cached bounding boxes on current_segments and current_nodes
are kept up to date.

I am sort of hoping that Robert (or anyone else who's interested and
familiar with Ruby) can help me integrate this with the OSM Ruby port.
I am no Ruby expert, although adding the spatial indexing should
require nothing more than altering the SELECT query set up by the
"map?bbox=..." API call. After several hours of fiddling, I still
haven't gotten the OSM Ruby port working yet, but I'm running a data
import now and hope to give the server another try once there's
something in the d/b.

The rebuild.sh and import_osm.py scripts build the database and
populate it from a planet.osm respectively. The import_osm.py script
is *looking* as if it will take about two hours to import the latest
planet.osm on an average 2 GHz Athlon machine. I hope that the
import_osm.py script will be easy to port to Ruby -- it requires the
Python psycopg2 module to talk to the database. (You'll also need
PostGIS, of course.)

After this gets up and running, I hope to try to tweak its performance
and see if we can't confirm or deny that proper spatial indexing might
speed up the operation of the API. I am on #osm on irc.oftc.net if
anyone is interested or has questions.

SDE