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= As of '15-Dec-2008', the latest release is 'PostGIS 1.3.5'.

Current releases for a variety of operating systems can be downloaded [http://postgis.refractions.net/download/ here].

Ubuntu users may wish to start here: [http://postgis.refractions.net/support/wiki/index.php?PostgisOnUbuntu Getting started on Ubuntu]

=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

* [http://www.postgresonline.com/journal/index.php?/archives/7-PostGIS-for-geospatial-analysis-and-mapping.html Brief article plus additional resource links]

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

postgis.txt · Last modified: 2012/02/07 11:32 (external edit)