Extract a PostGIS dump from OSM with a particular tag

Question

I'm new to OpenStreetMap. I want to extract all restaurants from Switzerland with their capacity if available. I am using osmosis. I tried:

osmosis --read-pbf file=switzerland.pbf --tf accept-nodes amenity=restaurant --write-xml output.osm

(I downloaded switzerland.pbf from http://planet.osm.ch/)

And it looks like it works. I would like to extract it not in XML but as a postgresql/PostGIS format. I tried

osmosis --read-pbf file=switzerland.pbf --tf accept-nodes amenity=restaurant --write-pgsql restaurant_osm.psql

But it fails with the following error:

INFO: Pipeline executing, waiting for completion.
Apr 8, 2014 1:06:08 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-pbf failed
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The server requested password-based authentication, but no password was provided.)

I don't really understand this error. How should I do in order to extract sport facilities, leisure facilities, restaurants and shops from Switzerland version of OSM with their capacity if available?

Answer 1

--write-pgsql wants to talk to a Postgis database. You have not specified any database connection for that task, e.g.:

osmosis --read-pbf file=switzerland.pbf --tf accept-nodes shop=supermarket --write-pgsql host=128.178.12.34 database=myDatabase user=myUsername password=myPassword

Furthermore, keep in mind that restaurants can also be modelled as closed ways, so you have to look into the way table too.

If you have a postgis database, make sure you have loaded all scripts before populating. My batch reads as follows:

%ProgramFiles%\PostgreSQL\8.4\bin\createdb -U postgres -E UTF8 -O osmuser osmosisdb
%ProgramFiles%\PostgreSQL\8.4\bin\createlang -U postgres plpgsql osmosisdb
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "%ProgramFiles%\PostgreSQL\8.4\share\contrib\postgis-1.5\postgis.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "%ProgramFiles%\PostgreSQL\8.4\share\contrib\postgis-1.5\spatial_ref_sys.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "%ProgramFiles%\PostgreSQL\8.4\share\contrib\_int.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "%ProgramFiles%\PostgreSQL\8.4\share\contrib\hstore.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "D:\Karten\OpenStreetMap\osmosis\script\pgsnapshot_schema_0.6.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "D:\Karten\OpenStreetMap\osmosis\script\pgsnapshot_schema_0.6_action.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "D:\Karten\OpenStreetMap\osmosis\script\pgsnapshot_schema_0.6_bbox.sql"
%ProgramFiles%\PostgreSQL\8.4\bin\psql -U postgres -d osmosisdb -f "D:\Karten\OpenStreetMap\osmosis\script\pgsnapshot_schema_0.6_linestring.sql"

(and a short version for people with Postgresql 9.x:

CREATE EXTENSION postgis;
CREATE EXTENSION hstore;
run pgsnapshot_schema_0.6.sql from Osmosis

)

And please not that osmosis will give you nodes, ways and relations. If you want a database that can be used for rendering by Mapnik, or to be used with other GIS softawre, osm2pgsql is a better choice. You will get proper polygons out of that.

Alternatively, try to use ogr2ogr for export to other GIS formats.

Answer 2

Try this post: How to manage the osmosis command line?

Pay attention to comments where I solved some troubles.

Here I have some tricks too: How to extract primary and secondary roads from OSM data?