Detecting adjacent polygons using QGIS Virtual Layers

Challenge:-

- to take a series of polygons in a shapefile
- find which pairs of polygons share a border.
- draw a line between the centroids of adjacent polygons.
- Do this without manually loading into a database.

This is possible in QGIS using DB Manager with Virtual Layers. This uses Spatialite under the hood - I'm more used to PostGIS, but many of the functions have the same name.

finding polygons which share a border, excluding those which touch at a point

finding polygons which share a border, excluding those which touch at a point

Using the following query in the DB Manager SQL window, and adding the view as a new layer...

select
    a.gid,
    b.gid,
    make_line(st_pointonsurface(a.geometry), st_pointonsurface(b.geometry)) as edge
from
    areas20170628201438984 as a,
    areas20170628201438984 as b
where
    a.gid != b.gid and
    a.gid < b.gid and
    st_relate(a.geometry,b.geometry,'FF2F11212');

The st_relate value of FF2F11212 will find polygons whose intersection is a line. This means that polygons which intersect at a point are ignored.

The a.gid < b.gid makes sure there's only one line between each polygon pair. (Similar to filling in one diagonal half of a matrix)

st_pointonsurface is similar to st_centroid, but guarantees that the points are inside the polygon... given the irregular shapes of UK Census Output Areas, this is more useful than st_centroid.

I find virtual layer queries like this are painfully slow to render when the source layer is a shapefile; it's probably better to do this on a layer from a database ;-)

TIP: I also found that I had to run Execute (F5) twice to allow the 'edge' geometry field to be selected when adding the layer. (QGIS 2.18.7).