Osm2postgresql

From OpenStreetMap Wiki
Jump to navigation Jump to search
Logo.png
This page describes a historic artifact in the history of OpenStreetMap. It does not reflect the current situation, but instead documents the historical concepts, issues, or ideas.
About
Osm2postgresql was a tool to import OpenStreetMap data into a PostgreSQL database with the PostGIS extension.
Impact on OpenStreetMap
Osm2postgresql was one of the multiple solutions to get OpenStreetMap data into a PostGIS enabeld PostgreSQL database. It had limited impact because alternatives were available back then as well.
Reason for being historic
Osm2postgresql was released the last time in 2012. As of October 2018, Osmosis is unmaintained. Much longer, Osm2pgsql and Imposm are more performant replacements.
Captured time
February 2019


To simplify rendering with QGIS and other GIS/web servers, this script sets up a PostgreSQL / PostGIS server or database, imports OSM data into it, process those data (including multi-polygons with holes) and proposes a classification.

This project is different from the following projects:

(although they have a few common goals, hence the related names).

It is available at http://sourceforge.net/projects/osm2postgresql/

Features

  1. Optional: installs a postgres server and configure it; create a database (with postgis and hstore)
  2. Loads OSM data into the database and process it
  3. Creates and fix polygons based on ways and relations
  4. Fixes small streams, canals... on riverbanks (nicer rendering)
  5. Creates 3 tables that are ready for rendering: polygons, ways, nodes_with_tags
  6. In each table (nodes_with_tags, ways, polygons), populates a "class" field that you can link to your symbology
  7. Helps you easily render OSM data in your GIS application or web server
  8. Keeps ALL tags (in two fields: 'tags' as text and 'tagshstore' as hstore)

Simple usage

Example rendering made with Osm2postgresql version 0.1 and QGIS, same area as here. Click on image (twice) for higher resolution.
./osm2postgresql_05rc4.sh [OPTION]...  --file filename.osm  [OPTION]...

EXAMPLE USAGE (download and uncompress, then import into database):

wget -O - http://download.geofabrik.de/osm/europe/monaco.osm.bz2 | bzcat > monaco.osm

./osm2postgresql_05rc4.sh --file monaco.osm

More examples are shown at the end.

Parameters

MANDATORY PARAMETER: 
-f, --file              name of .osm or .pbf file to import


OPTIONAL PARAMETERS (NOT FOLLOWED BY A VARIABLE):
--help                  display this help
--version               display the version and date of this script
-c, --createdb          create a database. If not, make sure your database has postgis,
                          spatial_ref_sys and hstore.
-r,--rivers_as_is          keep each river, stream... as is (default is to cut rivers to
                          improve rendering of streams and rivers linked to rivers and lakes)
--pbf                   import OSM binary file (.pbf, faster) instead of XML-type .osm
--pgpass                add entry to pgpass (use with care)
-i, --install           [NOT recommended]. Install a postgres server (using EntrepriseDB installer) and create a db user. Read release note in the script first!

OPTIONAL PARAMETERS WHICH MUST BE FOLLOWED BY A VARIABLE:
-d, --dbname            database name to create and/or connect to;
                          with --install or --createdb, default is OSM$NOW
                          otherwise default is `whoami`
-U, --username          username (default: `whoami`). Role must already exist (except
                          with --install). Mileage varies...
--password              postgresql password for 'username', to be added to ~/.pgpass file
                        (use with care)
