Postgres 4 Planners : Automating Site Constraint Checks

Introduction

In terms of the Housing Land Audit , Employment land and Vacant and Derelict land it is important to quickly identify what constraints/policies and education catchment areas a site might be within. Traditionally this was often done manually by a planner pouring over site maps and the related constraint boundaries. A lot of organisations now have digital maps. PostGIS extension for Postgres contains a function called ST_Within which automates this process. Below is a simple detailed example of ST_Within being used.

First off let us create a new database to hold our examples in.

SQL
CREATE DATABASE stwithindb;

Now add the postgis extension.

SQL
CREATE EXTENSION POSTGIS;

Lets create two tables one called fields and one called plots

SQL
CREATE TABLE t00001fields
(pkid serial primarykey,
fieldname varchar(50),
geom geometry(polygon,27700));

CREATE TABLE t00002Plots
(pkid serial primarykey,
plotname varchar(50),
geom geometry(polygon,27700));

Now lets go to QGIS connect to the PostGIS instance add the tables and create some test data manually.

Here I have added fields in green with bold number labels and plots in brown with smaller number labelling. The numbers represent the pkid fields.

Now here I can quickly run a query to identify the plots that are in fields

SQL
SELECT 
t00002plots.pkid
FROM
t00002plots,
t00001fields
WHERE
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

And it correctly identifies that plot 1 is within the fields layer.

But what would be great in an application is to have some kind of junction table that individual master records could display their children on. For this we need a junction table that links between the field and plots table showing the pkids from each.

SQL
SELECT t00002plots.pkid as Plotspkid,
t00001fields.pkid as Fieldspkid 
FROM
t00002plots,
t00001fields
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

Now I will move plot 2 into field 3 and rerun the above.

The layer now looks like

and running the former query we get.

Now its possible to either create a junction table to hold this information..

eg

SQL
CREATE TABLE t00010fieldplotjunction AS 
SELECT t00002plots.pkid asPlotspkid,
t00001fields.pkid as Fieldspkid 
FROM 
t00002plots,
t00001fields 
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

or we can create a view that will constantly calculate this every time it is seen

SQL
CREATE VIEW v001FieldPlotJunction AS 
SELECT t00002plots.pkid as Plotspkid,
t00001fields.pkid as Fieldspkid 
FROM 
t00002plots,
t00001fields 
WHERE 
ST_WITHIN(PUBLIC.T00002PLOTS.GEOM, PUBLIC.T00001FIELDS.GEOM);

Now if I add a few more plots and fields and then pull up the view we shall see that everything has been adjusted

and running the view we now get

In some circumstances this calculation may be expensive so we may wish to run and create a junction table overnight other times we may be happy to do it fully dynamically. Of course in a front end you could query and filter such that only one record was compared against the fields plot at anytime. Very useful nonetheless.