Working with Geometry

If your database has the postgis extension installed, you have access to the hundreds of functions contained in PostGIS for spatial processing. These spatial functions are normally prefixed with ST_ e.g. ST_Length.

This module explores a few of them and shows how they can be used in Postgres. You can find out more about PostGIS and the functionality it contains on the PostGIS site. Most of these functions are mirrored by processes in QGIS - this module covers how to run them in SQL queries.

As for SQL in Postgres, PostGIS functions are not case sensitive.

Measurements

PostGIS has functions for measurements which depend on the geometry type of the features to be measured.

Length

For lines, ST_Length(geometry) will return the length of each feature in a table, using the name of the geometry column as its only parameter. In the example below, we are also rounding the resulting length to a whole number. The units of the length output are the units of the CRS of the table.

Area

For polygons, ST_Area(geometry) measures the area, excluding internal rings.

Perimeter

For polygons, ST_Perimeter(geometry) measures the perimeter, including internal rings.

Get length, area and perimeter of features

Navigate to the geometry_columns view in the public schema of your database and view all the rows
Choose a LINESTRING table and a POLYGON table
For each table, construct a query to determine the length, area, and/or the perimeter of features as appropriate
Run these queries, and for each one determine the highest value

Transforming Geometry

PostGIS has functions which will transform a geometry, based on one or more existing features.

Buffer

For example, you can use ST_Buffer to create a new set of features which describe an area which is within a set distance of an existing feature - whether it is a LINE, POLYGON or POINT.

Note that because the source lines are individual segments, by default a single buffer feature is generated for each segment. To dissolve all these buffer features into a single MULTIPOLYGON, you would use ST_Union(geometry) to merge the result of the buffer:

CREATE TABLE cycle_network.allroutes_buffer_500m_dissolved AS (SELECT ST_Union(ST_Buffer(wkb_geometry,500)) AS wkb_geometry FROM cycle_network.allroutes)

Intersection

ST_Intersection(geometry) will return the area which is common to the geometries of two layers, as shown in the cross-hatched areas below.

Result of ST_Intersection shown as cross-hatched

Create buffer and intersection

Choose a table from the database, and create a new table representing a 500m buffer around each feature, using the example code above
Review the results in QGIS
Extend your SQL to create a new table of the resulting features into a single MULTIPOLYGON, and again, review the results in QGIS
Create a new layer representing the intersection between two overlapping layers in the database - these could be your new buffer layers, or existing layers, and review the results
If you have time, use ST_Difference to find the area that is not common to two layers

Spatial Relationships and Joins

PostGIS supports the ability to query sets of layers to test for spatial relationships between them. In each case, these functions produce a boolean result, i.e. true or false. For example:

Function and parameters	Description	Notes
ST_Disjoint(a.geometry, b.geometry)	Is there no contact between a feature in table a and a feature in table b?	The reverse of ST_Intersects
ST_Within (a.geometry, b.geometry)	Is the feature in table a completely inside a feature in table b?	The feature in table b must contain all of the feature in table a
ST_Contains(a.geometry, b.geometry)	Does the feature in table a completely contain a feature in table b?	The feature in table a must contain all of the feature in table b
ST_Intersects(a.geometry, b.geometry)	Is there any contact between a feature in table a and a feature in table b?	The reverse of ST_Disjoint

These functions in turn allow spatial joins to be created within tables, where instead of using equality between values in corresponding tables to make a join, the SQL looks for a spatial relationship.

For example, this query creates a list of settlements with the name of the parish which contains them, using ST_Within.

Create a spatial selection

Working from the example above, write a query which creates a new table with all the features in the cycle.network.allroutes table which intersect a feature in the general.urban_areas table
Include the geometry column in your output, so the result can be viewed in QGIS
View the results in QGIS, and check that they are as expected
If you have time, construct a query with one of the other spatial functions in the table above

SQL-03 13: Working with Geometry