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 23 November 2017 10:20

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

Jointure spatiale entre plusieurs tables

Bonjour,


Je dispose de 8 tables que j'aimerais joindre spatialement.
- J'ai en effet une table qui suite à une requête exécutée auparavant récupère une parcelle cadastrale en provenance des fichiers Majics.
- Une table contenant les zones de mon PLU
- une table contenant les prescriptions surfaciques
- une table contenant les prescriptions linéaires
- une table contenant les prescriptions ponctuelles
- une table contenant les informations surfaciques
- une table contenant les informations linéaires
- une table contenant les informations ponctuelles

N'ayant pas d'identifiant commun entre mes tables, J'aimerais pouvoir joindre spatialement cette parcelle avec mes zones PLU, mes prescriptions et mes informations de telle manière à avoir une table en sortie qui me dirait dans quelle zone du PLU se situe ma parcelle et si cette dernière intersecte une prescription surfacique, linéaire, ponctuelle, une information surfacique, linéaire ou ponctuelle et le type de prescription, information.

Pour ce faire, j'ai créer une requête comme suit :

Code:

SELECT parcelle.* ,
    plu.typezone ,
    plu.libelong,
    plu.datvalid,
    plu.urlfic,
    plu.geom,
    pscsurf.libelle, 
    pscsurf.geom, 
    psclin.libelle,
    psclin.geom,
    pscpct.libelle,
    pscpct.geom,
    infosurf.libelle, 
    infosurf.txt, 
    infosurf.geom,
    infolin.libelle,
    infolin.geom,
    infopct.libelle,
    infopct.geom
    FROM schema.parcelle AS parcelle,
    schema.zones_plu AS plu, 
    schema.psc_surf AS pscsurf,
    schema.psc_lin AS psclin,
    schema.psc_pct AS pscpct,
    schema.info_surf AS infosurf,
    schema.info_lin AS infolin,
    schema.info_pct AS infopct
 WHERE ST_DWithin(ST_BUFFER(parcelle.geom,-50), plu.geom,0) 
AND ST_Intersects (ST_BUFFER(parcelle.geom,-10), pscsurf.geom)
AND ST_Intersects (ST_BUFFER(parcelle.geom,-10), psclin.geom)
AND ST_Intersects (ST_BUFFER(parcelle.geom,-10), pscpct.geom)
AND ST_Intersects (ST_BUFFER(parcelle.geom,-10), infosurf.geom)
AND ST_Intersects (ST_BUFFER(parcelle.geom,-10), infolin.geom)
AND ST_Intersects (ST_BUFFER(parcelle.geom,-10), infopct.geom)

Mais cette requête ne fonctionne pas, à cause de mes AND.
J'ai essayé avec l'opérateur OR, ce qui serait bien plus logique, mais ma requête prend un temps infini et ne me retourne pas le résultat escompté.

Ainsi, quelle serait la méthode la plus productive pour arriver à mes fins ?
Une seule et unique requête avec des sous requêtes ?
Utiliser des triggers ?

Je ne suis malheureusement pas très calé en SQL et fonctionnement de bases de données, donc merci pour votre aide :-)

Hors ligne

 

#2 Thu 23 November 2017 11:05

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

Re: Jointure spatiale entre plusieurs tables

Salut,

peut être que des LEFT JOIN vous aiderait:

Code:

SELECT mes_champs
FROM parcelle p JOIN zone z ON ST_Intersects(p.geom, z.geom)
LEFT JOIN t1 ON ST_Intersects(p.geom, t1.geom)
LEFT JOIN t2 ON ST_Intersects(p.geom, t2.geom)
.
.
.

A noter que si votre parcelle intersecte plusieurs lignes d'une même table,
vous aurez fatalement des doublons.

On peut effectuer un petit GROUP BY  avec les agrégats qui vont bien
pour les éliminer par la suite.

Aussi, vérifiez que vous avez bien construit vos indexes.

Dernière modification par tumasgiu (Thu 23 November 2017 11:12)

Hors ligne

 

#3 Thu 23 November 2017 11:27

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

Re: Jointure spatiale entre plusieurs tables

Merci tumasgiu !! Ca a l'air de fonctionner parfaitement !!

