SpatiaLite logo

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


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));
You can start with this first simple 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.
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.