Objectif: determiner le pourcentage de recouvrement entre deux couches SIG
Données:- table1 (geometry, id)
- table2 (geometry,id) + Index Spatial RTree
Méthode: on va calculer pour chaque objet de la table 1, l'aire (ou le pourcentage) d'intersection avec les objets de la table 2
Solution:SELECT
table1.id as id1,
table2.id as id2
st_area( st_intersection(table1.geometry,table2.geometry) ) as "aire de recouvrement",
st_area( st_intersection(table1.geometry,table2.geometry) )/ st_area ( table1.geometry)
as "pourcentage de recouvrement"
FROM table1,table2
WHERE
st_intersects(table1.geometry,table2.geometry)
AND
table2.rowid IN (
SELECT pkid FROM idx_table2_geometry
WHERE pkid MATCH RTreeintersects(
MBRminX(table1.geometry),
MBRminY(table1.geometry),
MBRmaxX(table1.geometry),
MBRmaxY(table1.geometry),
)))
Quelques explications:Clause SELECT ... FROM: on selectionne les colonnes voulues dans les tables.
- st_area(st_intersection()) calcule l'aire de l'intersection
Clause WHERE:
- jointure spatiale: la fonction st_intersects() permet de selectionner les objets des deux tables se chevauchants.
- RTree: Pour chaque objet de la table1, on préfiltre les objets de la table2 intersectant le RTree. Ceci permet d'accelerer grandement la requete
Facile non ?
En revanche, il est plus difficile de selectionner directement par SQL, pour chaque objet de la table 1, l'objet de la table 2 correspondant au recouvrement maximal (utile pour passer les valeurs d'une table dans une autre en fonction du taux de recouvrement). En effet, la clause
GROUP BY id1 couplée à
MAX(%intersecteion) va ici nous retourner des lignes composées de la façon suivante:
1) Une ligne par objet de la table 1
2) Pourcentage max de recouvrement entre cet objet et les objets de la table 2
3) un objet de la table2 "au hasard" (ne correspondant pas au taux de recouvrement maximal)
Objectif 2: Determiner pour chaque objet de la table 1, l'objet de la table 2 avec lequel le pourcentage de recouvrement est maximal
Solution:Etape1:Creer une vue à partir de la requete précédente ( vue = intermédiaire entre table et requete):
CREATE VIEW vue1 AS SELECT ....requete précédente....La vue1 contient alors la liste des pourcentages de recouvrement pour chaque couple d'objets (table1 et table2)
Etape2:Creer une nouvelle vue contenant, pour chaque objet de la table1, la valeur max du pourcentage de recouvrement:
CREATE VIEW vue2 ASSELECTvue1.id1 as id1,MAX( vue1."pourcentage de recouvrement" ) as "pourcentage de recouvrement max"FROM vue1Etape3:Enfin, en utilisant les deux vues précédentes, il devient possible d'extraire de la vue1, les lignes correspondants au % max de recouvrement pour chacun des objets de la table1:
SELECT
*
FROM vue1
JOIN vue2 ON (
vue2.id1=vue1.id1
AND
vue2.
"pourcentage de recouvrement max"=vue1."pourcentage de recouvrement")
Et voila, le travail est terminé ! A priori cela peut paraître compliqué, mais en décortiquant un peu les requetes, on s'apperçoit que c'est à la portée de tout le monde.
De plus, répondre à ce genre de question via des logiciels SIG classiques (MAPINFO, QGIS, ...) s'avère encore plus complexe à mettre en oeuvre ...
