Le portail francophone de la géomatique

af · Sat 14 January 2012 17:42

Bonjour
J'ai deux tables de polygones, avec dans la table A des zones qui recouvrent des zones de la table B.
Je souhaiterais mettre à jour dans un seul champ de la table A les ID des zones de la table B qui sont effectivement chevauchées, en sachant que je peux être dans une relation 1-N

Avec la requête suivante, je récupère pour chaque zone A le nombre de zones B qui sont chevauchées :

select idA, count(idA)
from tableA, tableB
WHERE ST_Intersects(tableA.the_geom, tableB.the_geom)
GROUP BY idA
HAVING count(idA) > 1;

Cette autre requête met à jour le champ id_tableB de la tableA mais en ne prenant qu'une seule valeur :

update tableA set id_tableB = idB from tableB
join tableA
ON ST_Intersects(tableA.the_geom, tableB.the_geom);

En espérant avoir exposé le pb correctement, je vous remercie pour les pistes

Nicolas Ribot · Sat 14 January 2012 20:43

af a écrit:

Bonjour
J'ai deux tables de polygones, avec dans la table A des zones qui recouvrent des zones de la table B.
Je souhaiterais mettre à jour dans un seul champ de la table A les ID des zones de la table B qui sont effectivement chevauchées, en sachant que je peux être dans une relation 1-N

Avec la requête suivante, je récupère pour chaque zone A le nombre de zones B qui sont chevauchées :

select idA, count(idA)
from tableA, tableB
WHERE ST_Intersects(tableA.the_geom, tableB.the_geom)
GROUP BY idA
HAVING count(idA) > 1;

Cette autre requête met à jour le champ id_tableB de la tableA mais en ne prenant qu'une seule valeur :

update tableA set id_tableB = idB from tableB
join tableA
ON ST_Intersects(tableA.the_geom, tableB.the_geom);

En espérant avoir exposé le pb correctement, je vous remercie pour les pistes

Bonsoir,

Il y a plusieurs solutions pour cela si je comprends bien ce que vous voulez faire:

1°) soit ajouter physiquement des lignes dans la tableA pour créer la relation 1-N, mais cela dupliquerait les données de la table A. C'est rarement le bon choix.

2°) Créer une table de lien entre les deux tables, qui réaliserait le lien 1-N, en ajoutant des clés étrangères pour garantir que le modèle est toujours correct:

Code:

-- chaque ligne représente un couple de zoneA et zoneB qui s'intersectent.

create table link as (
select idA, idB  
from tableA, tableB 
WHERE ST_Intersects(tableA.the_geom, tableB.the_geom)                                                                                                                                                                                                                           order by ida
);

-- clés étrangères a créer sur les tableA et tableB...

3°) Accumuler les identifiants de la tableB dans une colonne de la tableA:
• Soit en utilisant un array[integer] qui contiendra, pour chaque zoneA, un tableau des identifiants des zoneB qui intersectent la zoneA: Le type Array est puissant avec postgresql et permet de faire beaucoup de choses.

Code:

-- colonne de type tableau d'entier stockant les idb
alter table tableA add column idbs integer[];

-- mise a jour de la colonne: le SQL est un peu lourd car PG ne supporte pas le group by dans un UPDATE. Il faut passer par une sous-requete:
update tableA set idbs = ids 
from (
    select ida, array_agg(idb) as ids 
    from tableA, tableB 
    where st_intersects(tableA.the_geom, tableB.the_geom) 
    group by ida having count(ida) > 1
) as foo
where foo.ida = tableA.ida;

-- la liste des idb qui intersctent chaque ida: 
select ida, idbs from tableA order by ida;

-- le nombre d'intersections > 1 entre zoneA et zoneB:
select ida, array_length(idbs, 1) as num_intersection from tableA order by ida;

• Soit en utilisant une colonne texte et en concatenant les identifiants des zoneB qui intersectent: c'est moins puissant qu'en utilisant un tableau il me semble. Ca peut suffire a des fins d'affichage par ex.

Code:

-- définition d'une fonction agrégée permettant de concatener des textes:
CREATE AGGREGATE sum ( BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = '' );

-- creation de la colonne sur la tableA
alter table tableA add column idbsAsText text;

-- mise a jour de cette colonne.
update tableA set idbsAsText = ids 
from (
    select ida, sum(idb || ', ') as ids 
    from tableA, tableB 
    where st_intersects(tableA.the_geom, tableB.the_geom) 
    group by ida having count(ida) > 1
) as foo
where foo.ida = tableA.ida;

-- selection:
select ida, idbs, idbsAsText from tableA order by ida;

Nicolas

Dernière modification par Nicolas Ribot (Sat 14 January 2012 20:47)

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

af · Sun 15 January 2012 08:55

Merci pour ta réponse rapide Nicolas. Je vois que tu es toujours aussi réactif et compétent !!
J'ai pris la 3eme solution qui fonctionne à merveille.
Pour infos, sais tu si la syntaxe marcherait aussi sur SQlite/Spatialite (avec Qgis) ?

PS : si tu ne me remets pas, un indice : formation - une île de la Caraïbe il y a 3-4 ans

+

Alain

Dernière modification par af (Sun 15 January 2012 09:03)

Nicolas Ribot · Sun 15 January 2012 12:14

af a écrit:

Merci pour ta réponse rapide Nicolas. Je vois que tu es toujours aussi réactif et compétent !!
J'ai pris la 3eme solution qui fonctionne à merveille.
Pour infos, sais tu si la syntaxe marcherait aussi sur SQlite/Spatialite (avec Qgis) ?

Salut Alain !

Oui, je viens de voir que ça marche, avec une petite adaptation:

les tables et tableb ont été dumpées en shapefile puis chargées dans spatialite, la colonne the_geom devient "geometry"

L'operateur group_concat permet de joindre les valeurs d'une colonne (merci StackOverflow: http://stackoverflow.com/questions/5707 … in-sqlite)

Code:

select ida, group_concat(idb) as ids 
    from tableA, tableB 
    where intersects(tableA.geometry, tableB.geometry) 
    group by ida having count(ida) > 1;

PS : si tu ne me remets pas, un indice : formation - une île de la Caraïbe il y a 3-4 ans

+

Alain

Hmmm, souvenirs de la formation au paradis, oui je me souviens bien !
Que de bons souvenirs !

Nico

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 Sat 14 January 2012 17:42

POSTGIS - ST_intersects relation 1-n

#2 Sat 14 January 2012 20:43

Re: POSTGIS - ST_intersects relation 1-n

Code:

Code:

Code:

#3 Sun 15 January 2012 08:55

Re: POSTGIS - ST_intersects relation 1-n

#4 Sun 15 January 2012 12:14

Re: POSTGIS - ST_intersects relation 1-n

Code:

Pied de page des forums