Par contre, si je peux me permettre, concernant la construction des indexes, c'est-à-dire ?

Hors ligne

 

#4 Thu 23 November 2017 11:54

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

Re: Jointure spatiale entre plusieurs tables

Les indexes permettent d'éviter de scanner toute une table
pour rechercher les lignes qui satisfont une certaine condition,
en l'occurrence ici des intersections géométriques.

On peut vérifier qu'un index est construit en lancant :

Code:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' 
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner",
 c2.relname as "Table"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY 1,2;

On construit un index géo comme suit :

Code:

CREATE INDEX ON matable
USING GIST(ma_colonne_geo);

Hors ligne

 

#5 Thu 23 November 2017 11:56

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

Re: Jointure spatiale entre plusieurs tables

Bonjour,

récupère une parcelle cadastrale en provenance des fichiers Majics


La géométrie certainement !! wink

C'est un petit pb de conception: Une parcelle ça appartient à quoi ? un PLU ça appartient à quoi à qui, pourquoi, pour qui ?

Une parcelle appartient à une section et à une commune. Un plu appartient à une commune, une prescription aussi.

S'il existait un lien logique entre une table commune (idcommune) et les autres tables (ptrcommune pour dire j’appartiens à la commune de) vous réduiriez nettement les temps de calcul en ne calculant des notions spatiales que sur les objet appartenant à la même commune donc dont la valeur ptrcommune est identique.


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

Hors ligne

 

#6 Fri 24 November 2017 08:27

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

Re: Jointure spatiale entre plusieurs tables

Super, merci pour le code !
J'ai vérifié et mes indexes sont bien construits, pas de problème.

Autre chose Tumasgiu, vous avez raison quant aux doublons, certaines colonnes de fait, renvoient une même valeur.
Quand vous dites

On peut effectuer un petit GROUP BY  avec les agrégats qui vont bien
pour les éliminer par la suite.


C'est-à-dire faire quelquechose comme un COUNT sur mes colonnes, puis faire un GROUP BY par colonne HAVING COUNT >=1 ?
J'ai essayé de le faire, sans succès pour l'instant...

Oui, c'est exact ChristopheV que je devrais pouvoir alléger le temps de calcul en ciblant l'Id communal des différentes tables.
Donc quelquechose comme SELECT colonne FROM table WHERE codecommune = ... ?

Hors ligne

 

#7 Fri 24 November 2017 09:49

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

Re: Jointure spatiale entre plusieurs tables

Bonjour,

alléger le temps de calcul en ciblant l'Id communal des différentes tables


L'idée c'est le modèle de données.

Dans votre cas :

1) qu'est ce qu'une parcelle ? Au sens de qu'est ce qui la rend unique, identifiable de façon non équivoque ?
2) Qu'est ce qu'un PLU ?
etc ...

Dans la vraie vie une parcelle est dans une section d'une commune. Donc j'ai une table commune (idcommune serial, nom text, insee varchar[3], poulation integer, ...), une table section(idsection serial,ptrsection integer,,nomsection text, attributx type, ...) et une table parcelle (idparcelle,ptrsection integer, numero,geom, ...)

COMMUNE est composé de 1 à n SECTION  condition réalisée par : idcommune=ptrcommune
SECTION est composée de 1 à n PARCELLE condition réalisée par : idsection = ptrsection

Donc pour chercher la parcelle 003 0A 0456 (insee, section, numéro)

Code:

SELECT insee,nom,nomsection,numero FROM commune JOIN section ON idcommune=ptrcommune 
JOIN parcelle ON idsection=ptrsection 
WHERE insee='003' AND nomsection='0A' AND numero::integer=456

Nommons p1 la requête précédente (comme une CTE WITH p1 as (...))

Un PLU c'est relatif à une commune donc j'ai une table PLU (idplu serial,ptrcommune integer,machin,truc ...)

Donc la condition PLU.ptrcommune=p1.ptrcommune garantit que les objets géométriques appartiennent à la même commune.


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

Hors ligne

 

#8 Fri 24 November 2017 10:02

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

Re: Jointure spatiale entre plusieurs tables

Hippo a écrit:

