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 #14 Populated Places vs Local Councils
2011 January 28

Previous Slide

Table of Contents

Next Slide

The problem

Do you remember ?

We've left the populated_places table in a self-standing position since now.
While designing the DB layout we concluded that some spatial relationship must exists between populated_places and local_councils.
We can easily expect to get some inconsistencies between these two datasets, because they come from absolutely unrelated sources.
The populated_places table has POINT Geometries into the 4236 SRID (Geographic, WGS84, long-lat):
whilst the local_councils table has MULTIPOLYGON Geometries into the 23032 SRID (planar, ED50 UTM zone 32)
Using two different SRIDs surely introduces some further complication to be resolved.

It's now time to confront yourself with this not-so-simple problem.

PopulatedPlaceId	PopulatedPlaceName	LocalCouncilId	LocalCouncilName	County	Region
...	...	...	...	...	...
12383	Acitrezza	NULL	NULL	NULL	NULL
12384	Lavinio	NULL	NULL	NULL	NULL
11327	Altino	69001	ALTINO	CHIETI	ABRUZZO
11265	Archi	69002	ARCHI	CHIETI	ABRUZZO
11247	Ari	69003	ARI	CHIETI	ABRUZZO
...	...	...	...	...	...

You can start with this first simple query:

the ST_Contains(geom1, geom2) function is used to evaluate the spatial relationship existing between Local Councils and Populated Places.
there is absolutely nothing strange in this query: you'll simply use a JOIN condition based on a spatial relationship.
quite obviously using Transform() is required so to re-project any coordinate into the same SRID.
anyway you are already warned: you are now well conscious that using a so simplistic approach (i.e. not using the R*Tree Spatial Index) will surely produce a very slowly running query.

SELECT pp.id AS PopulatedPlaceId,
  pp.name AS PopulatedPlaceName,
  lc.lc_id AS LocalCouncilId,
  lc.lc_name AS LocalCouncilName
FROM populated_places AS pp,
  local_councils AS lc
WHERE ST_Contains(lc.geometry,
    Transform(pp.geometry, 23032))
  AND lc.lc_id IN (
    SELECT pkid
    FROM idx_local_councils_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(
        Transform(pp.geometry, 23032)),
      MbrMinY(
        Transform(pp.geometry, 23032)),
      MbrMaxX(
        Transform(pp.geometry, 23032)),
      MbrMaxY(
        Transform(pp.geometry, 23032)));

This further query is exactly the same as the first one: except in that this second version fully exploits the R*Tree Spatial Index.
Please note: using Transform() several times is absolutely required, so to correctly re-project any coordinate into an uniform SRID.

Anyway there is still an unresolved issue in the above query: following this way any mismatching Populated Place will never be identified.
In order to detect if some Populated Place does actually falls outside any corresponding Local Council you absolutely have to implement a LEFT JOIN.

SELECT pp.id AS PopulatedPlaceId,
  pp.name AS PopulatedPlaceName,
  lc.lc_id AS LocalCouncilId,
  lc.lc_name AS LocalCouncilName,
  c.county_name AS County,
  r.region_name AS Region
FROM populated_places AS pp
LEFT JOIN local_councils AS lc
  ON (ST_Contains(lc.geometry,
      Transform(pp.geometry, 23032))
    AND lc.lc_id IN (
      SELECT pkid
      FROM idx_local_councils_geometry
      WHERE pkid MATCH RTreeIntersects(
        MbrMinX(
          Transform(pp.geometry, 23032)),
        MbrMinY(
          Transform(pp.geometry, 23032)),
        MbrMaxX(
          Transform(pp.geometry, 23032)),
        MbrMaxY(
          Transform(pp.geometry, 23032)))))
LEFT JOIN counties AS c
  ON (c.county_id = lc.county_id)
LEFT JOIN regions AS r
  ON (r.region_id = c.region_id)
ORDER BY 6, 5, 4;

All right: this one is the final and definitive version.

you've simply added further LEFT JOIN clauses, so to fully qualify each Local Council reporting the corresponding County and Region.
not at all surprisingly about twenty Populated Places doesn't actually correspond to any Local Council
(you expected this, because these two datasets come from unrelated sources).
you can duly use QGIS to visually inspect such mismatching entries: and you'll soon discover that in each case all them are towns placed very closely to sea shore.
And some (slight) misplacement actually exist.

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.