Importing OSM (PBF file) to PostGIS (PGSNAPSHOT schema)

OSM Data can be used for variety of usecases like rendering, searching, data analytics etc. Each usecase yield quickest result in a particular data format. Due to the large volume of data, this can cause significant difference in the response times. 

Some of these formats are,

1. PBF -- A highly compressed format used almost exclusively for storage and downloads owing to the small sizes. These format are not human readable and gzip compressed.
2. PostgreSQL with PostGIS extension -- A data format backed up by PostgreSQL database. There are various schemas supported for multiple use cases which can greatly impact the response time as indexes and normalization are used to speed up queries. Some popular schemas along with their usecase and certain features are as below. The table is taken directly from openstreetmap wiki.

Schema name	Created with	Used by	Primary use case	Updatable	Geometries (PostGIS)	Lossless	hstore columns	Database
osm2pgsql	osm2pgsql	Mapnik, Kothic JS	Rendering	yes	yes	no	optional	PostgreSQL
apidb	osmosis	API	Mirroring	yes	no	yes	no	PostgreSQL, MySQL
pgsnapshot	osmosis	jXAPI	Analysis	yes	optional	yes	yes	PostgreSQL
imposm	Imposm		Rendering	no	yes	no	Imposm2: no, Imposm3: yes	PostgreSQL
nominatim	osm2pgsql	Nominatim	Search, Geocoding	yes	yes	yes	?	PostgreSQL
ogr2ogr	ogr2ogr		Analysis	no	yes	no	optional	various
osmsharp	OsmSharp		Routing	yes	no	?	?	Oracle
overpass	Overpass API		Analysis	yes	?	yes	?	custom
mongosm	MongOSM		Analysis	maybe	?	?	?	MongoDB
node-mongosm	Mongoosejs		Analysis	yes	yes	yes	NA	MongoDB
goosm	goosm		Analysis	no	yes	yes	NA	MongoDB
pbf2mongo	pbf2mongo		Analysis	no	yes	yes	NA	MongoDB
waychange	SQL	streetkeysmv	Data cache and Analysis	only a schema	optional	?	no	PostgreSQL

In this blog, we'll download a PBF file containing our region of interest and insert the data into a PostgreSQL database as PGSNAPSHOT schema.

Get PBF Data

Visit https://download.geofabrik.de/, there you'll find OSM data for continent and countries (and cities in some cases). 

Install osmosis

Visit https://github.com/openstreetmap/osmosis/releases/latest, and download the gzipped tarball containing osmosis binaries.

# mkdir osmosis
# mv osmosis-latest.tgz osmosis
# cd osmosis
# tar xvfz osmosis-latest.tgz
# rm osmosis-latest.tgz

Create Dataqbase and import PGSNAPSHOT schema

# createdb gis
# psql -d gis -c "CREATE extension IF NOT EXISTS postgis; CREATE extension IF NOT EXISTS hstore"
# psql -d gis -f osmosis/script/pgsnapshot_schema_0.6.sql

At a later stage, these scripts can be run for specific queries,

# psql -d pgsnapshot -f osmosis_dir/script/pgsnapshot_schema_0.6_action.sql 

# psql -d pgsnapshot -f osmosis_dir/script/pgsnapshot_schema_0.6_bbox.sql 

# psql -d pgsnapshot -f osmosis_dir/script/pgsnapshot_schema_0.6_linestring.sql

Import PBF data

osmosis/bin/osmosis \
--read-pbf file="asia.osm.pbf" \
--write-pgsql host="localhost" database="gis" user="" password=""

Try out GeoQuery

select st_distance(geom, 'SRID=4326;POINT(-74.1235 35.3521)'::geometry) as dist, \
tags->'name' as name from nodes where tags?'place' and tags->'place' \
in ('city','muncipality','town') order by dist;