PostGIS Tutorial

Creating a PostGIS database

CREATE DATABASE your_database WITH TEMPLATE = template_postgis;
  • This step is already done on n3.
  • We have 2 databases group1_postgis and group2_postgis, created with the right privileges

Additional information 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 (
  srid       INTEGER NOT NULL PRIMARY KEY,
  auth_name  VARCHAR(256),
  auth_srid  INTEGER,
  srtext     VARCHAR(2048),
  proj4text  VARCHAR(2048)
)

and

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

Creating a table that GIS Objects

  • Create a regular table
CREATE TABLE test_tutorial_postgis ( ID int4);
  • Add a GIS column named geometry of type polygon
SELECT AddGeometryColumn(
  '',
  'test_tutorial_postgis',
  '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.

Delete a GIS object column

SELECT DropGeometryColumn('', 'test_tutorial_postgis','geometry');

Inserting data into that postGIS Table

insert into
  test_tutorial_postgis (ID,geometry)
values
  (
   1,
   GeomFromText('POLYGON( (-128 50, -128 49.948, -127.874 49.948, -127.874 50, -128 50))',
                  4326)
  );

Perform spatial query

  • E.g. Find all objects with 100 meters of POINT(-128 49.948 49.948)
  SELECT id
  FROM test_tutorial_postgis
  WHERE ST_DWithin(geometry, GeomFromText('POINT(-128 49.948 49.948)',4326), 100.0);

References