Different methods to calculate polygon neighbors in QGIS

index.md

Different methods to calculate polygon neighbors in QGIS

kgjenkins, 2020-11-10

Before using any of the methods below, make sure that your polygon layer has a spatial index.
Reported timings are based on test data of 1606 counties in the eastern US.

Method 1: Virtual Layers SQL query

select
  a.GEOID,
  group_concat(b.GEOID) as neighbors,
  a.geometry
from test as a, test as b
where st_intersects(a.geometry, b.geometry)
  and a.GEOID <> b.GEOID
  and a._search_frame_ = b._search_frame_
group by a.GEOID

83 seconds (shapefile)
105 seconds (gpkg)
plus more time to load as layer, and then to export to file (50s gpkg)

Method 2: Field Calculater -- aggregate query

aggregate(
  layer:= 'counties copy',
  aggregate:= 'concatenate',
  expression:= GEOID,
  concatenator:= ',',
  filter:= GEOID<>attribute(@parent, 'GEOID') and intersects($geometry, geometry(@parent))
)

59 seconds (shapefile)

Method 3: Spatial Join then group via SQL

Processing tool "Join attributes by location"

1.8 seconds

Then Virtual Layer SQL query to combine neighbor IDs into a single field:

select
  GEOID,
  count(GEOID_2) as n,
  group_concat(GEOID_2) as neighbors,
  geometry
from Joined
where GEOID <> GEOID_2
group by GEOID

Method 4: DB Manager -- GeoPackage query

select
  a.GEOID,
  group_concat(b.GEOID) as neighbors,
  a.geom
from testgpkg as a, testgpkg as b
where st_intersects(a.geom, b.geom)
  and a.GEOID <> b.GEOID
group by a.GEOID

14 seconds to run
26 to load as layer
28 to draw
28 to export to gpkg