SpatiaLite Cookbook

	Recipe #13 Isolated Islands
2011 January 28

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:

this time we've used NOT ST_Disjoint() to identify any allowable Spatial relationship between couples of adjacent Local Councils.
and we've used LEFT JOIN for the second instance of the local_councils table (AS lc2): this way we'll be absolutely sure to insert into the result-set every Local Council coming from the left-sided term lc1, because a LEFT JOIN is valid even when the right-sided term lc2 doesn't matches any corresponding entry.
the GROUP BY lc1.lc_id clause is required so to build a distinct aggregation group for each Local Council.
after all this the function Count(lc2.lc_id) will return the number of neighbours for each Local Council:
quite obviously, a value ZERO denotes that this one actually is an isolated Local Council.
and finally we've used the HAVING clause to exclude any not-isolated Local Council.
Please note well: the HAVING clause must not be confused with the WHERE clause.
They only are apparently similar, but a strong difference exists between them:
- WHERE immediately evaluates if a candidate row has to inserted into the result-set or not.
  So, a row discarded by WHERE is completely ignored, and cannot be used in any further step.
- on the other side HAVING is evaluated only when the result-set is completely defined, just immediately before be passed back to the calling process.
  So HAVING is really useful to perform any kind of post-processing, as in this case.
  We simply needed to reduce the result-set (by deleting any not-isolated Local Council), and the HAVING clause was exactly the tool for the job.

Previous Slide

Table of Contents

Next Slide

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

	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.