Le portail francophone de la géomatique

Hippo · Thu 13 September 2018 12:34

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

tumasgiu · Thu 13 September 2018 14:22

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)

ChristopheV · Thu 13 September 2018 14:25

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)

tumasgiu · Thu 13 September 2018 14:45

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.

Hippo · Thu 13 September 2018 14:46

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.

ChristopheV · Thu 13 September 2018 14:47

Ouaf !!

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

Nicolas Ribot · Thu 13 September 2018 14:49

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

Hippo · Thu 13 September 2018 14:50

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 !

Hippo · Thu 13 September 2018 14:56

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

Nicolas Ribot · Thu 13 September 2018 15:03

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

Hippo · Thu 13 September 2018 15:12

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)

tumasgiu · Thu 13 September 2018 15:13

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)

tumasgiu · Thu 13 September 2018 15:27

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)

Nicolas Ribot · Thu 13 September 2018 15:27

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.

ChristopheV · Thu 13 September 2018 15:32

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.

tumasgiu · Thu 13 September 2018 15:48

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é.

ChristopheV · Thu 13 September 2018 15:51

Bonjour,

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

Nicolas Ribot · Thu 13 September 2018 16:14

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.

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Thu 13 September 2018 12:34

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

#2 Thu 13 September 2018 14:22

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

Code:

#3 Thu 13 September 2018 14:25

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

Code:

#4 Thu 13 September 2018 14:45

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

Code:

#5 Thu 13 September 2018 14:46

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

#6 Thu 13 September 2018 14:47

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

#7 Thu 13 September 2018 14:49

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

#8 Thu 13 September 2018 14:50

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

Code:

#9 Thu 13 September 2018 14:56

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

#10 Thu 13 September 2018 15:03

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

Code:

#11 Thu 13 September 2018 15:12

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

#12 Thu 13 September 2018 15:13

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

Code:

#13 Thu 13 September 2018 15:27

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

#14 Thu 13 September 2018 15:27

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

#15 Thu 13 September 2018 15:32

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

#16 Thu 13 September 2018 15:48

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

#17 Thu 13 September 2018 15:51

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

#18 Thu 13 September 2018 16:14

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

Code:

Pied de page des forums