PostGIS Tutorial

Creating a PostGIS database

CREATE DATABASE your_database WITH TEMPLATE = template_postgis;

Details about the template

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 

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

Inserting data into that PostGIS Table

insert into postgis_your_netid (ID,geometry,centroid) 
values  (
   GeomFromText('POLYGON( (-128 50, -128 49.948, -127.874 49.948, -127.874 50, -128 50))', 4326),
   GeomFromText('POINT(-127.37 49.974)', 4326)
insert into postgis_your_netid (ID,geometry,centroid) 
values  (
   GeomFromText('POLYGON( (-130 50, -130 20, -70 20, -70 50, -130 50))', 4326),
   GeomFromText('POINT(-100 25)', 4326)
 select * from postgis_your_netid

Loading data from shape files

shp2pgsql -c -i -s 4326 /srv/cigi/example/states/states.shp public.postgis_states_your_netid > $HOME/states.sql
psql -U geog480 -d tutorial -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(the_geom) from postgis_states_your_netid where state_name ilike 'illinois';