-h, --host              server host (default is to use a socket with user `whoami`
                          except with --install option: default is 127.0.0.1
-p, --port              server port (default when not using a socket is: 5432)
-O, --options_postgres  any additional psql options to connect to postgres as postgres user
-u, --options_user      any additional psql options to connect to postgres as current user
-t, --tablespace        name of the tablespace to create (only if createdb="yes" and
                          not install="yes" ; will be created in folder 'datadir')
-D, --datadir           where to save the database (with option --install or --tablespace)
                          You will be asked to type your root or sudo password, except if
                          this folder already exists on disk (see next option too)
--no_mkdir              does not try to create datadir nor chown it to postgres. Usefull if
                         you use -D but do not want the script to ask for your (su)do password.
                         In this case you must mkdir and chown yourself before.
-m, --temporarydir      where to save the files created by osmosis
-P, --postgis           folder where to find the sql commands to install postgis in database
-H, --hstore            folder where to find the sql commands to install hstore in database
-E, --EPSG              new SRID (EPSG) code (if you want to reproject the geometries)
                          do not use to keep data in lat/long.
-I, --installdir        where to install the postgres server (with --install option);
                          default is: ~/PostgresPlus
-x, --psql_folder       folder where psql executable is installed (e.g.
                          /usr/lib/postgresql/9.0/bin/). By default, the script will search
                          in the path.
-a, --arch              architecture when installing the postgres server
                          use "-a x64" for 64 bits; do not use that option for 32 bits
-b, --bash_init_script  the name of the file (e.g. your bash init script) where to add
                         a command that will add the path to 'psql' if using the 
                         --installdir option. Default is ~/.bashrc

The last two parameters are only relevant with the --install option.

Examples

./osm2postgresql_05rc4.sh --file monaco.osm

The above command assumes the database is already created (with postgis and hstore support), is empty and owned by current user (trusted local connection with socket without password when logged with a specific linux user, which means running psql from the command line is enough to connect to the "current user name" database).


The following also assumes you have already created a postgis database with hstore support, but takes account of user names, passwords, hostname, java tmp directory. Replace BIGDISK, HOSTNAME, DBNAME and PASSWORD with the values that apply to your environment.

mkdir /BIGDISK/tmp
set JAVACMD_OPTIONS=-Djava.io.tmpdir=/BIGDISK/tmp
export JAVACMD_OPTIONS
rm -r /BIGDISK/temposm/
rm -r /BIGDISK/tmp/*
./osm2postgresql_05rc4.sh -h HOSTNAME -d DBNAME -U postgres --password PASSWORD \
--pgpass -m /BIGDISK/temposm --pbf -f /BIGDISK/planet.osm.pbf
# results are logged in current directory, file logs_osm2postgresql_$DATE

If you want a database to be created, you need to provide the paths to postgis.sql and hstore.sql, for instance with:

./osm2postgresql_05rc4.sh --postgis /usr/share/postgresql/8.4/contrib/postgis-1.5   --hstore /usr/share/postgresql/8.4/contrib   --createdb   --file monaco.osm

You can install a server and populate a new database using the EnterpriseDB installer:

./osm2postgresql_05rc4.sh --install --file monaco.osm

In this case, you will be asked your root or sudo password each time it is required.

Full example session in virtual machine

The following is an example session. It has been tested on the following ubuntu-11-10-oneiric-ocelot virtual machine: http://virtualboxes.org/2011/10/14/ubuntu-11-10-oneiric-ocelot-is-here-x86/comment-page-1/

whose active user account (username/password) is: ubuntu/reverse

After installation of the virtual machine, you need to log in, copy osm2postgresql to your home, and run the following line after line. Note that this is much slower than in a non virtualized environment. There seems to be issues with some VM configuration related to disk space (see here).

Recommended use is to install first postgresql, postgis and hstore using your package manager (but then rights management can be more tricky). Still the virtual machine can be useful for tests and new users.


sudo apt-get install openjdk-6-jre-headless
cd ~/
chmod a+x ./osm2postgresql_05rc4.sh
wget http://download.geofabrik.de/osm/europe/monaco.osm.pbf
wget https://dev.openstreetmap.org/~bretth/osmosis-build/osmosis-0.40.tgz
wget http://get.enterprisedb.com/ga/postgresplus-8.4.1-2-linux.bin
# If you want to play with the options without redoing the above every time,
# it is now a good time to take a snapshot of your virtual machine
#     hostname:port:database:username:password 
echo "localhost:5432:postgres:postgres:postgres" >> ~/.pgpass
chmod 0600 ~/.pgpass #ignored otherwise
# Test installing postgresplus from enterprisedb
~/osm2postgresql_05rc4.sh --pgpass --username ubuntu --password ubuntu --host localhost  --createdb --file monaco.osm.pbf --pbf --psql_folder ~/PostgresPlus/bin/ --options_postgres '-d postgres' -P ~/PostgresPlus/share/postgresql/contrib --hstore ~/PostgresPlus/share/postgresql/contrib --install -I ~/PostgresPlus --dbname monaco_test1

Full example session in non-virtualized environment

The following has been tested on a non-virtualized environment only. You should make sure that psql is able to connect and may need to change some options. This requires some basic knowledge in linux shell and postgresql administration.

cp ~/sig/software/postgresql/osm2postgresql/osm2postgresql_05rc4c_comments.sh ./
chmod a+x osm2postgresql_05rc4c_comments.sh
wget http://download.geofabrik.de/osm/europe/monaco.osm.pbf

./osm2postgresql_05rc4c_comments.sh --createdb --file monaco.osm.pbf --pbf --psql_folder /usr/lib/postgresql/8.4/bin/  -P /usr/share/postgresql/8.4/contrib/postgis-1.5 -H /usr/share/postgresql/8.4/contrib --dbname monaco_test2 -E 900913

echo "SELECT NumInteriorRings(polygon) as nInteriorRings, name, substring(tags for 50),ST_AsEWKT(polygon)  FROM polygons WHERE NumInteriorRings(polygon)>0 and name is not null;" | psql -d monaco_test2
# Should return something like this:
ninteriorrings |      name       |                    substring                    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        st_asewkt                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
----------------+-----------------+-------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              1 | Palais Princier | "name"="Palais Princier", "type"="multipolygon" | SRID=900913;POLYGON((825924.798471185 5423966.90329793,825997.456702825 5423908.65470891,825988.784914491 5423895.66783481,825995.029937926 5423890.0448167,825982.339515974 5423870.15630617,825994.339757081 5423858.77165521,826025.130728236 5423891.63158585,826017.316099983 5423900.01219635,826034.915711476 5423918.69913654,826044.07730557 5423910.31850905,826089.885276029 5423957.99883496,826078.174465598 5423969.46063664,826084.875898944 5423982.47842596,826080.879529225 5423984.37332464,826072.886789785 5423988.16312316,826066.352335677 5423976.43940189,826047.31670275 5423986.74579995,826048.229522575 5423991.13642146,825995.942757748 5424013.02793743,826005.86132438 5424036.49089362,825987.159649927 5424042.48373967,825974.023950012 5424039.67988834,825979.222570234 5424020.60755956,825955.622838186 5424012.87388013,825954.35379599 5424009.8851691,825938.02322669 5424004.89371572,825939.381324477 5423980.79920714,825936.409094074 5423979.41269641,825935.819100771 5423968.72116513,825929.919167761 5423972.32608928,825924.798471185 5423966.90329793),(826020.410781826 5423927.58815599,826054.57473355 5423960.17103016,825993.849951324 5423989.11827588,825973.144526037 5423962.92864039,826020.410781826 5423927.58815599))
(1 row)

# The following deletes most tables then insert new data to the existing database.
rm -r tempmonaco_test2
echo "DROP TABLE IF EXISTS dumped_multilinestring, nodes, nodes_with_tags, polygons,  relation_members, relations, simple_polys, way_nodes, ways CASCADE; DROP SEQUENCE IF EXISTS  polygons_idint4_seq; SELECT Populate_Geometry_Columns(); CREATE TABLE test ( id integer ) ;" | psql -d monaco_test2

./osm2postgresql_05rc4c_comments.sh --file monaco.osm.pbf --pbf --psql_folder /usr/lib/postgresql/8.4/bin/ --dbname monaco_test2 -E 32632

echo "SELECT name, class, substring(tags for 30),ST_AsEWKT(linestring)  FROM ways WHERE name is not null LIMIT 3;" | psql -d monaco_test2
# Should return something like this:
          name          |       class       |           substring            |                                                                           st_asewkt                                                                           
------------------------+-------------------+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Boulevard du Larvotto  | primary highway   | "name"="Boulevard du Larvotto" | SRID=32632;LINESTRING(373643.599960678 4844797.2421754,373644.12674241 4844781.255358,373643.747761319 4844772.72970582)
 Bretelle               | primary highway   | "name"="Bretelle", "highway"=" | SRID=32632;LINESTRING(373410.268443561 4843963.30400713,373356.511182892 4843936.47026826)
 Avenue Princesse Grace | secondary highway | "name"="Avenue Princesse Grace | SRID=32632;LINESTRING(374310.756911022 4845229.4205588,374299.792984344 4845226.30517949,374291.014825344 4845224.63741443,374280.626492711 4845226.42202404)
(3 rows)

See also

The following pages give rendering examples, hints, style files for qgis, icons, etc. that are related to osm2postgresql:

Other links: