Le portail francophone de la géomatique

Salut

un genre de chose comme çà ?

La sous requête t renvoie tout les couples de bâtiments qui ont une intersection commune possible
en éliminant les doublons ( exemple, si le bâtiment 1 et 2 ont une intersection possible, le couple (1,2)
sera conservé et le couple (2,1) sera éliminé ).

St_relate permet de filtrer les géométries des couples ayant une limite commune.

Je ne suis pas certain que le fait de pré-filtrer les doublons ait un impact significatif sur la performance
de la requête.

En revanche, ne connaissant pas la qualité topologique de la bdtopo, peut être serait il préférable
d'aligner vos géométries sur une grille avant de les filtrer.

PS:
la doc de st_relate est ici: http://postgis.net/docs/ST_Relate.html

Dernière modification par tumasgiu (Tue 24 January 2017 15:56)

Si vous avez réussi à adapter la requête à votre base,
c'est que vous l'avez certainement compris.

La requête fait appel à des concepts SQL, PostgreSQL et PostGIS.

On retraduit votre besoin : Récupérer les parties mitoyennes des bâtiments
afin de pouvoir effectuer des calculs avec.

Comparer tous les bâtiments 2 à 2 pour savoir si ils possèdent une partie mitoyenne
serait le plus simple mais aussi le plus couteux puisqu'il vous faudra effectuer n*n = n²
comparaisons.
On va tricher un peu et essayer de ne comparer que ce qui nous intéresse, c'est à çà
que sert la sous requête t.

select DISTINCT ON (ids)
(SELECT array_agg(x) FROM (SELECT unnest(ARRAY[b1.idbatiment, b2.idbatiment]) x order by x) i) ids,
ARRAY[b1.the_geom, b2.the_geom] g
FROM
batiment b1
JOIN
batiment b2
ON b1.idbatiment <> b2.idbatiment 
and b1.the_geom && b2.the_geom

Bon, j'ai un peu menti quand j'ai dit qu'on allait comparer que ce qui nous intéressait, on a quand même
besoin de lister toutes les combinaisons possibles. Seulement on va éliminer à ce stade plusieurs types
de couple, pour que les opérations spatiales plus couteuses ne soient effectuées que quand
c'est nécessaire.

Pour çà, on calcule le carré cartésien de la table :
c'est la jointure des lignes 5 à 9.

Première élimination, avec le test sur les identifiants, ligne 8 : il ne faut pas comparer un bâtiment avec lui même !

Ensuite le test ligne 9 utilisant l'opérateur &&, qui est un opérateur POSTGIS qui renvoie vrai
si les boites englobantes des opérandes s'intersectent. C'est un calcul rapide ( calcul de l'intersection
de deux rectangles ), et qui de plus utilise l'index spatial de la table si il est disponible.
On peut raisonnablement penser que si les boites de deux bâtiments ne partagent aucun espace,
ceux-ci n'en partageront pas non plus.

On a éliminé les couples dont on est sûr qu'ils n'auront aucune mitoyenneté, mais il reste un autre
type de candidat à éliminer : les doublons.
Dans le résultat de la jointure, pour deux bâtiments x et y distincts, on aura les couples (x,y) et (y,x).
On aura aussi leur géométries respectives, mais comme la relation
d’intersection entre deux géométries est symétrique (i.e. intersection(a, b) = intersection(b, a) ),
nous n'avons pas besoin de calculer deux fois l'intersection pour chaque couple car
cela doublerait le temps de calcul et vous auriez tout vos résultats en double.
Pour éviter çà, on peut utiliser l'astuce suivante :
En SQL, on peut utiliser le mot-clef DISTINCT dans la clause SELECT, ce qui a pour effet d'éliminer
les lignes doublons ( i.e. qui ont les mêmes valeurs sur toutes les colonnes ).
PostgreSQL propose aussi DISTINCT ON (colonne1, colonne2...), qui permet d'éliminer les lignes qui ont uniquement
les valeurs des colonnes spécifiées entre parenthèses en commun ( je ne suis pas sur que ce soit un standard SQL ).
L'idée, c'est de produire une valeur qui sera commune au couple (x,y) et (y,x).
La solution proposée, c'est d'utiliser comme valeur distincte le couple, en en faisant une liste ( tableau ).
Mais pour que deux listes soient considérées égales, il faut qu'elle possèdent les mêmes
valeurs dans le même ordre. C'est là qu'intervient l'expression de la ligne 2, qui va trier les 2 identifiants du
couple dans l'ordre croissant.

SELECT unnest(ARRAY[b1.idbatiment, b2.idbatiment]) x order by x

Votre question à propos de la ligne 2 était pertinente, ce qui prouve que vous avez compris ce qu'il se
passait. L'expression ci-dessus pourrait en fait être simplifiée par

