Computing

PostGIS is an open source extension to PostgreSQL that is often described as “spatially enabling” a PostgreSQL database. Because PostGIS understands complex geometries, it can be used not only to store spatial datasets, but also to query, summarize, and manipulate them based on spatial relationships. Using SQL statements, one can perform numerous common spatial operations such as overlays, joins, intersection, buffering, and much more. PostGIS can also serve as the backend database for powerful analysis and visualization applications like Quantum GIS, GRASS, and Mapserver.

What can PostGIS do?

Nice overview of the PostGIS functionalities by Paul Ramsey at FOSS4G North America 2015:

https://2015.foss4g-na.org/session/postgis-feature-frenzy

Installation guide

Ubuntu users may wish to start here: http://trac.osgeo.org/postgis/wiki/UsersWikiMain

Postgis installation on Ubuntu LTS 14.04

Add the GIS repository (use the unstable one):

sudo apt-get install python-software-properties

sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable

Install postgres with the postgis extension:

sudo apt-get install postgis postgresql-9.3-postgis-2.1

Enable the command-line tools to work from your shell:

sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/shp2pgsql

sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/pgsql2shp

sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/raster2pgsql

Setting postgres password: (not mandatory)

sudo -u postgres psql postgres

\password postgres

Enable the admin pack: (not mandatory)

CREATE EXTENSION adminpack;

Create a geodatabase:

CREATE DATABASE geodb;

\c geodb;

Activate PostGIS (with raster):

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

Other spatial extension (facultative):

TIGER geocoder http://postgis.net/docs/manual-dev/Extras.html#Tiger_Geocoder :

CREATE EXTENSION fuzzystrmatch;

CREATE EXTENSION postgis_tiger_geocoder;

REMEMBER to store your data in a schema other than “public”, because the “public” schema is where the PostGIS functions and system tables get installed. So if you dump that schema you get all those definitions in your dump. If those definitions are mixed in amongst your data, loading them into a fresh database, as required for upgrades, gets tricky. See http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html

Postgres Optimization

Out of the box, postgres is not optimized for powerful servers and PostGIS requires some specific settings. Here are 3 references to guide the installation tuning:

https://postgis.net/workshops/postgis-intro/tuning.html

http://www.anchor.com.au/hosting/dedicated/Tuning_PostgreSQL_on_your_Dedicated_Server

http://www.revsys.com/writings/postgresql-performance.html

How can I…

Import a shapefile into a PostGIS database

Below, myshapefile.shp is a shapefile to be imported as a table called mytable in the PostGIS database called mydatabase. You should supply an SRID (i.e, spatial reference ID) corresponding to the projection of your shapefile. Projection information is stored in the myshapefile.prj file, although the .prj file is not always present. Assuming you are able to determine the projection name from the .prj file (or other metadata), there are several ways to determine the SRID. Within PostGIS itself, it is easy to query the included spatial_ref_sys table. For example, if inspection of myshapefile.prj revealed that the projection is “NAD83 / UTM zone 10N”, the associated SRID could be retrieved using the following SQL statement:

mydatabase=# SELECT srid FROM spatial_ref_sys WHERE srtext LIKE '%NAD83 / UTM zone 10N%';

srid  

26910

As an alternative, see [http://spatialreference.org/ here] for a simple web search of spatial reference system (SRS) codes.

Then use the following command to load the shapefile (with SRID 26910 in this example) into PostGIS:

shp2pgsql -s 26910 -D myshapefile.shp mytable mydatabase > filename.sql

psql -f filename.sql -d mydatabase

Note that in a Unix environment, you can achieve the same thing in a single statement:

shp2pgsql -s 26910 -D myshapefile.shp mytable mydatabase | psql mydatabase

Export a shapefile from a PostGIS database

This dumps the spatial table mytable contained in mydatabase into a shapefile called myshapefile.shp:

pgsql2shp -f myshapefile.shp mydatabase mytable

Note that arbitrary SQL statements can be used to create the shapefile, rather than simply dumping a table. For example, the following command will create a shapefile consisting only of certain features from mytable:

pgsql2shp -f myshapefile.shp mydatabase select * from mytable where state='CA'

Buffer points

In this example, mypoints is a spatial points table with an attribute column siteid. This SQL statement generates a new spatial polygon table containing circular buffers of 1000 map units around each of the points. Note that the map units depend on the spatial reference system assigned to your table.

CREATE TABLE mybuffers WITH oids AS SELECT siteid, ST_Buffer(the_geom, 1000) AS the_geom FROM mypoints;

PostGIS resources on the web

Resource portals
Online tutorials and demos
More on PostGIS
Extras