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.
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.
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)) FROM cycle_network.allroutes)