Le portail francophone de la géomatique

Bonjour,

Il manque la jointure sur les deux tables parcelles et protege. Ca risque de tourner un moment, oui !

Mettez toutes les note_esp_protege a -1

update parcelles p set note_esp_protege = -1;

Puis mettez a jour pour celles qui sont contenues:

update parcelles p set note_esp_protege = 0
from protege
where st_within(st_centroid(p.geom), protege.geom);

vacuum analyze parcelles;

Ca ira plus vite si vous créez un index sur st_centroid(p.geom) sur la table parcelles (index fonctionnel)

Nico

Bonjour je me permet de relancer ce post car j'ai une problématique similaire ou je dois faire un update d'une colonne avec plusieurs informations comme 'oui' et 'non'. J'ai un fichier de points (table 1)  et je dois dire si ils intersectectent  avec des polygones (table 2 ) .

update schema.table_1 as a  set info = case when st_intersects (a.geom,b.geom) then 'oui' else 'non'  end  from schema.table_2 as b;

Le requête ne me renvoi des résultats que sur l'intersection d'un seul polygone alors qu'il y en a plusieurs....

J'ai contourné le problème en faisant un update en plusieurs étapes, mais je trouve ça dommage car un case wen peut etre pratique pour distinguer plusieurs types d'intersections ou autre mise a jour.

Si vous avez un élément de réponse sur le sujet...ou des exemples d'updates utilisant des case when.

Merci

Dernière modification par Theos2000 (Sun 03 May 2020 19:00)

Bonjour,

Oui il y a un problème avec l'update sous cette forme: si un point a des relations avec plusieurs polygones, il va apparaitre plusieurs fois dans la table virtuelle faite lors de l'update, mais une seule valeur sera mise à jour au final pour ce point (la derniere valeur uniquement)

Ensuite, comme je vous le disais, un update a partir de deux tables, sans lien entre les tables (jointure), ca fait un produit cartésien: nombre de lignes de la table  x nbre de lignes de la table 2: si les tables sont toutes petites, ca va, mais sinon, la requete ne finira jamais (100 000 parcelles vs 10 000 points, ca fait une combinatoire de 10 milliards de lignes...).

Le plus efficace est de faire en deux fois: premiere passe, pour tous les points, valeur non.
deuxieme passe, pour les points qui ont une interaction avec les polygones (st_intersects, ou st_dwithin si on veut prendre en compte la précision), on calcule le type d'intersection. (index spatiaux sur les tables indispensable, sauf si toutes petites tables)

Ensuite, vous devez savoir quel type d'info vous voulez stocker: si vous voulez juste savoir si un point a une relation avec un polygone, c'est plus simple que si vous voulez la liste des relations qu'un point peut avoir avec les polygones:

(cf image, les labels correspondent aux gid des deux tables pt et pg)

• Cas 1: info simple pour les points: ont-ils une relation avec les pg ?:

alter table pt add column info_simple text;

-- update de tous les points (peut etre fait aussi en définissant une valeur par défaut sur la colonne info_simple)
-- alter table pt add column info_simple text default '0 interaction';
-- tout dépend de quand est définie la colonne
update pt set info_simple = '0 interaction';

-- puis update des points avec une relation avec les pg
update pt set info_simple = '1 interaction au moins'
from pg p
where st_intersects(p.geom, pt.geom);

select gid, info_simple from pt;

gid    info_simple
1    0 interaction
2    0 interaction
3    0 interaction
7    0 interaction
6    1 interaction au moins
4    1 interaction au moins
5    1 interaction au moins
10    1 interaction au moins
9    1 interaction au moins
8    1 interaction au moins

-- le pt gid=6 par exemple, a une interaction avec deux polygones (gid 1 et 2), l'update est passé deux fois pour ce point:
-- 1 fois pour l'interaction 6-1, une fois pour l'interaction 6-2

• Cas 2, on veut garder l'info plus précise du type d'interaction d'un point avec un polygone, et l'id du polygone concerné
il faut utiliser group by pour regrouper les points par gid, et faire la synthèse des interactions
avec un case, on peut alors savoir quel type d'interaction entre un point et un pg.

La requête peut etre écrite plus directement en update, sans utiliser une CTE, mais je trouve pratique la CTE pour les update: on teste d'abord la requete en select pour s'assurer qu'elle marche, puis on la transforme en update ensuite:

alter table pt add column info_detail text default 'ø';

-- synthèse des interactions par point (group by pt.gid) et case pour le type d'interaction
with tmp as (
    select pt.gid,
           string_agg(case
                          when st_touches(pt.geom, pg.geom)
                              then 'touche pg: ' || pg.gid
                          when st_within(pt.geom, pg.geom)
                              then 'contenu dans pg: ' || pg.gid
                          else 'autre relation ?!' end, ', ') as detail
    from pt
             join pg on st_intersects(pt.geom, pg.geom)
    group by pt.gid
) update pt set info_detail = t.detail
from tmp
where pt.gid = tmp.gid;

-- normalement, le "else" n'est jamais déclenché: un point en interaction avec un polygone est soit sur son contour (touches), soit contenu dans le polyone (st_within(a,b) ou st_contains(b, a) )

select gid, info_detail from pt;

gid    info_detail
1    ø
2    ø
3    ø
7    ø
6    touche pg: 1, contenu dans pg: 2
4    contenu dans pg: 3, contenu dans pg: 4
5    contenu dans pg: 5
10    touche pg: 6, contenu dans pg: 5
9    contenu dans pg: 7, touche pg: 8
8    contenu dans pg: 9

Nico