SpatiaLite Cookbook

	Recette #14 Populated Places vs Local Councils
Février 2011

Table des matières

Le problème

Vous vous souvenez ?

On a laissé la table populated_places à part jusqu'à maintenant.
Lors de la création de la BDD, on avait conclu qu'il devait exister une relation spatiale entre populated_places et local_councils.
On peut facilement imaginer qu'il y ait quelques incohérences entre ces deux jeux de données provenant de sources totalement indépendantes.
La table populated_places possède une géométrie de type POINT et le SRID 4236 (Geographic, WGS84, long-lat):
en revanche, la table local_councils possède une géométrie de type MULTIPOLYGON et le SRID 23032 (planar, ED50 UTM zone 32)
Utiliser deux SRIDs introduit nécessairement un problème qu'il faudra résoudre.

Il est maintenant temps de résoudre ce "petit" problème.

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

On va commencer par une requête simple

la fonction ST_Contains(geom1, geom2) (Contains=Contient) évalue la relation spatiale existant entre les tables Local Councils et Populated Places.
il n'y a rien d'étrange dans cette requête: on utilise simplement une jointure spatiale.
L'utilisation de la fonction Transform() permet de reprojeter la géométrie afin de travailler qu'avec un seul SRID.
Mais comme vous le savez, utiliser une approche aussi simple (ie sans R*Tress) produira nécessairement une requète peu efficace et un temps de traitement important.

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)));

Cette nouvelle requête est la même que la précédente, mais utilise l'Index Spatial R*Tree.
note: l'utilisation de la fonction Transform() à chaque étape est absolument nécessaire, afin de ne travailler que sous un seul SRID

Cependant, un problème persiste: la requête ci dessus ne permet pas d'identifier les Populated Place non inclus dans les Local Councils.
Pour ce faire, il est nécessaire de mettre en place un 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;

Voici la version finale.

on a simplement ajouté une clause LEFT JOIN , afin de caractérisé chaque Local Council en reportant les County et Region approprié.
IL n'est pas si surprenant que 20 Populated Places ne correspondent à aucun Local Council
(on s'y attendait, car les deux jeux de données proviennent de sources différentes).
Il est possible d'utiliser QGIS pour inspecter ces données: vous vous rendrez vite compte qu'il s'agit de points situés près de la mer. Ce qui explique les quelques petites incohérences de positionnement des points

Table des matières

	Author: Alessandro Furieri a.furieri@lqt.it Traduced From English by RIVIERE Romain
	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.