User Tools

Site Tools


postgis

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.

Installation guides

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

Postgis installation on Ubuntu LTS 14.04

Add postgres repository:

sudo sh -c 'echo “deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main” » /etc/apt/sources.list'

Add the Keys:

wget –quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

Install postgis:

sudo apt-get -y install postgresql-9.4 postgresql-contrib postgresql-9.4-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;

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 psql -f filename -d mydatabase

Note that in a *nix 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

* [http://www.foss4g2007.org/workshops/W-04 Excellent PostGIS tutorial materials] (FOSS4G workshop, 2007) * [http://www.bostongis.com/ Boston GIS tutorials] (includes tutorials for PostGIS and other applications)

More on PostGIS
Extras

* [http://pgrouting.postlbs.org/ The pgRouting Project]: Provides routing functionality for PostGIS

postgis.txt · Last modified: 2015/02/26 12:53 by brun