Recette #12
Voisins

Février 2011


Précédent

Table des matières

Suivant


Le problème

  • A première vue, chaque Local Council partage une limite en commun avec un voisin:
    [ce n'est pas une règle absolue: e.g. petites îles].

  • On va utiliser SQL afin d'identifier les couples de Local Councils adjacents.

  • Afin d'ajouter un peu de complexité, nous allons nous intéresser uniquement aux Local Councils voisins la TOSCANE (Région).


Tuscan Local Council

Tuscan County

Neighbour LC

County

Région

ANGHIARI

AREZZO

CITERNA

PERUGIA

UMBRIA

AREZZO

AREZZO

MONTE SANTA MARIA TIBERINA

PERUGIA

UMBRIA

BIBBIENA

AREZZO

BAGNO DI ROMAGNA

FORLI' - CESENA

EMILIA-ROMAGNA

CHIUSI DELLA VERNA

AREZZO

BAGNO DI ROMAGNA

FORLI' - CESENA

EMILIA-ROMAGNA

CHIUSI DELLA VERNA

AREZZO

VERGHERETO

FORLI' - CESENA

EMILIA-ROMAGNA

...

...

...

...

...



SELECT lc1.lc_name AS "Local Council",
  lc2.lc_name AS "Neighbour"
FROM local_councils AS lc1,
  local_councils AS lc2
WHERE ST_Touches(lc1.geometry, lc2.geometry);

Cette première requête est simple:

Cependant, une approche aussi simpliste implique plusieurs problèmes:



SELECT lc1.lc_name AS "Local Council",
  lc2.lc_name AS "Neighour"
FROM local_councils AS lc1,
  local_councils AS lc2
WHERE lc2.ROWID IN (
  SELECT pkid
    FROM idx_local_councils_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(lc1.geometry),
      MbrMinY(lc1.geometry),
      MbrMaxX(lc1.geometry),
      MbrMaxY(lc1.geometry)));

Heureusement, on peut réaliser ces requêtes spatiales d'une manière plus rapide et efficace:

Voici ce qui se passe étape par étape:



SELECT lc1.lc_name AS "Tuscan Local Council",
  c1.county_name AS "Tuscan County",
  lc2.lc_name AS "Neighbour LC",
  c2.county_name AS County,
  r2.region_name AS Région
FROM local_councils AS lc1,
  local_councils AS lc2,
  counties AS c1,
  counties AS c2,
  regions AS r1,
  regions AS r2
WHERE c1.county_id = lc1.county_id
  AND c2.county_id = lc2.county_id
  AND r1.region_id = c1.region_id
  AND r2.region_id = c2.region_id
  AND r1.region_name LIKE 'toscana'
  AND r1.region_id <> r2.region_id
  AND ST_Touches(lc1.geometry, lc2.geometry)
  AND lc2.ROWID IN (
    SELECT pkid
      FROM idx_local_councils_geometry
      WHERE pkid MATCH RTreeIntersects(
        MbrMinX(lc1.geometry),
        MbrMinY(lc1.geometry),
        MbrMaxX(lc1.geometry),
        MbrMaxY(lc1.geometry)))
ORDER BY c1.county_name, lc1.lc_name;

Bien, maintenant que nous avons résolu le sujet de l'Index Spatial, la réécriture de la requête ne pose plus de problèmes
Etant donné qu'il s'agit d'une requète complexe, des informations supplémentaires ne vous feront pas de mal:


SELECT lc1.lc_name AS "Tuscan Local Council",
  c1.county_name AS "Tuscan County",
  lc2.lc_name AS "Neighbour LC",
  c2.county_name AS County,
  r2.region_name AS Région
FROM local_councils AS lc1,
  local_councils AS lc2
JOIN counties AS c1
  ON (c1.county_id = lc1.county_id)
JOIN counties AS c2
  ON (c2.county_id = lc2.county_id)
JOIN regions AS r1
  ON (r1.region_id = c1.region_id)
JOIN regions AS r2
  ON (r2.region_id = c2.region_id)
WHERE r1.region_name LIKE 'toscana'
  AND r1.region_id <> r2.region_id
  AND ST_Touches(lc1.geometry, lc2.geometry)
  AND lc2.ROWID IN (
    SELECT pkid
      FROM idx_local_councils_geometry
      WHERE pkid MATCH RTreeIntersects(
        MbrMinX(lc1.geometry),
        MbrMinY(lc1.geometry),
        MbrMaxX(lc1.geometry),
        MbrMaxY(lc1.geometry)))
ORDER BY c1.county_name, lc1.lc_name;

Même requête, mais avec une syntaxe de JOINture différente.

Réaliser des analyses spatiales n'est pas toujours une tâche facile.
Maîtriser des requêtes SQL complexes n'est pas évident (mais pas impossible).
Optimiser de telles requêtes, afin d'obtenir des réponses rapides requiert un soin et une attention particulière.
Mais le résultat en vaut la chandelle.


Précédent

Table des matières

Suivant


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.