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 Thu 13 September 2018 12:34

Hippo
Participant actif
Date d'inscription: 18 Jan 2015
Messages: 57

Postgis - St_Intersects sur des géométries d'une même colonne

Bonjour,

J'ai une table contenant des dossiers d'urbanisme de différents types CU, PC, ... et j'aurais besoin de faire une requête me retournant les géométries
qui s'intersectent, par exemple tel PC recoupe la géométrie de tel CU pour pouvoir ensuite lister les différents documents se trouvant sur un même périmètre.

Ma table est conçue grosso modo comme suit, où par exemple ici la 1ère ligne à la même géométrie que la 3è et où ma requête devrait donc me retourner les labels de la 1ère et 3ème ligne :


____"label" ___  |_______"geom"___________
  CU156156151   |     015151515151515151212
  PC151611156   |     051561611515161515156
  PC115151121   |     015151515151515151212


Donc, comment peut-on faire un St_intersects de plusieurs géométries contenues dans la même table et même colonne "geom" ?

J'ai tenté un "SELECT label from table WHERE ST_Intersects(geom,geom)" mais ça ne marche pas ...

Hors ligne

 

#2 Thu 13 September 2018 14:22

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

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Salut,

il faut que vous fassiez un produit cartésien de votre table sur elle même.

Code:

SELECT * FROM
(  SELECT a.label AS label_a, b.label AS label_b, 
    st_intersection(a.geom, b.geom) AS geom
   FROM matable AS a CROSS JOIN matable AS b
   WHERE a.geom && b.geom
) AS foo
WHERE not st_isempty(geom);

Dernière modification par tumasgiu (Thu 13 September 2018 14:24)

Hors ligne

 

#3 Thu 13 September 2018 14:25

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Bonjour,
Première chose ajouter une colonne numérique (serial) qui servira de clef primaire, nommons la idtable.

Ensuite on utilise une jointure réflexive.

Code:

SELECT a.label,b.label,st_intersection(a.geom,b.geom) FROM monschema.matable as a, monschema.matable as b WHERE st_intersects(a.geom,b.geom) AND a.idtable<>b.idtable

Dernière modification par ChristopheV (Thu 13 September 2018 14:25)


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#4 Thu 13 September 2018 14:45

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

Re: Postgis - St_Intersects sur des géométries d'une même colonne

ChristopheV a écrit:

Bonjour,
Première chose ajouter une colonne numérique (serial) qui servira de clef primaire, nommons la idtable.

Ensuite on utilise une jointure réflexive.

Code:

SELECT a.label,b.label,st_intersection(a.geom,b.geom) FROM monschema.matable as a, monschema.matable as b WHERE st_intersects(a.geom,b.geom) AND a.idtable<>b.idtable


Oui, il faut ajouter une condition à la clause WHERE pour ignorer les intersections d'une ligne sur elle même.

Hors ligne

 

#5 Thu 13 September 2018 14:46

Hippo
Participant actif
Date d'inscription: 18 Jan 2015
Messages: 57

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Super !! Merci pour vos réponses à tous les 2.
Ma réflexion s'orientait effectivement sur le fait de devoir créer 2 références dans cette même table pour pouvoir les intersecter, mais je n'avais pas réussi à finaliser la chose.

Hors ligne

 

#6 Thu 13 September 2018 14:47

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Ouaf !!

A deux mètres de distance et deux secondes d'intervalle, deux réponses différentes qui fonctionnent, la magie du net !


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#7 Thu 13 September 2018 14:49

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Bonjour,

Sur ce sujet, une fonction récente et bien pratique de postGIS: st_clusterIntersecting: fn agrégée agissant sur une colonne geo, et retournant un tableau des geométries en relation spatiale entre elles.
S'il faut en faire l'union, st_unaruUnion marche super bien.
S'il faut juste identifier les groupes d'objets liés spatialement, on peut ensuite faire une requete spatiale entre la couche initiale et le résultat, avec par exemple st_pointOnSurface, pour réassocier les identifiants des objets.

Nicolas

Hors ligne

 

#8 Thu 13 September 2018 14:50

Hippo
Participant actif
Date d'inscription: 18 Jan 2015
Messages: 57

Re: Postgis - St_Intersects sur des géométries d'une même colonne

tumasgiu a écrit:
ChristopheV a écrit:

Bonjour,
Première chose ajouter une colonne numérique (serial) qui servira de clef primaire, nommons la idtable.

Ensuite on utilise une jointure réflexive.

Code:

SELECT a.label,b.label,st_intersection(a.geom,b.geom) FROM monschema.matable as a, monschema.matable as b WHERE st_intersects(a.geom,b.geom) AND a.idtable<>b.idtable


Oui, il faut ajouter une condition à la clause WHERE pour ignorer les intersections d'une ligne sur elle même.


