PostGIS

From NCEAS Knowledge Base

Jump to: navigation, search

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.


Contents

Installation guides

As of 12-Apr-2008, the latest release is PostGIS 1.3.3.

Current releases for a variety of operating systems can be downloaded here.

Ubuntu users may wish to start here: Getting started on Ubuntu

Windows users may find helpful setup tips here: Getting started on Windows


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 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

More on PostGIS


Extras

Retrieved from "http://help.nceas.ucsb.edu/PostGIS"
Personal tools