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.
', the latest release is
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…=
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%';
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:
Note that in a *nix environment, you can achieve the same thing in a single statement:
mydatabase | psql
This dumps the spatial table
mytable contained in
mydatabase into a shapefile called
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
mydatabase “select * from
mytable where state='CA'”
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.
mybuffers WITH oids AS SELECT
siteid, ST_Buffer(the_geom, 1000) AS the_geom FROM
=PostGIS resources on the web=
* [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)
* [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