Lab Session 8 - Building a Spatial Database using PostGIS

By Mitchell Welch

University of New England

Reading

Summary

Introduction

A spatial database, or geo-database is a database that is optimized to store and query data that represents objects defined in a geometric space. Most spatial databases allow representing simple geometric objects such as points, lines and polygons. Some examples:

Geospatial databases allow for the implementation of a wide range of application that contain spatial data.

The PostGIS database extension provides the Geometry datatype and a suite of functions to the PostgreSQL DBMS for implementing spatial database tables and spatial queries.

House Keeping.

For todays practical, you have to use the database prac_08. This database has already been created for you and has has the spatial database extensions enabled. (Enabling the extensions for a database requires administrator access that your accounts don’t have). Because you will be sharing this database with other users, you should prefix your database tables with your UNE username. Note that other users can’t access your database tables and you cannot access other users database tables.


[mwelch8@turing mwelch8]$ psql prac_08
psql (9.4.4)
Type "help" for help.

prac_08=> \dt
             List of relations
 Schema |      Name       | Type  |  Owner  
--------+-----------------+-------+---------
 public | capital         | table | jknife
 public | roads           | table | mwelch8
 public | spatial_ref_sys | table | stadmin
 public | tablename       | table | mwelch8
 public | testing         | table | mwelch8
(5 rows)

prac_08=>

The Geometry Datatype

The Geometry datatype extends the existing data-model in PostgreSQL and can be used to create Geometry-valued attributes within database tables. The Geometry datatype can be used to represent:

Tables with geometry-typed columns can be generated in the same way as any other postgreSQL table:


CREATE TABLE ROADS ( 
    ID int4, 
    ROAD_NAME varchar(25), 
    geom geometry(LINESTRING,4326) );

This creates a table called ROADS that contains a Geometry-typed column. In this example, we have (optionally) specified that the geometry will contain line objects and use the WGS84 coordinate system (with ID:4326).


CREATE TABLE ROADS ( 
    ID int4, 
    ROAD_NAME varchar(25), 
    geom geometry );

This declaration is also valid, and will allow any geometry object to be assigned to the column. Defaults will be used for the coordinate system.

Once we have our spatial table, we can populate it with some spatial data. To do this we will make use of the well-known text representation. Spatial object are created using one of the constructor functions listed in: http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors

In our first example, we will insert 2 records into the ROADS table that contain a simple line in geom column.

INSERT INTO ROADS(ID,ROAD_NAME,geom) 
VALUES (1, 'Test Road', ST_LineFromText('LINESTRING(1 2, 3 4)'));
INSERT INTO ROADS(ID,ROAD_NAME,geom) 
VALUES (1, 'Test Road', ST_LineFromText('LINESTRING(5 6, 3 7)'));

Run:

SELECT * FROM ROADS;

And observe the result. Notice that the geometry column contains binary data. The only way to access the data in a meaningful way is through the accessor functions: http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors

For example, we can return the end point of each line in our table:

SELECT ST_EndPoint(geom) FROM ROADS;

This returns the binary representation of the line objects. We can access the individual points through the use of the ST_AsText function:

SELECT ST_AsText(ST_EndPoint(geom)) FROM ROADS;

Alternatively, we can access the individual x and y coordinate values from the geometry using the ST_X and ST_Y functions. These return float-typed values:

SELECT ST_X(ST_EndPoint(geom)), ST_Y(ST_EndPoint(geom)) FROM ROADS;

Spatial Queries

Once we have some spatial data within our database, we can carry out some queries on this data. Spatial queries are usually based around conditions that are related to the spatial properties of the geometry object. We can build out spatial queries using the spatial relationship functions provided by PostGIS: http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements

Example 1: find the distance between all geometry objects within the ROADS table:


SELECT ST_AsText(R1.geom), ST_AsText(R2.geom), ST_Distance(R1.geom, R2.geom)
FROM ROADS as R1, ROADS as R2;

In this query we include the ROADS relation twice so that we can compare each geometry object against every other geometry object. The objects are compared using the ST_distance function.

Example 2: Determine if any part of each geometry within the database table touches any other geometry object:

SELECT ST_AsText(R1.geom), ST_AsText(R2.geom), ST_touches(R1.geom, R2.geom)
FROM ROADS as R1, ROADS as R2;

The ST_Touches returns true if there are any points in common between the geometries: http://postgis.net/docs/manual-2.1/ST_Touches.html

Example 3: Return the shortest line between the closest two points of each geometry in the ROADS table:

SELECT ST_AsText(R1.geom), ST_AsText(R2.geom), ST_AsText(ST_ShortestLine(R1.geom, R2.geom))
FROM ROADS as R1, ROADS as R2;

Exercises for You

Your task is to construct a database table that stores the name and location of each state capital within Australia. Your database table should include a POINT-type geometry object that represents the location of each city.

Once you have constructed the table, create a query that finds the distance between each capital city and every other capital city. You should do this by using the ST_Distance function.

Construct a second query that returns all capital cities that are within 1000Km of Melbourne.