Versions Compared

Old Version 13

changes.mady.by.user Kim Stimpson

Saved on

compared with

New Version Current

changes.mady.by.user Antony Scott (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Create buffer and intersection

  1. Choose a table from the database, and create a new table representing a 500m buffer around each feature, using the example code above

  2. Review the results in QGIS

  3. Extend your SQL to create a new table of the resulting features into a single MULTIPOLYGON, and again, review the results in QGIS

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

  5. 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:

Numberedheadings
number-formatdecimal
skip-headings
start-numbering-with83
h1
h2
h3
h4
h5
enabledtrue
h6
start-numbering-atH1

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

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

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:

sql

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

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

  1. Working from the example above, write a query which creates a new table with all the features in the

cycle

  1. cycle.network.allroutes table which intersect a feature in the general.urban_areas table

  2. Include the geometry column in your output, so the result can be viewed in QGIS

  3. View the results in QGIS, and check that they are as expected

  4. If you have time, construct a query with one of the other spatial functions in the table above