PostgreSQL & PostGIS command line introduction

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s