SpatiaLite logo

recipe #11
Guinness Book of Records

2011 January 28

Previous Slide Table of Contents Next Slide

Local Council County Region
ATRANI SALERNO CAMPANIA
BARDONECCHIA TORINO PIEMONTE
BRIGA ALTA CUNEO PIEMONTE
CASAVATORE NAPOLI CAMPANIA
LAMPEDUSA E LINOSA AGRIGENTO SICILIA
LU ALESSANDRIA PIEMONTE
MORTERONE LECCO LOMBARDIA
NE GENOVA LIGURIA
OTRANTO LECCE PUGLIA
PINO SULLA SPONDA DEL LAGO MAGGIOR VARESE LOMBARDIA
PREDOI BOLZANO TRENTINO-ALTO ADIGE
RE VERBANO-CUSIO-OSSOLA PIEMONTE
RO FERRARA EMILIA-ROMAGNA
ROMA ROMA LAZIO
SAN VALENTINO IN ABRUZZO CITERIORE PESCARA ABRUZZO
VO PADOVA VENETO

The above list of Local Councils really is a kind of Guinness Book of Records.
For one reason or the other each one of them really has something absolutely exceptional and worth of note.
May well be you are really puzzled and surprised while reading this, because (with the notable exception of Rome) none of them is within the most renowned places of Italy.
Anyway, the explanation is really simple: Discovering such highly (un)useful Guinness Records collection is quite easy.
You simply have to execute the following SQL query.

SELECT lc.lc_name AS LocalCouncil,
  c.county_name AS County,
  r.region_name AS Region,
  lc.population AS Population,
  ST_Area(lc.geometry) / 1000000.0 AS "Area sqKm",
  lc.population / (ST_Area(lc.geometry) / 1000000.0)
    AS "PopDensity [peoples/sqKm]",
  Length(lc.lc_name) AS NameLength,
  MbrMaxY(lc.geometry) AS North,
  MbrMinY(lc.geometry) AS South,
  MbrMinX(lc.geometry) AS West,
  MbrMaxX(lc.geometry) AS East
