Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

--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.

share|improve this answer
    
It works a bit better, but I get an "ERROR: relation "schema_info" does not exist". Any suggestion? I'm reading some help here: help.openstreetmap.org/questions/20863/… –  Antonin Apr 8 at 12:03
    
I ran pgsnapshot_schema_0.6.sql to build the correct schema. It creates a problem: "ERROR: type "hstore" does not exist". –  Antonin Apr 8 at 12:17
1  
You have missed to load the hstore and all scheme sql files in advance, See my extended answer. –  Andre Joost Apr 8 at 12:19
    
Yes, I just found here: wiki.openstreetmap.org/wiki/Osmosis/PostGIS_Setup that the solution is to "CREATE EXTENSION hstore;". Thanks. –  Antonin Apr 8 at 12:21
add comment

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?

share|improve this answer
    
Thank you for your answer. So I need to use first XML output and then use osm2pgsql? It is not possible to directly extract it as pgsql in the way that is described in the detailed usage from osmosis here: wiki.openstreetmap.org/wiki/Osmosis/… ? –  Antonin Apr 8 at 11:30
    
To me it's the easy way since gives me more control. Andre Joost said: 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 can connect all commands to make all in one step. I think the XML step will be made in background anyway. –  Magno C Apr 8 at 12:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.