Extracting data from OpenStreetMap into a Postgres db

1. Download PNG data

wget https://download.geofabrik.de/australia-oceania/papua-new-guinea-latest.osm.pbf

2. Clone the openstreetmap-carto dir

git clone git://github.com/gravitystorm/openstreetmap-carto.git --depth 1

3. Install osm2pgsql

On MacOS brew install osm2pgsql

4. Import to a new postgres db

4.1. Set up the database

psql -c "create database png_osm"
psql -d png_osm -c "CREATE EXTENSION hstore;"
psql -d png_osm -c "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;"

4.1. Import data from the PBF file to the db

osm2pgsql --host 127.0.0.1 -P 5432 -d png_osm --create --slim -G --hstore -C 2500 --number-processes 1 -S openstreetmap-carto/openstreetmap-carto.style papua-new-guinea-latest.osm.pbf

5. Start extracting data

Example: query all schools stored as a point:

png_osm=# select name from planet_osm_point where amenity = 'school' and name != '';
                         name
-------------------------------------------------------
 Daru Chalmers Primary School
 Tangi Primary School
 Towan Primary School
 Towan Primary School
 Pawari Holy Cross Primary School
 Margarima Secondary School
 Tukupawi Primary School
 Lake Haiabi Primary School

Note that some schools are stored as polygon:

png_osm=# select count(*) from planet_osm_polygon where amenity = 'school';
 count
-------
   854
(1 row)