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.

Measurements

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

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.

Area

For polygons, ST_Area measures the area, excluding internal rings.

Get length, area, perimeter of features

1. Navigate to the geometry_columns view in the public schema of your database and view all the rows

2. Choose a LINESTRING table and a POLYGON table

3. For each table, construct query to determine the length, area, and/or the perimeter of features as appropriate

4. 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.

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:

sql

Spatial Joins