C'est-à-dire faire quelquechose comme un COUNT sur mes colonnes, puis faire un GROUP BY par colonne HAVING COUNT >=1 ?
J'ai essayé de le faire, sans succès pour l'instant.


Je pensais à quelque chose du genre :

Code:

SELECT parcelle.geom,
st_collect(t1.geom ORDER BY x) FILTER (WHERE x IS NOT NULL) as t1_geom,
array_agg(t1.libelle ORDER BY x) FILTER (WHERE x IS NOT NULL) as t1_libelle,
.
.
.
GROUP BY parcelle.geom

Ainsi vous aurez a la fin une seule ligne, avec des collections de géométries
et leur libelle associés sous la forme de tableaux.

Hors ligne

 

#9 Fri 24 November 2017 10:39

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

Re: Jointure spatiale entre plusieurs tables

@ ChristopheV, merci, je vais tester ça !

@tumasgiu, désolé, je ne comprends pas bien ce bout de code.
Comment dois je l'intégrer dans mon code cité au début du post ?
Quand j'essaie, ça me dit qu'il y a une erreur au niveau de FILTER.
Et si j'intègre ce code dans mon code précédant, j'ai également une erreur me disant que mes autres colonnes sélectionnées doivent également apparaitre dans le GROUP BY ou dans une fonction d'aggrégation.

Hors ligne

 

#10 Fri 24 November 2017 11:01

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

Re: Jointure spatiale entre plusieurs tables

l'idée est de regrouper vos autres colonnes dans des agrégats, tableaux ou MULTIGEOM,
en filtrant les nuls, et en suivant toujours le même ordre,
afin d'avoir une correspondance entre la position d'une géométrie
dans une collection et la position d'un libellé dans un tableau.

Hors ligne

 

#11 Fri 24 November 2017 13:20

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

Re: Jointure spatiale entre plusieurs tables

Ok, je vois sur l'idée ce que ça donnerait, mais insérer ce code me renvoie des erreurs.

Code:

SELECT parcelle.* ,
    plu.typezone ,
    plu.libelong,
    plu.datvalid,
    plu.urlfic,
    plu.geom,
    pscsurf.libelle, 
    pscsurf.geom, 
    psclin.libelle,
    psclin.geom,
    pscpct.libelle,
    pscpct.geom,
    infosurf.libelle, 
    infosurf.txt, 
    infosurf.geom,
    infolin.libelle,
    infolin.geom,
    infopct.libelle,
    infopct.geom,
    parcelle.geom,
    st_collect(t1.geom ORDER BY x) FILTER (WHERE x IS NOT NULL) as t1_geom,
    array_agg(t1.libelle ORDER BY x) FILTER (WHERE x IS NOT NULL) as t1_libelle
    FROM cadastre.parcelle AS parcelle JOIN schema.zones_plu AS plu ON ST_DWithin(ST_BUFFER(parcelle.geom,-1), plu.geom,0) 
    LEFT JOIN schema.psc_surf AS pscsurf ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), pscsurf.geom)
    LEFT JOIN schema.psc_lin AS psclin ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), psclin.geom)
    LEFT JOIN schema.psc_pct AS pscpct ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), pscpct.geom)
    LEFT JOIN schema.info_surf AS infosurf ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infosurf.geom)
    LEFT JOIN schema.info_lin AS infolin ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infolin.geom)
    LEFT JOIN schema.info_pct AS infopct ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infopct.geom)
    GROUP BY parcelle.geom

Désolé, mais niveau en SQL est vraiment basique :-(

Dernière modification par Hippo (Fri 24 November 2017 13:23)

Hors ligne

 

#12 Fri 24 November 2017 14:02

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

Re: Jointure spatiale entre plusieurs tables

Désolé, le code que j'ai écrit était un exemple qui n’était pas destiné
à être utilisé tel quel, je pensais que c’était implicite.

Voici un exemple normalement fonctionnel qui se limite à la jointure
d'une seule table. On voit que les colonnes info_pct.geom et libelle
ont été remplaçées par des foncitons d’agrégat qui donneront en sortie
un colllection de point et un tableau contenant les libellés.

Code:

SELECT 
parcelle.geom,
st_collect(infopct.geom ORDER BY infopct.libelle) 
FILTER (WHERE infopct.geom IS NOT NULL) as info_pct_geom,
array_agg(infopct.libelle ORDER BY infopct.libelle) 
FILTER (WHERE infopct.geom IS NOT NULL) as info_pct_libelle
FROM cadastre.parcelle AS parcelle
LEFT JOIN schema.info_pct AS infopct 
ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infopct.geom)
GROUP BY parcelle.geom

