Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Tue 24 January 2017 13:22

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Postgis : calculs de mitoyenneté des bâtiments

Bonjour à tous,

Je suis désolée si j'aborde un sujet qui a déjà été traité mais très sincèrement je ne trouve rien sur le net...

Je travail avec le bâti de la BDTOPO (BATI_INDIFFERENCIE) et je cherche la (ou les) fonctions qui me permettraient de connaitre quels sont les bâtiments mitoyens, la longueur de cette mitoyenneté et au final la hauteur + surface verticale de mitoyenneté (je peux calculer sans trop de souci ces deux derniers paramètres si j'arrive à déterminer les deux premiers).

Pour mieux comprendre j'ai fait un schéma que je joins à ce message.

J'aimerais pouvoir dans un premier temps déterminer quels sont tous les bâtiments dans le cas de A et B et ensuite quelle est la longueur x. Le bâtiment C doit quant à lui être considéré comme non mitoyen (bâti isolé).

Une idée??? XD

Merci beaucoup pour votre aide toujours aussi précieuse!

Lucie D.

Dernière modification par Lucie D. (Tue 24 January 2017 13:32)


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#2 Tue 24 January 2017 14:35

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: Postgis : calculs de mitoyenneté des bâtiments

Salut

un genre de chose comme çà ?

SELECT
row_number() over () id,
ids,
st_intersection(g[1], g[2]) inter
FROM
(
    select DISTINCT ON (ids)
    (SELECT array_agg(x) FROM (SELECT unnest(ARRAY[b1.idbatiment, b2.idbatiment]) x order by x) i) ids,
        -- la ligne précedente peut etre remplacée par :
        -- sort(ARRAY[b1.idbatiment, b2.idbatiment]) ids,
        -- si l'extension intarray est activée dans la base de données
        -- (cela sera + rapide)
    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
) t
WHERE st_relate(g[1], g[2], '****1****')


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)

Hors ligne

 

#3 Wed 25 January 2017 11:06

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgis : calculs de mitoyenneté des bâtiments

Bonjour,

Le résultat est vraiment très satisfaisant effectivement, merci beaucoup pour cette requête...que j'ai malheureusement copiée bêtement car  je ne la comprends pas vraiment... Si je déroule la requête en "français parlé" : P

- pour le ids, il s'agit en fait d'une liste des identifiants des bâtiments qui se touchent?
Mais pourquoi est-ce que pour deux bâtiments mitoyens id1 et id2, mon id1 revient plein de fois et idem pour mon id2... dans ma liste finale ids? Pourquoi créer une liste de mes identifiants, puis les éclater avec le unnest, puis les classer dans l'ordre croissant, puis les ré-agréger dans une liste avec le array_agg??? (que fait cette fonction d'ailleurs, impossible de comprendre ce qui est expliqué sur le web...quelle est la différence avec la fonction array[...]???)

- pourquoi mettre les géométries dans une liste?

- que signifie la clause b1.the_geom && b2.the_geom???

- que signifie l'expression  '****1****' ???

Ouhlà je suis franchement désolée mais j'aimerais bien comprendre malgré mon niveau franchement "médiocre" sur Postgresql... ='(

Merci infiniment pour votre aide!

Bonne journée!

Bien cordialement,

Lucie D.

Hors ligne

 

#4 Wed 25 January 2017 15:12

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: Postgis : calculs de mitoyenneté des bâtiments

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.

Code:

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.

Code:

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

Code:

(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 :

pourquoi est-ce que pour deux bâtiments mitoyens id1 et id2, mon id1 revient plein de fois et idem pour mon id2... dans ma liste finale ids?

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

Hors ligne

 

#5 Wed 25 January 2017 15:51

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: Postgis : calculs de mitoyenneté des bâtiments

La requête remaniée suite à vos remarques :

Code:

SELECT
row_number() OVER () id,
ids,
st_intersection(g1, g2) inter
FROM
(
    SELECT DISTINCT ON (ids)
    (SELECT array_agg(idbatiment) 
    FROM  ( (SELECT b1.idbatiment UNION SELECT b2.idbatiment) 
             ORDER BY idbatiment ) i
    ) ids,
-- seconde manière de faire ( nécessite l'extension intarray ) :
-- sort(ARRAY[b1.idbatiment, b2.idbatiment]) ids, 
-- une troisième manière de faire (apparemment la plus rapide) :
-- CASE 
-- WHEN b1.idbatiment < b2.idbatiment 
-- THEN ARRAY[b1.idbatiment, b2.idbatiment] 
-- ELSE ARRAY[b2.idbatiment, b1.idbatiment] END ids,
    b1.the_geom g1,
    b2.the_geom g2
    FROM
    batiment b1
    JOIN
    batiment b2
    ON b1.idbatiment <> b2.idbatiment AND b1.the_geom && b2.the_geom
) t
WHERE st_relate(g1, g2, '****1****')

Dernière modification par tumasgiu (Wed 25 January 2017 17:06)

Hors ligne

 

#6 Wed 25 January 2017 16:07

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgis : calculs de mitoyenneté des bâtiments

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 :

Code:

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

Code:

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 :

Code:

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

hmm

Merciiii!!!

Bonne fin de journée.

Bien cordialement,

Lucie D.

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

Hors ligne

 

#7 Thu 26 January 2017 10:33

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: Postgis : calculs de mitoyenneté des bâtiments

Vous vous compliquez les choses.

Vous voulez récupérez la hauteur minimum entre deux bâtiments,
faites le au moment ou vous les comparez deux à deux :

Code:

SELECT
row_number() OVER () id,
ids,
st_length(st_intersection(g1, g2)) * hauteur_min surface
FROM
(
    SELECT DISTINCT ON (ids)
--    premiere maniere de classer les ids
--   (SELECT array_agg(idbatiment) 
--   FROM  ( (SELECT b1.idbatiment UNION SELECT b2.idbatiment) 
--            ORDER BY idbatiment ) i
--   ) ids,
--  seconde manière de faire ( nécessite l'extension intarray ) :
--  sort(ARRAY[b1.idbatiment, b2.idbatiment]) ids, 
--  une troisième manière de faire (apparemment la plus rapide) :
    CASE
        WHEN b1.gid < b2.gid
        THEN ARRAY[b1.gid, b2.gid] 
        ELSE ARRAY[b2.gid, b1.gid] 
    END ids,
    CASE 
        WHEN b1.hauteur < b2.hauteur
        THEN b1.hauteur
        ELSE b2.hauteur
    END hauteur_min,
    b1.geom g1,
    b2.geom g2
    FROM
    bati_indifferencie b1
    JOIN
    bati_indifferencie b2
    ON b1.gid <> b2.gid AND b1.geom && b2.geom
) t
WHERE st_relate(g1, g2, '****1****')
ORDER BY ids

Hors ligne

 

#8 Thu 26 January 2017 10:54

Lucie D.
Participant actif
Date d'inscription: 21 Oct 2013
Messages: 137

Re: Postgis : calculs de mitoyenneté des bâtiments

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.

Hors ligne

 

Pied de page des forums

Powered by FluxBB