1. Download data and software
The instructions are fairly generic, so should work for both Windows, Linux and Mac OS X. I wrote them for Windows, but I've since then switched to Mac OS X.
PostgreSQL+PostGIS
I assume that you do not already have Postgres/PostGIS installed on your system.
Download PostgreSQL+PostGIS for all platforms here: http://www.postgresql.org/download/
Follow the instructions provided to install the database software.
OSM data
Download OpenStreetMap data (.osm file):
downloads.cloudmade.com
I chose europe -> denmark.
osm2pgsql
Download the version for your platform (Windows, Linux, Mac OS X etc):
wiki.openstreetmap.org/wiki/Osm2pgsql
2. Create and prepare database
If you add the PostgreSQL bin folder to your path, you'll have access to commands like createuser and createdb in your shell.
Create a user (after running command, answer 'y' to superuser):
createuser -U postgres <enter your username, I entered kostas>
Create the database (should be called 'gis'):
createdb -U postgres -E UTF8 -O kostas gis
Install language plpgsql (on my system this was already installed):
createlang -U postgres plpgsql gis
Add PostGIS functionality to database (you should get pages of output as functions etc are created):
psql -U postgres -d gis -f PATH_TO_POSTGRES/share/contrib/postgis-1.5/postgis.sql
Download the file 900913.sql. The following will add spherical mercator projection to the database:
psql -U postgres -d gis -f PATH_TO_FILE/900913.sql
3. Add OSM data to database
Change directory to where you unzipped osm2pgsql:
cd PATH_TO_OSM2PGSQL
Import OSM data:
osm2pgsql -U postgres -s -S ./default.style PATH_TO_OSM_FILE/denmark.osm
Options for osm2pgsql:
- -U postgres
Run program with the PostgreSQL admin user - -s
Run in "slim" mode, which means running the program not in RAM, which is necessary on my system - -S ./default.style
On windows (maybe also Linux and other OS) you must specify path to style file. Use default which comes with osm2pgsql.
That's it! You now have a database full of street data.
4. Testing the database
This is where I live:
select name, astext(way) from planet_osm_line where name='Anders Henriksens Gade'
Which gives the name of the road plus a WKT representation of the geometry:
Anders Henriksens Gade LINESTRING(1402528.63 7491973.55,1402602.4 7491829.85)
Conclusion
It works, but tables are created in the 'public' schema of the 'gis' database. This is not so nice. I'd prefer that tables were created e.g. in 'osm' schema. When I've looked into how this is done, I'll update this post.
I'd like to write a howto that uses Osmosis to continuously update the local OSM data with diffs from OSM.
danke sehr
I will follow this
Thanks!!!
Hi,
Please try my osm2pgsql patch, with it you’ll can specify the schema name you want with -g option.
Available at : http://trac.openstreetmap.org/ticket/4144
Thank your for sharing the experience. But with following your instruction, I still cannot complete the function. :( Errors always occur after the last step which is setting up tables. I’m going to transfer it on Linux…
Hi Channing Zong,
Could you please elaborate on what goes wrong? What OS are you experiencing the errors on, etc?
I am pretty new to all this but this is a very nice explanation I have tried to follow. As in the case of CHanning Zong I am getting errors when adding the tables. Let me summarized what I have done.
* Install PostgreSQL 9.1
* Install PostGiS 1.5
Follow your instructions exactly with the exception of
>psql -U postgres -d testdb -f /900913.sql
which gave me an error saying that testdb was not found. Istead I run the following:
>psql -U postgres -d my_gis -f /900913.sql
being my_gis the database I created before (instead of gis)
when running osm2pgsql
osm2pgsql -U postgres -s -S .\default.style my_osm_file.osm -W
the error it gaves is that AddGeometryColumn() has an ivalid SRID:
Can you help me?? If you need more info please let me know. Thanks a bunch.
I have updated the instructions, because there was a typo.
Sweet!!! It works now. Thank you very very much.
Downloading your 900913.sql was crucial. Thanks.
Thanks for the post, it helped a lot (especially the 900913.sql file part :) )
Providers are pretty easy to write. It’s a very small infetrace to implement. I actually spent more time learning the different dialects of sql than coding the providers. If you post a link to a free version of db2 and a .net connector to it, I can probably whip one up this week.
Thank you! This was a huge help.
I’m churning through this with (apparently semi-recent, semi-ancient) windows binaries, so, to repay the karma, here are a couple of things that helped me:
“C:\Program Files (x86)\PostgreSQL\9.1\bin\psql” -U -d gis -f “C:\Program Files (x86)\PostgreSQL\9.1\share\contrib\postgis-2.0\legacy.sql”
springmeyer’s note from here: http://trac.osgeo.org/postgis/ticket/1287
(it’s the sql that begins with … “CREATE OPERATOR CLASS gist_geometry_ops”)
“C:\Program Files (x86)\PostgreSQL\9.1\bin\psql” -U -d gis -f “C:\Program Files (x86)\PostgreSQL\9.1\share\contrib\postgis-2.0\postgis.sql”
I think it was easier for me to learn solve the Rubik’s cube. Good luck to us all….
Hi,
> -S ./default.style
> On windows (maybe also Linux and other OS) you must specify path to style file. Use default
> which comes with osm2pgsql.
Default may be old, here is updated
http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/default.style
Greetings
Marcin
Pingback: Installing osm2pgsql - dBforums
Unfortunately the link to 900913.sql does not work any more.
Is there any alternatives?
Pingback: MechLab Engineering – [Tutorial] Entstehung der ÖPNV Fahrzeit Heatmaps 2.0 mit PostgresApp, TileMill und QGIS