Osm2postgresql
Jump to navigation
Jump to search
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
 | This script is not actively developed and the supported versions of PostgreSQL are pre-v9.1. |
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
- Optional: installs a postgres server and configure it; create a database (with postgis and hstore)
- Loads OSM data into the database and process it
- Creates and fix polygons based on ways and relations
- Fixes small streams, canals... on riverbanks (nicer rendering)
- Creates 3 tables that are ready for rendering: polygons, ways, nodes_with_tags
- In each table (nodes_with_tags, ways, polygons), populates a "class" field that you can link to your symbology
- Helps you easily render OSM data in your GIS application or web server
- 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:
- http://www.qgis.org/wiki/OpenStreetMap_data_rendered_with_QGIS
- Video on how to use osm2postgresql and QGIS together with OSM data: http://www.youtube.com/watch?v=NBBYtH2svw0
- http://www.qgis.org/wiki/Using_OpenStreetMap_data
- http://www.mediafire.com/?jiooxkbmyzgr0 (QGIS project, styles, icons and sample data)
Other links:
- osmosis which powers osm2postgresql
- Rendering samples made with Osm2postgresql and Quantum GIS, compared with other tools
- Osm2pgsql
- Osm2pgrouting
- Imposm