[Solved] [Beginner] Import data with osm2pgsql

Hello everybody !

I’m trying to import some data from openstreetmap into a postgresql database since 2 days and I can’t manage to do it, that is why I need someone’s help !

I think it is not quite difficult, but I am a great beginner in using PostgreSQL, PostGIS, osm2pgsql, osm data, and the windows command prompt.

Here is the situation:

I follow this tutorial (https://github.com/mapserver/mapserver/wiki/RenderingOsmDataWindows#wiki-osm-import) (but not the MapServer part)

I’m (unfortunately) using Windows 7 (x64). I’have installed PostgreSQL 9.2, PostGIS 2.0, and osm2pgsql.
My openstreetmap data are rhone-alpes.osm (France).

PostgreSQL is here: “C:\Program Files\PostgreSQL\9.2\bin”
osm2pgsql is here: “C:\Program Files\osm2pgsql”
rhone-alpes.osm.bz2 is here : “C:\aGlumLieu\GeoMkt”

The name of my PostgreSQL database is “osm”

Here is the problem:

I don’t understand how to import those data into my database “osm”, even if i have read many, many tutorials…

I try with this:

osm2pgsql -U postgres -W -m -d osm -p osm -E 3857 -S "C:\Program Files\osm2pgsql\default.style" rhone-alpes.osm.bz2

or this

osm2pgsql -U postgres -W -m -d osm -p osm -E 3857 -S "C:\Program Files\osm2pgsql\default.style" C:\aGlumLieu\GeoMkt\rhone-alpes.osm.bz2

But there is an error, and I always have this message:

osm2pgsql SVN version af61cae663 (64bit id space)

release notes: 'Windows version built by Dominik Perpeet <http://www.customdebug.com/osm2pgsql/index.html>'
osm2pgsql: illegal option --W
Usage error. For further information see:
               osm2pgsql.exe -h|--help

Does someone know the right way to do it ?

Have a good day !

Ps: Thank you so much to Openstreetmap and its community, you’re are doing something awesome ! \o/

Hi
I know nothing about this but I did notice on the following page that you may not need to use the -W switch.
It might be worth a try.

PLEASE CHECK THAT THIS INFO IS RELEVANT TO WHAT YOU ARE DOING FIRST

…from http://www.postgresql.org/docs/9.2/static/app-pgdump.html

-W
–password

Force pg_dump to prompt for a password before connecting to a database.

This option is never essential, since pg_dump will automatically prompt for a password if the server demands password authentication. However, pg_dump will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

(Sorry if I have mislead through ignorance)

Hi Nevw !

I have already tried not to use -W like this:

osm2pgsql -U postgres -m -d osm -p osm -E 3857 -S "C:\Program Files\osm2pgsql\default.style" C:\aGlumLieu\GeoMkt\rhone-alpes.osm.bz2

or

osm2pgsql -U postgres -m -d osm -p osm -E 3857 -S "C:\Program Files\osm2pgsql\default.style" rhone-alpes.osm.bz2

But it does’nt work much better, and I have a new error message:

osm2pgsql SVN version af61cae663 (64bit id space)

release notes: 'Windows version built by Dominik Perpeet <http://www.customdebug.com/osm2pgsql/index.html>'
Error: connection to database failed: fe_sendauth: no password supplied

Ok, good (?) news:

Following the PostgreSQL documentation, i have update the pg_hba.conf file with “trust” in “method” in order to don’t need password.

And it works !

But there is a new Error :

osm2pgsql SVN version af61cae663 (64bit id space)

release notes: 'Windows version built by Dominik Perpeet <http://www.customdebug.com/osm2pgsql/index.html>'
Could'nt read EPSG definition <do you have /usr/share/proj/epsg?>

I’m trying to understand what is this “EPSG definition”.

If you have some advices, it would be great !

It seems a common problem…
https://www.google.com.au/search?q=/usr/share/proj/epsg&ie=utf-8&oe=utf-8&rls=org.mozilla:en-GB:official&client=firefox-a&channel=rcs&gws_rd=cr&ei=wehfUvLTFa2WiQfaooDQBw
…I hope the solution is there too

Hi everybody !

I don’t know what to do with this ‘EPSG definition’ problem, even if I read many things on it.

I try to import my data with another windows command :

osm2pgsql -s -U postgres -d osm -S "/Program Files/osm2pgsql/default.style" /toto/GeoMkt/rhone-alpes/rhone-alpes.osm

But I have a new problem:

osm2pgsql SVN version af61cae663 (64bit id space)

release notes: 'Windows version built by Dominik Perpeet <http://www.customdebug.com/osm2pgsql/index.html>'
Using projection SRS 900913 (Spherical Mercator)
Error reading style file line 151 (fields=4)
flag 'phstore' is invalid in non-hstore mode
Error occured, cleaning up

In the default.style file, in line 151, there are

148      # Area tags
149      # We don't make columns for these tags, but objects with them are areas.
150      # Mainly for use with hstore
151      way         abandoned:aeroway       text    phstore
152      way         abandoned:amenity       text    phstore
153      way         abandoned:building      text    phstore
154      way         abandoned:landuse       text    phstore
155      way         abandoned:power         text    phstore
156      way         area:highway            text    phstore

I don’t know what I have to do…

Hi,
About phstore type, I think you can either cut those lines off the stylefile or use osm2pgsql in hstore more by adding -k into the command.

Hello JRA !

Thank you !

I have installed the hstore mode in my “osm” database (with : CREATE EXTENSION hstore
and try to import the data with this windows command:

osm2pgsql -s -U postgres -d osm --hstore -S “/Program Files/osm2pgsql/default.style” /toto/GeoMkt/rhone-alpes/rhone-alpes.osm

It start to create the table planet_osm_point, but stop because of this error:

failed: Error: the population column is specified more than once

Problem solved !

“Population” was mentionned two times in my default.style file !

(One week to load data into PostgreSQL…Alléluia :D)

Thanks to Nevw and JRA !