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.

 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.