SpatiaLite logo

Recipe #13
Isolated Islands

2011 January 28

Previous Slide Table of Contents Next Slide

The problem

Very closely related to the latest one. Now the problem is:
  • identify any isolated Local Council, i.e. each one doesn't sharing a common boundary with any other Italian Local Councils.

Local Council County Region
CAMPIONE D'ITALIA COMO LOMBARDIA
CAPRAIA ISOLA LIVORNO TOSCANA
CARLOFORTE CAGLIARI SARDEGNA
FAVIGNANA TRAPANI SICILIA
ISOLA DEL GIGLIO GROSSETO TOSCANA
ISOLE TREMITI FOGGIA PUGLIA
LA MADDALENA SASSARI SARDEGNA
LAMPEDUSA E LINOSA AGRIGENTO SICILIA
LIPARI MESSINA SICILIA
PANTELLERIA TRAPANI SICILIA
PONZA LATINA LAZIO
PROCIDA NAPOLI CAMPANIA
USTICA PALERMO SICILIA
VENTOTENE LATINA LAZIO

Please note: quite all the above listed Local Councils are small sea island.
With the remarkable exception of Campione d'Italia, which is a land island:
i.e. a small Italian enclave completely surrounded by Switzerland.

SELECT lc1.lc_name AS "Local Council",
  c.county_name AS County,
  r.region_name AS Region
FROM local_councils AS lc1
JOIN counties AS c ON (
  c.county_id = lc1.county_id)
JOIN regions AS r ON (
  r.region_id = c.region_id)
LEFT JOIN local_councils AS lc2 ON (
  lc1.lc_id <> lc2.lc_id
  AND NOT ST_Disjoint(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))))
GROUP BY lc1.lc_id
HAVING Count(lc2.lc_id) = 0
ORDER BY lc1.lc_name;
Nothing really new in this: more or less, this is quite exactly the same we've already examined in the latest example.
Just few differences are worth to be explained:
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.