(SELECT b1.idbatiment AS x UNION SELECT b2.idbatiment) ORDER BY x

qui est plus lisible, plus compréhensible, et peut être aussi plus performante.
Une fois qu'on a trié les 2 identifiants, il faut en faire une liste. C'est à çà que sert array_agg : c'est une fonction
d'agrégat PostgreSQL qui va créer une liste a partir des valeurs d'une colonne. Une fonction d’agrégation est une fonction qui renvoie un
résultat unique à partir d'un ensemble de valeurs. Une autre fonction d’agrégation est SUM, que vous devez certainement connaitre.
Vous avez également raison de vous poser la question de la nécessité de créer une liste pour les deux géométries ( ligne 3 ),
en fait ce n'est pas nécessaire, j'ai écrit un peu trop vite, pardon.


Une fois qu'on a récupéré cette liste de couple de bâtiments qui sont potentiellement mitoyens, on peut ne retenir
que ceux qui le sont effectivement.
Pour cela on utilise st_relate ( fonction POSTGIS ), qui calcule la matrice d'intersection DE-9IM
( http://postgis.net/docs/using_postgis_d … tml#DE-9IM )
de deux géométries et la compare avec un patron de matrice, ici en l’occurrence ****1****, qui se traduit par :
il faut que les limites des deux géométries s'intersectent en une (des) ligne(s).

En espérant ne pas avoir été trop confus.

Vous pouvez vous amuser à essayer de trouver un des défauts de cette requête, ou répondre à votre question :

Dernière modification par tumasgiu (Wed 25 January 2017 16:05)

Hum

Je pense que je vais devoir relire ces explications plusieurs fois avant de les intégrer et du coup je n'ai pas encore la réponse à la dernière question... :p

En revanche, grâce à ma petite requête suivante :

WITH tempa AS
(SELECT bati.id,sum(m.length) as length
FROM mitoyen as m, bati
WHERE bati.id=ANY(m.ids)
GROUP BY bati.id)
UPDATE bati
SET long_mitoy=round(tempa.length,2)
FROM tempa
WHERE bati.id=tempa.id;

Je trouve la longueur mitoyenne pour chaque bâtiment.

Mon objectif maintenant est de calculer la surface verticale mitoyenne de chaque bâtiment (ou la surface des murs mitoyens pour être plus claire).

Le problème, c'est que parfois, deux bâtiments peuvent être mitoyens sans pour autant avoir la même hauteur.

Ainsi, ma surface mitoyenne 'surf_mitoy' doit être égale à :


long_mitoy*(min(hauteur_bati1,hauteur_bati2))

Je dois donc ramener pour chaque bâtiment quelque chose dans ce genre  (src signifie source et nbr neighbour):

id_src | id_nbr | hauteur_src | hauteur_nbr

Je pensais créer une table intermédiaire avec cette structure sachant qu'il peut y avoir des doublons pour les bâtiments mitoyens sur plusieurs façades...

Le problème, c'est qu'avec le résultat de la requête que vous me proposez, je bloque un peu, pourtant, je suis sûre que c'est faisable...
J'ai bien essayé quelque chose de ce type dans un premier temps :

DROP TABLE IF EXISTS bati_intermediaire;
CREATE TABLE bati_intermediaire
AS
SELECT a.id as id_src,unnest(b.ids) as id_nbr
FROM bati as a
JOIN mitoyen as b
ON a.id=ANY(b.ids);

Où mitoyen est la table temporaire correspondant à votre requête.
Mais ça marche pas, bien évidemment, en relisant cette requête je me rends compte à quel point c'est ridicule mais...je bloque encore!!!



Merciiii!!!

Bonne fin de journée.

Bien cordialement,

Lucie D.

Dernière modification par Lucie D. (Wed 25 January 2017 16:07)

Bonjour,

Merci infiniment, je me disais bien que je pouvais tout intégrer dans une même requête. Et il faut croire que mon cerveau se plait à compliquer les choses...Bah ce serait moins marrant sinon! ^^"

Pour moi je comprends bien mieux votre nouvelle requête qui en plus me permet de ne ramener qu'une seule fois id1 et id2 dans le champ ids (et je peux ainsi faire d'autres traitements avec cette nouvelle table qui me servira plus tard dans mon étude).

La troisième méthode est pour moi la meilleure (case when) car la première marche sur une petit jeu de données mais pas sur un département tout entier (manque de place sur mon serveur apparemment mais ça ce n'est pas nouveau, ça encourage à optimiser les requête!!!). La deuxième solution ne fonctionne pas (intarray non installé?).

Je considère donc cette discussion comme résolue!

À nouveau un TRÈS GRAND MERCI!

Bonne continuation.

Lucie D.