This post highlights some very useful commands for entering spatial data into a PostgreSQL database ready for analysis within GIS or using Postgis.
For entering spatial data into PostgreSQL you can either use the ogr tools or pgsql2shp commands. After download, both need to be added to your windows environment variable:
ogr2ogr: input csv
ogr2ogr -overwrite -f "postgresql" pg:"dbname=postgres host=localhost port = 5632 user=postgres password = XXXXX" C:\Link\To\file.csv -lco OVERWRITE=YES
ogr2ogr: input shapefile
ogr2ogr –overwrite –nlt MULTIPOLYGON –f PostgreSQL PG:"dbname=Nathan_spatial host=localhost port=5632 user=postgres password=XXXXX" example.shp
ogr2ogr: input ESRI FileGDB
ogr2ogr –overwrite – skipfailures –f PostgreSQL PG:"dbname=Nathan_spatial host=localhost port=5632 user=postgres password=XXXXX" "C:\somefolder\somegeodatabase.gdb" "example feature class"
ogr2ogr: output ESRI FileGDB
ogr2ogr -overwrite -skipfailures -f "FileGDB" "C:\Users\tonyfregoli\Documents\ArcGIS\Default.gdb" PG:"dbname=Nathan_spatial host=localhost port=5632 user=postgres password=XXXXX" "pcd" -nln (new layer name) districts -nlt MULTIPOLYGON
pgsql2shp/shp2pgsql: Postgis query to shapefile
pgsql2shp -f path to output shapefile -h hostname -u username -P password databasename "query"
pgsql2shp -f qds_cnt -h localhost -u postgres -P password gisdb "SELECT sp_count, geom FROM grid50_rsa WHERE province = 'Gauteng'"
If you want to save the whole table, just use the table name in place of the query.
pgsql2shp/shp2pgsql: shapefile to database
shp2pgsql -I -s 4269 C:\MyData\roads\roads.shp roads | psql -d MyDatabase
Other useful queries:
Postgresql from command line
psql -d postgres -U postgres - h localhost -p 5632 -W
Dump from postgresql
Navigate to bin folder (C:\Program Files\PostgreSQL\9.3\bin)
pg_dump -U postgres -h localhost -p 5632 -W -F p postgres > C:\Users\Nathanshaw\Desktop\postgres.sql
Load OSM data into PostGIS
Download osm2pgsql for windows using these instructions. Add to windows path variable and run from folder containing osm download (.osm.pbf). Copy defualt.style from os2pgsql download into this folder. Note – must run with –slim flag. Note – make database hstore compatible (CREATE EXTENSION hstore;)
osm2pgsql great-britain-latest.osm.pbf -d OSM -U postgres -P 5632 -W -S defualt.style --slim --hstore --keep-coastlines
