Le portail francophone de la géomatique

Hippo · Thu 23 November 2017 10:20

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

tumasgiu · Thu 23 November 2017 11:05

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)

Hippo · Thu 23 November 2017 11:27

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

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

tumasgiu · Thu 23 November 2017 11:54

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

ChristopheV · Thu 23 November 2017 11:56

Bonjour,

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

La géométrie certainement !!

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.

Hippo · Fri 24 November 2017 08:27

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

ChristopheV · Fri 24 November 2017 09:49

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.

tumasgiu · Fri 24 November 2017 10:02

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.

Hippo · Fri 24 November 2017 10:39

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

tumasgiu · Fri 24 November 2017 11:01

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.

Hippo · Fri 24 November 2017 13:20

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)

tumasgiu · Fri 24 November 2017 14:02

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)

Hippo · Fri 24 November 2017 15:09

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,

tumasgiu · Fri 24 November 2017 15:15

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

Hippo · Fri 24 November 2017 16:13

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

tumasgiu · Fri 24 November 2017 16:49

Il faut pour cela remplacer

Code:

GROUP BY parcelle.geom

par

Code:

GROUP BY parcelle.*

Hippo · Sat 25 November 2017 11:38

Ah oui, tout simplement en fait.

Merci tumasgiu !!!

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

Jointure spatiale entre plusieurs tables

Code:

#2 Thu 23 November 2017 11:05

Re: Jointure spatiale entre plusieurs tables

Code:

#3 Thu 23 November 2017 11:27

Re: Jointure spatiale entre plusieurs tables

#4 Thu 23 November 2017 11:54

Re: Jointure spatiale entre plusieurs tables

Code:

Code:

#5 Thu 23 November 2017 11:56

Re: Jointure spatiale entre plusieurs tables

#6 Fri 24 November 2017 08:27

Re: Jointure spatiale entre plusieurs tables

#7 Fri 24 November 2017 09:49

Re: Jointure spatiale entre plusieurs tables

Code:

#8 Fri 24 November 2017 10:02

Re: Jointure spatiale entre plusieurs tables

Code:

#9 Fri 24 November 2017 10:39

Re: Jointure spatiale entre plusieurs tables

#10 Fri 24 November 2017 11:01

Re: Jointure spatiale entre plusieurs tables

#11 Fri 24 November 2017 13:20

Re: Jointure spatiale entre plusieurs tables

Code:

#12 Fri 24 November 2017 14:02

Re: Jointure spatiale entre plusieurs tables

Code:

#13 Fri 24 November 2017 15:09

Re: Jointure spatiale entre plusieurs tables

#14 Fri 24 November 2017 15:15

Re: Jointure spatiale entre plusieurs tables

#15 Fri 24 November 2017 16:13

Re: Jointure spatiale entre plusieurs tables

Code:

#16 Fri 24 November 2017 16:49

Re: Jointure spatiale entre plusieurs tables

Code:

Code:

#17 Sat 25 November 2017 11:38

Re: Jointure spatiale entre plusieurs tables

Pied de page des forums