SpatiaLite logo

Recipe #12
Neighbours

2011 January 28

Previous Slide Table of Contents Next Slide

The problem

  • Obviously each Local Council shares a common boundary with a neighbour:
    [not an absolute rule, anyway: e.g. small islands are self-contained].
  • We'll use Spatial SQL to identify every adjacent couple of Local Councils.
  • Just to add some further complexity, we'll specifically focus our attention on the Tuscany region boundaries.

Tuscan Local Council Tuscan County Neighbour LC County Region
ANGHIARI AREZZO CITERNA PERUGIA UMBRIA
AREZZO AREZZO MONTE SANTA MARIA TIBERINA PERUGIA UMBRIA
BIBBIENA AREZZO BAGNO DI ROMAGNA FORLI' - CESENA EMILIA-ROMAGNA
CHIUSI DELLA VERNA AREZZO BAGNO DI ROMAGNA FORLI' - CESENA EMILIA-ROMAGNA
CHIUSI DELLA VERNA AREZZO VERGHERETO FORLI' - CESENA EMILIA-ROMAGNA
... ... ... ... ...


SELECT lc1.lc_name AS "Local Council",
  lc2.lc_name AS "Neighbour"
FROM local_councils AS lc1,
  local_councils AS lc2
WHERE ST_Touches(lc1.geometry, lc2.geometry);
This first query is really simple: Anyway, a so simplistic approach implies several (strong, severe) issues:

SELECT lc1.lc_name AS "Local Council",
  lc2.lc_name AS "Neighour"
FROM local_councils AS lc1,
  local_councils AS lc2
WHERE lc2.ROWID IN (
  SELECT pkid
    FROM idx_local_councils_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(lc1.geometry),
      MbrMinY(lc1.geometry),
      MbrMaxX(lc1.geometry),
      MbrMaxY(lc1.geometry)));
Happily enough, we can perform such Spatial queries in a really fast and efficient way: Just to explain better what's going on, you can imagine that this SQL query is processed using the following steps:

SELECT lc1.lc_name AS "Tuscan Local Council",
  c1.county_name AS "Tuscan County",
  lc2.lc_name AS "Neighbour LC",
  c2.county_name AS County,
  r2.region_name AS Region
FROM local_councils AS lc1,
  local_councils AS lc2,
  counties AS c1,
  counties AS c2,
  regions AS r1,
  regions AS r2
WHERE c1.county_id = lc1.county_id
  AND c2.county_id = lc2.county_id
  AND r1.region_id = c1.region_id
  AND r2.region_id = c2.region_id
  AND r1.region_name LIKE 'toscana'
  AND r1.region_id <> r2.region_id
  AND ST_Touches(lc1.geometry, lc2.geometry)
  AND lc2.ROWID IN (
    SELECT pkid
      FROM idx_local_councils_geometry
      WHERE pkid MATCH RTreeIntersects(
        MbrMinX(lc1.geometry),
        MbrMinY(lc1.geometry),
        MbrMaxX(lc1.geometry),
        MbrMaxY(lc1.geometry)))
ORDER BY c1.county_name, lc1.lc_name;
All right, once we have resolved the Spatial Index stuff writing the whole SQL query isn't so difficult.
Anyway this one is a rather complex query, so some further explanation is surely welcome:
SELECT lc1.lc_name AS "Tuscan Local Council",
  c1.county_name AS "Tuscan County",
  lc2.lc_name AS "Neighbour LC",
  c2.county_name AS County,
  r2.region_name AS Region
FROM local_councils AS lc1,
  local_councils AS lc2
JOIN counties AS c1
  ON (c1.county_id = lc1.county_id)
JOIN counties AS c2
  ON (c2.county_id = lc2.county_id)
JOIN regions AS r1
  ON (r1.region_id = c1.region_id)
JOIN regions AS r2
  ON (r2.region_id = c2.region_id)
WHERE r1.region_name LIKE 'toscana'
  AND r1.region_id <> r2.region_id
  AND ST_Touches(lc1.geometry, lc2.geometry)
  AND lc2.ROWID IN (
    SELECT pkid
      FROM idx_local_councils_geometry
      WHERE pkid MATCH RTreeIntersects(
        MbrMinX(lc1.geometry),
        MbrMinY(lc1.geometry),
        MbrMaxX(lc1.geometry),
        MbrMaxY(lc1.geometry)))
ORDER BY c1.county_name, lc1.lc_name;
Obviously you can this query adopting the alternative syntax for JOINs: the difference simply is syntactic.
And doesn't implies any difference at functional or performance levels.

Performing sophisticated Spatial Analysis not necessarily is an easy and plain task.
Mastering complex SQL queries is a little bit difficult (but not at all impossible).
Optimizing such complex SQL, so to get fast answers surely requires some extra-care and attention.

But Spatial SQL supports you in the most effective (and flexible) way: the results you can get simply are fantastic.
After all the game surely is worth the candle.

Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.