Importing OSM into PostGIS

At PCIC we’re trying/wanting to use OpenStreetMap for basemaps of the new version of our web mapping services. The thing is, the standard OSM maps are only generated in the common web mercator (EPSG:900913) projection and we would like to use and support a variety of other projections including BC Albers. So this means that we have to actually download the OSM data and render it ourselves. We already have PostGIS running on a pretty beefy server with lots of RAM and disk space, so this didn’t seem like that onerous of a task. Especially with all of the open source support and information out there.

It took me a little bit of time to figure this out. Especially since it’s so much data that mistakes are costly… if it takes 5 hours after starting your process for a problem to manifest, a cycle of make mistake/fix mistake/retry solution is very slow.

The steps to achieve our goal seem to be something like this:

1. Download planet.osm
2. Get planet.osm into PostGIS (somehow)
3. Render maps from PostGIS

Step 1 is easy. I haven’t gotten to step 3 yet. Step 2 is something like this.

The first thing that you need to do is make sure that you have enough memory to insert the data. I tried to enable BboxBuilder and LinestringBuilder thinking that those would be useful. But turns out that I couldn’t allocate enough RAM for them no matter it was more than was recommended by the webpage. Oh well; I’ll live without. Increasing the JVM memory allocation can be done through an environment variable. And osmosis help files recommended doing a pgsql dump first and then using their script to just do a COPY into the database because it would be faster. The dump was done within hours, but the COPY still has take several days (so far), so I’d hate to see how slow it would have been using the –write-pgsql switch instead. Here’s what I’ve done so far.

JAVACMD_OPTIONS="-Xmx10g" osmosis --read-xml file="planet-latest.osm" --used-node idTrackerType=BitSet --write-pgsql-dump directory="./pgimport" enableBboxBuilder="no" enableLinestringBuilder="no" nodeLocationStoreType="InMemory"
hiebert@windy:/home/data/gis/osm/pgimport$ ls
nodes.txt  node_tags.txt  relation_members.txt  relations.txt  relation_tags.txt  users.txt  way_nodes.txt  ways.txt  way_tags.txt
hiebert@windy:/home/data/gis/osm/pgimport$ sudo su postgres
[sudo] password for hiebert: 
X11 connection rejected because of wrong authentication.
postgres@windy:/home/data4/gis/osm/pgimport$ psql osm
Password: 
psql (8.4.8)
Type "help" for help.

osm=> \i /usr/share/doc/osmosis/examples/pgsql_simple_load_0.6.sql

I’ve had a few errors during the load that I’m not worried about:

psql:/usr/share/doc/osmosis/examples/pgsql_simple_load_0.6.sql:6: ERROR:  index "idx_nodes_action" does not exist

and some that I am worried about:

ALTER TABLE
psql:/usr/share/doc/osmosis/examples/pgsql_simple_load_0.6.sql:3212611252: ERROR:  column "action" does not exist

but I’ll stand by and see what happens.