PostGIS Tutorial

Creating a PostGIS database

CREATE DATABASE your_database WITH TEMPLATE = template_postgis;

Details about the template

 \d spatial_ref_sys
 \d geometry_columns
 select count(*) from spatial_ref_sys;

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:

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 TABLE postgis_your_netid ( ID int4);
SELECT AddGeometryColumn('', 'postgis_your_netid', 'geometry', 4326, 'POLYGON', 2 );
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  (
   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)
 select * from postgis_your_netid;

Loading data from shape files

/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

Retrieving data from PostGIS

  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;


Back to class homepage