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 

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:

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  (
   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 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(geom) from postgis_states_your_netid where state_name ilike 'illinois';