FROM local_councils AS lc
JOIN counties AS c ON (c.county_id = lc.county_id)
JOIN regions AS r ON (r.region_id = c.region_id)
WHERE lc.lc_id IN (
    SELECT lc_id
    FROM local_councils
    WHERE population IN (
      SELECT Max(population)
      FROM local_councils
    UNION
      SELECT Min(population)
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE ST_Area(geometry) IN (
      SELECT Max(ST_area(geometry))
      FROM local_councils
    UNION
      SELECT Min(ST_Area(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE population / (ST_Area(geometry) / 1000000.0) IN (
      SELECT Max(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils
    UNION
      SELECT MIN(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE Length(lc_name) IN (
      SELECT Max(Length(lc_name))
      FROM local_councils
    UNION
      SELECT Min(Length(lc_name))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxY(geometry) IN (
      SELECT Max(MbrMaxY(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinY(geometry) IN (
      SELECT Min(MbrMinY(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxX(geometry) IN (
      SELECT Max(MbrMaxX(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinX(geometry) IN (
      SELECT Min(MbrMinX(geometry))
      FROM local_councils));

Oh yes, this one surely isn't a simple and plain query.
But you are now consulting the High Cuisine Recipes.
So I suppose your intention was exactly the one to look for some really tasty and spicy SQL query:
and you've just got it. Feel happy.

After all the above SQL query is only apparently complex, but it real structure is surprisingly simple.
Let try rewriting the SQL query in a simplified form:

SELECT lc.lc_name AS LocalCouncil,
  c.county_name AS County,
  r.region_name AS Region,
  lc.population AS Population,
  ST_Area(lc.geometry) / 1000000.0 AS "Area sqKm",
  lc.population / (ST_Area(lc.geometry) / 1000000.0)
    AS "PopDensity [peoples/sqKm]",
  Length(lc.lc_name) AS NameLength,
  MbrMaxY(lc.geometry) AS North,
  MbrMinY(lc.geometry) AS South,
  MbrMinX(lc.geometry) AS West,
  MbrMaxX(lc.geometry) AS East
FROM local_councils AS lc
JOIN counties AS c ON (c.county_id = lc.county_id)
JOIN regions AS r ON (r.region_id = c.region_id)
WHERE lc.lc_id IN (... some list of values ...);

I suppose you can now easily understand what is the meaning of this SQL query.
There is absolutely nothing too much complex or difficult in this: But you already know all this, so I suppose you are very little interested to get any further detail.
Quite obviously the most interesting things happen within the WHERE ... IN (...) clause;
and exactly here we'll now focus our attention.

...
SELECT Max(population)
FROM local_councils
...
SELECT Min(population)
FROM local_councils
...
This SQL snippet is really simple: each query simply computes a Min / Max value.

...
  SELECT Max(population)
  FROM local_councils
UNION
  SELECT Min(population)
  FROM local_councils
...

This is the first time we introduce an UNION statement:
...
SELECT lc_id
FROM local_councils
WHERE population IN (
    SELECT Max(population)
    FROM local_councils
  UNION
    SELECT Min(population)
    FROM local_councils)
...

SQL supports a wonderful mechanism: the one known as sub-query.
You can define an inner query (which is executed first), then using any returned value into the outer (main) query.
Now the previous SQL snippet doesn't looks too much mysterious:
...
  SELECT lc_id
  FROM local_councils
  WHERE population IN (
      SELECT Max(population)
      FROM local_councils
    UNION
      SELECT Min(population)
      FROM local_councils)
UNION
  SELECT lc_id
  FROM local_councils
  WHERE ST_Area(geometry) IN
      SELECT Max(ST_area(geometry))
      FROM local_councils
    UNION
      SELECT Min(ST_Area(geometry))
      FROM local_councils)
...

Nothing forbid us to nest more than one single UNION clauses: this one is a fully legitimate option.
Accordingly to this, the above SQL snippet has to be interpreted as follows:

SELECT lc.lc_name AS LocalCouncil,
  c.county_name AS County,
  r.region_name AS Region,
  lc.population AS Population,
  ST_Area(lc.geometry) / 1000000.0 AS "Area sqKm",
  lc.population / (ST_Area(lc.geometry) / 1000000.0)
    AS "PopDensity [peoples/sqKm]",
  Length(lc.lc_name) AS NameLength,
  MbrMaxY(lc.geometry) AS North,
  MbrMinY(lc.geometry) AS South,
  MbrMinX(lc.geometry) AS West,
  MbrMaxX(lc.geometry) AS East
FROM local_councils AS lc
JOIN counties AS c ON (c.county_id = lc.county_id)
JOIN regions AS r ON (r.region_id = c.region_id)
WHERE lc.lc_id IN (
--
-- a list of lc.lc_id values will be returned
-- by this complex sub-query
--
    SELECT lc_id
    FROM local_councils
    WHERE population IN (
--
-- this further sub-query will return
-- Min/Max POPULATION
--
      SELECT Max(population)
      FROM local_councils
    UNION
      SELECT Min(population)
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE ST_Area(geometry) IN (
--
-- this further sub-query will return
-- Min/Max ST_AREA()
--
      SELECT Max(ST_area(geometry))
      FROM local_councils
    UNION
      SELECT Min(ST_Area(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE population / (ST_Area(geometry) / 1000000.0) IN (
--
-- this further sub-query will return
-- Min/Max POP-DENSITY
--
      SELECT Max(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils
    UNION
      SELECT MIN(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE Length(lc_name) IN (
--
-- this further sub-query will return
-- Min/Max NAME-LENGTH
--
      SELECT Max(Length(lc_name))
      FROM local_councils
    UNION
      SELECT Min(Length(lc_name))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxY(geometry) IN (
--
-- this further sub-query will return
-- Max NORTH
--
      SELECT Max(MbrMaxY(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinY(geometry) IN (
--
-- this further sub-query will return
-- Max SOUTH
--
      SELECT Min(MbrMinY(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxX(geometry) IN (
--
-- this further sub-query will return
-- Max WEST
--
      SELECT Max(MbrMaxX(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinX(geometry) IN (
--
-- this further sub-query will return
-- Max EAST
--
      SELECT Min(MbrMinX(geometry))
      FROM local_councils));
According to SQL syntax, using two consecutive hyphens (--) you can mark a comment.
i.e. any further text until the next line terminator is absolutely ignored by the SQL parser.
Placing appropriate comments within really complex SQL queries surely enhances readibility.


Conclusion: SQL is a wonderful language, fully supporting a regular and easily predictable syntax.
Each time you'll encounter some intimidating complex SQL query don't panic and don't be afraid:
simply attempt to break the complex statement into several smallest and simplest blocks, and you'll soon discover that complexity was more apparent than real.

Useful hint: attempting to debug some very complex SQL statement is obviously a difficult and defatigating task.
Breaking down a complex query into smallest chunks, then testing each one of them individually usually is the best approach you can follow.

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.