8. 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. 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.
8.1. Measurements
PostGIS has functions for measurements which depend on the geometry type of the features to be measured.
8.1.1. Length
For lines, ST_Length 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.
8.1.2. Area
For polygons, ST_Area measures the area, excluding internal rings.
Get length, area, 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 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
8.2. Transforming Geometry
PostGIS has functions which will transform a geometry, based on one or more existing features.
8.2.1. 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 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)
8.2.2. Intersection
ST_Intersection will return the area which is common to the geometries of two layers, as shown in the cross-hatched areas below.
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 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
8.3. Spatial Relationships and Joins
PostGIS support 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 |