Effectivement Tumasgiu, c'est corrigé grâce à ChristopheV.
Merci à tous les 2 !

Hors ligne

 

#9 Thu 13 September 2018 14:56

Hippo
Participant actif
Date d'inscription: 18 Jan 2015
Messages: 57

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Merci Nicolas Ribot, mais il apparait malheureusement que ma version de PostGis ne doit pas être assez récente pour supporter un st_clusterIntersecting.
A tester sur une version plus récente donc

Hors ligne

 

#10 Thu 13 September 2018 15:03

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgis - St_Intersects sur des géométries d'une même colonne

tumasgiu a écrit:
ChristopheV a écrit:

Bonjour,
Première chose ajouter une colonne numérique (serial) qui servira de clef primaire, nommons la idtable.

Ensuite on utilise une jointure réflexive.

Code:

SELECT a.label,b.label,st_intersection(a.geom,b.geom) FROM monschema.matable as a, monschema.matable as b WHERE st_intersects(a.geom,b.geom) AND a.idtable<>b.idtable


Oui, il faut ajouter une condition à la clause WHERE pour ignorer les intersections d'une ligne sur elle même.


Pour aller plus vite, et éviter de faire A inter B et B inter A (ce qui est la meme chose), la condition WHERE peut etre:
... AND a.idtable < b.idtable

On croise la moitié de la table avec l'autre moitié.

A condition que idtable soit bien un serial autoincrémenté.

Nicolas

Hors ligne

 

#11 Thu 13 September 2018 15:12

Hippo
Participant actif
Date d'inscription: 18 Jan 2015
Messages: 57

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Merci Nicolas pour cette réponse, j'ai suivi vos indications et ça fonctionne parfaitement !
Par contre, dans cet exemple, on croise les éléments 2 par 2.

Comment faire pour un nombre indéfini d'éléments qui s'intersectent comme par exemple récupérer les labels de 5 géométries qui s'intersectent ?

Dans notre cas présent, j'obtiens un tableau avec PC1 et PC2 s'intersectent, un autre avec PC2 et PC3 s'intersectent, et un autre avec PC1 et PC3 s'intersectent.

