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 Fri 31 January 2020 19:15

preliator
Participant assidu
Date d'inscription: 17 Nov 2018
Messages: 433

Case when ...

Bonjour à tous,

J'aimerais attribuer une valeur à un champ de ma couche parcelle (nommée "parcelles") si son centroide se situe dans un autre polygone représentant des espaces protégés (nommée "protege"). J'ai un problème avec la syntaxe à avoir dans ce cas.

Voici mon code :

Code:

update parcelles p set note_esp_protege = 
case
when st_within(st_centroid(p.geom), protege.geom) then 0
else -1
end
from protege

La requête tourne en boucle depuis plusieurs minutes, il y a donc très probablement une erreur.
Merci.

Hors ligne

 

#2 Sat 01 February 2020 11:13

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Case when ...

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

Code:

update parcelles p set note_esp_protege = -1;

Puis mettez a jour pour celles qui sont contenues:

Code:

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

En ligne

 

#3 Sat 01 February 2020 11:16

preliator
Participant assidu
Date d'inscription: 17 Nov 2018
Messages: 433

Re: Case when ...

Un grand merci à vous smile

Hors ligne

 

#4 Sun 03 May 2020 18:59

Theos2000
Participant assidu
Date d'inscription: 15 Jun 2015
Messages: 221

Re: Case when ...

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

Code:

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)

Hors ligne

 

#5 Mon 04 May 2020 09:51

preliator
Participant assidu
Date d'inscription: 17 Nov 2018
Messages: 433

Re: Case when ...

Bonjour,

Comme pour mon exemple, il manque une condition de jointure après votre "from". Je serais plutôt tenté de regarder du côté de "Where ... in ..."

Code:

update schema.table_1 as a  set info = 
case 
when a.id in (select a.id from schema.table_1 a, schema.table_2 b where st_intersects (a.geom,b.geom)) then 'OUI'
else 'NON'
end

A vérifier, ce n'est que spéculation de ma part smile

Hors ligne

 

#6 Mon 04 May 2020 10:35

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1534

Re: Case when ...

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

Code:

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:


Code:

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


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

En ligne

 

#7 Thu 07 May 2020 00:46

Theos2000
Participant assidu
Date d'inscription: 15 Jun 2015
Messages: 221

Re: Case when ...

Trés complet merci pour ces explications

Hors ligne

 

Pied de page des forums

Powered by FluxBB