Dernière modification par tumasgiu (Fri 24 November 2017 14:04)

Hors ligne

 

#13 Fri 24 November 2017 15:09

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

Re: Jointure spatiale entre plusieurs tables

Merci beaucoup pour votre aide et votre patience !!
Par contre, j'ai toujours une erreur au niveau du FILTER

syntax error at or near "("
LINE 6: FILTER (WHERE infopct.geom IS NOT NULL) as info_pct_geom,

Hors ligne

 

#14 Fri 24 November 2017 15:15

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

Re: Jointure spatiale entre plusieurs tables

Oui je crois que la syntaxe FILTER est dispo a partir de Postgresql 9.4

Hors ligne

 

#15 Fri 24 November 2017 16:13

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

Re: Jointure spatiale entre plusieurs tables

Oui il semblerait :-(

La requête marche tout de même, même si effectivement certains champs sont renseignés NULL.

Dernière question a priori SVP, comment je récupère dans mon tableau mes autres colonnes qui sont dans parcelle.* et les autres colonnes provenant de ma table plu ? Car si je les mets dans le SELECT j'ai une erreur  disant que ces colonnes doivent également apparaitre dans le GROUP BY.

Code:

SELECT 
parcelle.geom,
st_collect(plu.geom ORDER BY plu.libelong) ,
array_agg(plu.libelong ORDER BY plu.libelong),
st_collect(pscsurf.geom ORDER BY pscsurf.libelle) ,
array_agg(pscsurf.libelle ORDER BY pscsurf.libelle) AS psc_surf_libelle,
st_collect(psclin.geom ORDER BY psclin.libelle ),
array_agg(psclin.libelle ORDER BY psclin.libelle) AS psc_lin_libelle, 
st_collect(pscpct.geom ORDER BY pscpct.libelle),
array_agg(pscpct.libelle ORDER BY pscpct.libelle) AS psc_pct_libelle,
st_collect(infosurf.geom ORDER BY infosurf.libelle ),
array_agg(infosurf.libelle ORDER BY infosurf.libelle) AS info_surf_libelle,
st_collect(infolin.geom ORDER BY infolin.libelle ),
array_agg(infolin.libelle ORDER BY infolin.libelle) AS info_lin_libelle,
st_collect(infopct.geom ORDER BY infopct.libelle ),
array_agg(infopct.libelle ORDER BY infopct.libelle) AS info_pct_libelle
FROM cadastre.parcelle AS parcelle
JOIN schema.zones_plu AS plu ON ST_DWithin(ST_BUFFER(parcelle.geom,-1), plu.geom,0) 
LEFT JOIN schema.psc_surf AS pscsurf ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), pscsurf.geom) 
LEFT JOIN schema.psc_lin AS psclin ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), psclin.geom) 
LEFT JOIN schema.psc_pct AS pscpct ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), pscpct.geom)
LEFT JOIN schema.info_surf AS infosurf ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infosurf.geom)
LEFT JOIN schema.info_lin AS infolin ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infolin.geom)
LEFT JOIN schema.info_pct AS infopct ON ST_Intersects (ST_BUFFER(parcelle.geom,-10), infopct.geom)
GROUP BY parcelle.geom

Hors ligne

 

#16 Fri 24 November 2017 16:49

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

Re: Jointure spatiale entre plusieurs tables

Il faut pour cela remplacer

Code:

GROUP BY parcelle.geom

par

Code:

GROUP BY parcelle.*

Hors ligne

 

#17 Sat 25 November 2017 11:38

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

Re: Jointure spatiale entre plusieurs tables

Ah oui, tout simplement en fait.

Merci tumasgiu !!!

Hors ligne

 

Pied de page des forums

Powered by FluxBB