For importing OSM data into PostGIS, we'll use a command line utility called osm2pgsql. Apparently, making a Linux build of osm2pgsql is straightforward; getting one that runs on Windows may require some more effort as described here: https://github.com/openstreetmap/osm2pgsql/issues/17, https://github.com/openstreetmap/osm2pgsql/issues/472.
I have used a Cygwin build as mentioned here:
http://wiki.openstreetmap.org/wiki/Osm2pgsql#Cygwin
Once we have the osm2pgsql ready, we'll need some data. For the sake of simplicity, I have downloaded the Greenwich Park area from https://www.openstreetmap.org/export#map=16/51.4766/0.0003 and saved the file as greenwich_observatory.osm (you will find it in the data accompanying this chapter).
The downloaded file is actually an XML file. Do have a look what's inside to get an idea of the data osm2pgsql is dealing with.
In order to take advantage of the OSM tags used to describe the data, we will need the PostgreSQL hstore extension. Basically it allows for storing key-value pairs in a column, so data with flexible schema can easily be stored. In order to install it, you need to execute the following query in either PgAdmin or psql:
CREATE EXTENSION hstore;
In order to import OSM data, issue the following command, making sure you adjust the paths and db connection details to your environment:
osm2pgsql.exe -H localhost -P 5434 -U postgres -W -d mastering_postgis -S default.style ../data/greenwich_observatory.osm -hstore
You should see a similar output:
osm2pgsql SVN version 0.85.0 (64bit id space) Password: Using projection SRS 900913 (Spherical Mercator) Setting up table: planet_osm_point
NOTICE: table "planet_osm_point" does not exist, skipping
NOTICE: table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE: table "planet_osm_line" does not exist, skipping
NOTICE: table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon NOTICE: table "planet_osm_polygon" does not exist, skipping
NOTICE: table "planet_osm_polygon_tmp" does not exist, skipping Setting up table: planet_osm_roads NOTICE: table "planet_osm_roads" does not exist, skipping NOTICE: table "planet_osm_roads_tmp" does not exist, skipping Using built-in tag processing pipeline Allocating memory for sparse node cache Node-cache: cache=800MB, maxblocks=0*102400, allocation method=8192
Mid: Ram, scale=100 !! You are running this on 32bit system, so at most !! 3GB of RAM can be used. If you encounter unexpected
!! exceptions during import, you should try running in slim
!! mode using parameter -s.
Reading in file: ../data/greenwich_observatory.osm
Processing: Node(4k 4.7k/s) Way(0k 0.55k/s) Relation(41 41.00/s) parse time: 0s
Node stats: total(4654), max(4268388189) in 0s Way stats: total(546), max(420504897) in 0s Relation stats: total(41), max(6096780) in 0s Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads
Writing relation (41)
Sorting data and creating indexes for planet_osm_point Analyzing planet_osm_point finished Sorting data and creating indexes for planet_osm_line Sorting data and creating indexes for planet_osm_polygon Analyzing planet_osm_line finished node cache: stored: 4654(100.00%), storage efficiency: 50.00% (dense blocks: 0, sparse nodes: 4654), hit rate: 2.00% Sorting data and creating indexes for planet_osm_roads Analyzing planet_osm_polygon finished
Analyzing planet_osm_roads finished
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 0s
Completed planet_osm_point Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line Creating indexes on planet_osm_line finished Copying planet_osm_polygon to cluster by geometry finished Creating geometry index on planet_osm_polygon All indexes on planet_osm_line created in 0s Completed planet_osm_line Creating indexes on planet_osm_polygon finished Copying planet_osm_roads to cluster by geometry finished Creating geometry index on planet_osm_roads All indexes on planet_osm_polygon created in 0s Completed planet_osm_polygon Creating indexes on planet_osm_roads finished All indexes on planet_osm_roads created in 0s Completed planet_osm_roads Osm2pgsql took 1s overall
At this stage, you should have the OSM data imported to the public schema. Thanks to using the hstore datatype for tags column, we can now do the following type of queries:
select name FROM planet_osm_point where ((tags->'memorial') = 'stone');
When executed in psql with the dataset used in this example, you should see the following output:
name
-----------------------------
Prime Meridian of the World
(1 row)