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 'fall14' created with the right privileges.
  • Connecting to DB: Refer to Database Turorial

Details about the template

  • Creating a database the template creates five tables named spatial_ref_sys, geography_columns, geometry_columns, raster_columns, and raster_overviews
  • To descrive detailed schemas of these tables/view use
 \d spatial_ref_sys
 \d geometry_columns
  • To view the contents of the table or view use select statements
 e.g   
 select count(*) from spatial_ref_sys;
  • These tables store metadata information and are defined in OpenGIS standards to ensure matadata 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 fall14

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 object 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';

* Alternative approach to create a table (supported from postgis 2.0). This approach automatically adds metadata.
CREATE TABLE postgis_your_netid_2 ( ID int4, NAME varchar(25), geom geometry(LINESTRING,4326) );

Inserting data into that PostGIS Table

insert into postgis_your_netid (ID,geometry,centroid) 
values  (
   1,  
   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  (
   2,  
   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.
  • Please note you need to be outside the database (on linux prompt) for this step
    • Use \q to quit DB
  • 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 fall14 -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 (connect back to database)
  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';
  select state_name from postgis_states_your_netid;
  select count(state_name) from postgis_states_your_netid;

References