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.

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