PostGIS Tutorial

Creating a PostGIS database

CREATE DATABASE your_database WITH TEMPLATE = template_postgis;
  • This step is already done on geog480.
  • We have a database named 'tutorial' created with the right privileges.
  • Connecting to DB: Refer to DB tutorial

Details about the template

  • Creating a database the template creates two tables named spatial_ref_sys and geometry columns
  • The table definitions for these metadata tables are as follows (both these tables are already created)
CREATE TABLE spatial_ref_sys ( 
  auth_name  VARCHAR(256), 
  auth_srid  INTEGER, 
  srtext     VARCHAR(2048), 
  proj4text  VARCHAR(2048) 


CREATE TABLE geometry_columns ( 
  f_table_catalog    VARRCHAR(256) NOT NULL, 
  f_table_schema     VARCHAR(256) NOT NULL,
  f_table_nam        VARCHAR(256) NOT NULL, 
  f_geometry_column  VARCHAR(256) NOT NULL, 
  coord_dimension    INTEGER NOT NULL, 
  srid               INTEGER NOT NULL, 
  type               VARCHAR(30) NOT NULL 
  • These are two OpenGIS meta-data tables are defined in OpenGIS standards to ensure mata-data consistencies.
  • The spatial_ref_sys table is a OGC compliant database table that lists over 3000 known spatial reference systems. It holds the numeric IDs and textual descriptions of coordinate systems used in the spatial database.

Connecting to Database

Use the following command to connect to tutorial database

\c tutorial

Using PostGIS

GIS Objects

OpenGIS Consortium (OGC) “Simple Features for SQL” specification defines a set of “Simple Features” (i.e. basic objects and functions) that need to be supported by a Spatial database. The specification defines the Well-Known Text (WKT) form and the Well-Known Binary (WKB) form as two ways of expressing spatial objects.

Example of WKT representation of spatial objects:

  • POINT(1 1)
  • POLYGON( (1 1,3 1,3 3,3 1,1 1) )
  • GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(1 1,3 3),POLYGON( (1 1,1 3,3 3,3 1,1 1) ))

GIS objects supported in PostGIS is a superset of “Simple Features”. It extends the standard with support for 3DZ,3DM and 4D coordinates. Complete details of GIS Objects supported by by PostGIS can be found here

Creating a table with GIS Objects

  • Create a regular table
CREATE TABLE postgis_your_netid ( ID int4);
  • Add a GIS column named geometry of type polygon
SELECT AddGeometryColumn('', 'postgis_your_netid', 'geometry', 4326, 'POLYGON', 2 );
  • Syntax of AddGeometryColumn: AddGeometryColumn(<schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension>)
  • Why use the AddGeometryColumn() function and all the other OpenGIS functions?
    • In addition to creating a new column, using AddGeometryColumn also adds metadata that a simple create statement wont add. E.g. the metadata in geometry_columns added by AddGeometryColumn is used by Mapservers to ensure correct map projections.
  • Another geometry column
SELECT AddGeometryColumn( 'postgis_your_netid', 'centroid', 4326, 'POINT', 2 );

* Review the metadata table (geometry_columns) for your table

SELECT * from geometry_columns where f_table_name='postgis_your_netid';

Inserting data into that PostGIS Table

insert into postgis_your_netid (ID,geometry,centroid) 
values  (
   ST_GeomFromText('POLYGON( (-128 50, -128 49.948, -127.874 49.948, -127.874 50, -128 50))', 4326),
   ST_GeomFromText('POINT(-127.37 49.974)', 4326)
insert into postgis_your_netid (ID,geometry,centroid) 
values  (
   ST_GeomFromText('POLYGON( (-130 50, -130 20, -70 20, -70 50, -130 50))', 4326),
   ST_GeomFromText('POINT(-100 25)', 4326)
  • Now look at the data table
 select * from postgis_your_netid;
  • You will notice that the geometry data is the WKB format.

Loading data from shape files

  • Postsql provides a tool which facilitates the conversion of from shp files to postgis database.
  • Example
/usr/lib/postgresql/9.2/bin/shp2pgsql -c -i -s 4326 /srv/cigi/example/states/usa_states.shp public.postgis_states_your_netid > $HOME/states.sql
psql -U geog480 -d tutorial -f $HOME/states.sql
  • You will find that the contents of your shapefiles are now in the database table named postgis_states_your_netid
  • NOTE: There is a complementary command 'pgsql2shp' which can be used to create shape file from PostGIS tables

Retrieving data from PostGIS

  • Using SQL statements
  select id, ST_AsText(geometry), ST_AsText(centroid) from postgis_your_netid;
  select state_name, state_fips, ST_AsText(geom) from postgis_states_your_netid where state_name ilike 'illinois';