J'aurais besoin d'un unique tableau disant alors PC2 intersecte PC1 et PC3 (mais PC1 n'intersecte pas PC3)

Hors ligne

 

#12 Thu 13 September 2018 15:13

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

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Nicolas Ribot a écrit:
tumasgiu a écrit:
ChristopheV a écrit:

Bonjour,
Première chose ajouter une colonne numérique (serial) qui servira de clef primaire, nommons la idtable.

Ensuite on utilise une jointure réflexive.

Code:

SELECT a.label,b.label,st_intersection(a.geom,b.geom) FROM monschema.matable as a, monschema.matable as b WHERE st_intersects(a.geom,b.geom) AND a.idtable<>b.idtable


Oui, il faut ajouter une condition à la clause WHERE pour ignorer les intersections d'une ligne sur elle même.


Pour aller plus vite, et éviter de faire A inter B et B inter A (ce qui est la meme chose), la condition WHERE peut etre:
... AND a.idtable < b.idtable

On croise la moitié de la table avec l'autre moitié.

A condition que idtable soit bien un serial autoincrémenté.

Nicolas


Exact, c'est une astuce bien sympa, mais il me semble qu'il n'est pas nécessaire que cela soit un serial,
il suffit que la colonne soit une clef primaire ou du moins qu'une contrainte UNIQUE lui soit attachée
(dans tout les cas que la colonne puisse servir d'identifiant),
et que le type de la colonne supporte l'opérateur <.

Dernière modification par tumasgiu (Thu 13 September 2018 15:20)

Hors ligne

 

#13 Thu 13 September 2018 15:27

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

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Hippo a écrit:

Merci Nicolas pour cette réponse, j'ai suivi vos indications et ça fonctionne parfaitement !
Par contre, dans cet exemple, on croise les éléments 2 par 2.

Comment faire pour un nombre indéfini d'éléments qui s'intersectent comme par exemple récupérer les labels de 5 géométries qui s'intersectent ?

Dans notre cas présent, j'obtiens un tableau avec PC1 et PC2 s'intersectent, un autre avec PC2 et PC3 s'intersectent, et un autre avec PC1 et PC3 s'intersectent.

J'aurais besoin d'un unique tableau disant alors PC2 intersecte PC1 et PC3 (mais PC1 n'intersecte pas PC3)


De base, je ne pense pas que cela soit possible, il n'y pas de moyen de générer dynamiquement
des identifiants avec une simple requête SQL à ma connaissance.
Mais vous pourriez écrire une fonction pl/pgsql pour automatiser le croisement d'un nombre
indéterminé de tables. peut être qu'une extension faisant ce genre de chose existe.

Dernière modification par tumasgiu (Thu 13 September 2018 15:27)

Hors ligne

 

#14 Thu 13 September 2018 15:27

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgis - St_Intersects sur des géométries d'une même colonne

tumasgiu a écrit:
Nicolas Ribot a écrit:
tumasgiu a écrit:


Oui, il faut ajouter une condition à la clause WHERE pour ignorer les intersections d'une ligne sur elle même.


Pour aller plus vite, et éviter de faire A inter B et B inter A (ce qui est la meme chose), la condition WHERE peut etre:
... AND a.idtable < b.idtable

On croise la moitié de la table avec l'autre moitié.

A condition que idtable soit bien un serial autoincrémenté.

Nicolas


Exact, c'est une astuce bien sympa, mais il me semble qu'il n'est pas nécessaire que cela soit un serial,
il suffit que la colonne soit une clef primaire ou du moins qu'une contrainte UNIQUE lui soit attachée
(dans tout les cas que la colonne puisse servir d'identifiant),
et que le type de la colonne supporte l'opérateur <.


Oui effectivement ! Je l'ai tjs fait avec un serial mais ca marche pour toute colonne unique.

Hors ligne

 

#15 Thu 13 September 2018 15:32

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Bonjour,

Comment faire pour un nombre indéfini d'éléments qui s'intersectent comme par exemple récupérer les labels de 5 géométries qui s'intersectent ?


Passer à postgis 2.2 et utiliser clusterintersecting comme préconisé par Nicolas.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#16 Thu 13 September 2018 15:48

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

Re: Postgis - St_Intersects sur des géométries d'une même colonne

tumasgiu a écrit:
Hippo a écrit:

Merci Nicolas pour cette réponse, j'ai suivi vos indications et ça fonctionne parfaitement !
Par contre, dans cet exemple, on croise les éléments 2 par 2.

Comment faire pour un nombre indéfini d'éléments qui s'intersectent comme par exemple récupérer les labels de 5 géométries qui s'intersectent ?

Dans notre cas présent, j'obtiens un tableau avec PC1 et PC2 s'intersectent, un autre avec PC2 et PC3 s'intersectent, et un autre avec PC1 et PC3 s'intersectent.

J'aurais besoin d'un unique tableau disant alors PC2 intersecte PC1 et PC3 (mais PC1 n'intersecte pas PC3)


De base, je ne pense pas que cela soit possible, il n'y pas de moyen de générer dynamiquement
des identifiants avec une simple requête SQL à ma connaissance.
Mais vous pourriez écrire une fonction pl/pgsql pour automatiser le croisement d'un nombre
indéterminé de tables. peut être qu'une extension faisant ce genre de chose existe.


Désolé, j'avais pas vraiment lu/compris la question,
st_clusterintesecting comme précisé par Nicolas et Christophe semble tout indiqué.

Hors ligne

 

#17 Thu 13 September 2018 15:51

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3163
Site web

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Bonjour,

Pour une utilisation des fonctions de cluster cf :
https://georezo.net/forum/viewtopic.php … he+urbaine


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#18 Thu 13 September 2018 16:14

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Postgis - St_Intersects sur des géométries d'une même colonne

Hippo a écrit:

Merci Nicolas pour cette réponse, j'ai suivi vos indications et ça fonctionne parfaitement !
Par contre, dans cet exemple, on croise les éléments 2 par 2.

Comment faire pour un nombre indéfini d'éléments qui s'intersectent comme par exemple récupérer les labels de 5 géométries qui s'intersectent ?

Dans notre cas présent, j'obtiens un tableau avec PC1 et PC2 s'intersectent, un autre avec PC2 et PC3 s'intersectent, et un autre avec PC1 et PC3 s'intersectent.

J'aurais besoin d'un unique tableau disant alors PC2 intersecte PC1 et PC3 (mais PC1 n'intersecte pas PC3)


Vous pouvez faire des synthèses sur les croisements pour identifier les groupes en intersection:
(cf image de pg bidons: table testpg)

Code:

with tmp as (
    select t.id, t1.id as id1
    from testpg t
           join testpg t1 on st_intersects(t.geom, t1.geom) and t.id < t1.id
) select t.id, array_agg(t.id1) as id1
from tmp t
group by t.id
order by cardinality(array_agg(t.id1)) desc;

id    id1
1    {2,3,5,6}
3    {4,5,6}
7    {8,11}
9    {10}
5    {6}

Qui se lit: le plus grand groupe de pg en intersection sur la premiere ligne, avec pg 1, 2, 3, 5, et 6 concernés par l'intersection, groupe suivant sur la deuxieme ligne, etc.

Nicolas


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

Hors ligne

 

Pied de page des forums

Powered by FluxBB