Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	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:

Roma has the biggest population: 2,546,804 peoples
Morterone has the smallest population: 33 peoples
Roma (again) has the biggest area: 1,287 km²
Atrani has the smallest area: 0.1 km²
Casavatore has the highest population density: 13,627.5 peoples/km²
Briga Alta has the lowest population density: 1.2 peoples/km²
Pino sulla sponda del Lago Maggior(e) and San Valentino in Abruzzo Citeriore share the privilege to have the longest name.
on the other side Lu, Ne, Re, Ro and Vo have the shortest name.
Predoi is the northernmost
Lampedua e Linosa is the southernmost
Bardonecchia is the westernmost
Otranto is the easternmost

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:

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:

the local_councils table is JOINed to the counties table.
and the counties table is then JOINed to the regions table.
the ST_Area() Spatial function is used to calculate areas: an appropriate scale factor is applied so to get measures expressed in km² rather than in m².
a numeric expression (not at all sophisticated or complex) is used to calculate population density expressed in peoples/km²
a WHERE ... IN (...) clause is then used so to filter someway the result-set.

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:

this allows merging together two result-sets, so to get an unique result-set.
there is a condition to be satisfied: both the left-sided and the right-sided SELECTs must return the same number of columns, and corresponding columns must have the same value-type.

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:

in this case the sub-query actually is an UNION query.
the left-sided query will return the Max(population) value.
the right-sided query will return the Min(population) value.
then UNION will merge both values into an unique result-set:
and this one is the result-set returned by the (inner) sub-query.
so the WHERE population IN (...) clause in the (outer) main query will simply receive two population-values to be checked.
after all this, the (outer) main query will return a result-set which actually simply is a list of lc_id

...
  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:

we've already explained the leftmost query in the previous paragraph:
this will return a result-set containing a list of lc_id-values (Max/Min population-values).
the rightmost query performs a similar task:
this will return another result-set containing a further list of lc_id-values (Max/Min ST_Area(geometry)-values).
so the second-level UNION clause will simply return a longer list of lc_id-values, i.e. the one resulting by merging all together any previous intermediate result.
and so on ...

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

	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.