Le portail francophone de la géomatique

Nicolas Ribot · Wed 29 September 2021 19:21

Oui, c'est possible

https://www.postgresql.org/docs/13/inde … ional.html

Nicolas

Nicolas Ribot · Thu 30 September 2021 12:22

Bonjour,

Pour préciser un peu le traitement de hier et si j'ai bien compris: votre réseau contient des segments géométriquement identiques mais avec potentiellement différentes valeurs de v19, dont vous voulez faire la somme.
Dans ce cas, le plus simple est de "nettoyer" votre jeu de données en ne gardant qu'un exemplaire du segment, et en faisant la somme des valeurs des segments identiques, puis de lancer la récursion sur ce réseau propre.

Par exemple:

Code:

create table data_clean as
    select  id_bdcarth::int, sum(v19) as v19, st_geometryn(wkb_geometry, 1)::geometry(linestring, 2972) as geom
    from data_subset
    group by 1, 3;

La requête récursive proposée hier donne alors ce résultat:

Code:

id_bdcarth    vals
973195700    6
973196921    6
973196989    6
973197028    6
973195702    5
973195748    5
973195760    2
973195709    1
973195717    1
973195720    1
973195726    1
973195695    0
973195712    0
973195716    0
973195725    0
973195729    0
973195730    0
973195734    0
973195736    0
973195743    0
973195755    0
973195761    0
973195763    0
973196977    0

Nicolas

neskuik01 · Thu 30 September 2021 14:02

Nicolas Ribot a écrit:

Bonjour,

Pour préciser un peu le traitement de hier et si j'ai bien compris: votre réseau contient des segments géométriquement identiques mais avec potentiellement différentes valeurs de v19, dont vous voulez faire la somme.
Dans ce cas, le plus simple est de "nettoyer" votre jeu de données en ne gardant qu'un exemplaire du segment, et en faisant la somme des valeurs des segments identiques, puis de lancer la récursion sur ce réseau propre.

Par exemple:
Code:
create table data_clean as
    select  id_bdcarth::int, sum(v19) as v19, st_geometryn(wkb_geometry, 1)::geometry(linestring, 2972) as geom
    from data_subset
    group by 1, 3;
La requête récursive proposée hier donne alors ce résultat:
Code:
id_bdcarth    vals
973195700    6
973196921    6
973196989    6
973197028    6
973195702    5
973195748    5
973195760    2
973195709    1
973195717    1
973195720    1
973195726    1
973195695    0
973195712    0
973195716    0
973195725    0
973195729    0
973195730    0
973195734    0
973195736    0
973195743    0
973195755    0
973195761    0
973195763    0
973196977    0
Nicolas

Bonjour Oui exactement c'est ce que j'ai fais . Comme dit sur le subset fournit votre requête à très bien fonctionné mais c'est sur un réseau un peu plus volumineux que ca échoue (il s'agit bien d'un réseau logique avec une topologie respectée). j'ai exclu les test redondants en incluant une condition "WHERE not(d.ogc_fid = any(ids))", mais après 5h de moulinette je n'avais toujours pas de résultat (et il ne s'agit là que d'1/20 ème du réseau hydrographique que je dois couvrir ^^).

Code:

create table add_3reg_v19 as 
select * from (
with recursive toto as (
    select (array_agg(ogc_fid))[1] as ogc_fid,
           (array_agg(v))[1] as v,
           1::int as iter,
           array_agg(ogc_fid) as fids,
           (array_agg(st_endpoint(geom)))[1] as geom,
           array_agg[ogc_fid] as ids
    from data_subset
    group by unnest(ARRAY[st_startpoint(geom), st_endpoint(geom)])
    having count(*) = 1

    UNION ALL

    select d.ogc_fid,
           d.v,
           iter+1,
           t.fids || d.ogc_fid,
           st_endpoint(d.geom),
           ids || d.ogc_fid
    from data_subset d join toto t on st_dwithin(t.geom, st_startpoint(d.geom), 0.001)
    WHERE not(d.ogc_fid = any(ids)) -- exclu les tuples déjà testés
), tmp as (
    select ogc_fid, v, unnest(fids[1:cardinality(fids) - 1]) as childs
    from toto
), tmp1 as (
    select t.ogc_fid, t.v, array_agg(distinct childs) as childs
    from tmp t
    group by t.ogc_fid, t.v
) 
    
select t.ogc_fid, t.v + sum(d.v) as vals, dd.geom
from tmp1 t join data_subset d on d.ogc_fid = any(t.childs)
            join data_subset dd on t.ogc_fid = dd.ogc_fid
group by t.ogc_fid, t.v, dd.geom
    ) as t;

Pour les index c'est intéressant je ne savais pas que c'était possible . En revanche, je ne vois pas vraiment ou les mettre, avant le with c'est inutile car toto n'existe pas, entre toto et select c'est pas possible et après c'est inutile car en dehors du processus.

Dernière modification par neskuik01 (Thu 30 September 2021 14:37)

Nicolas Ribot · Thu 30 September 2021 15:32

Bonjour,

Je vous invite à réviser le fonctionnement des index (https://www.postgresql.org/docs/13/indexes.html).
Un index se crée sur une table, en amont des traitements que l'on veut faire sur cette table: ca ne s'insère pas dans une requête SQL.
Le principe général est de créer des index sur les colonnes qui sont utilisées dans une clause WHERE d'une requête.

Dans la requête récursive, le test le plus couteux est de trouver les connexions entre segments, on utilise st_dwithin pour cela (pour prendre en compte une éventuelle imprécision dans les données).
L'index ne doit pas etre créé sur toto, mais sur data_subset, table que l'on utilise dans la récursion. Comme on se sert des startpoint et endpoint des segments de data_subset, il faut créer deux index sur ces fonctions (un seul probablement est utile, mais dans le doute, on peut en créer 2, avant de vérifier leur usage avec un explain ...).

Code:

create index on data_subset using gist(st_startpoint(geom));
create index on data_subset using gist(st_endpoint(geom));
analyze data_subset;

(vous pouvez envoyer vos données qui moulinent ? au format zip, ou mieux, un dump de la table au format custom)

(et si vos identifiants de noeuds de départ et de fin de segment sont bons, utilisez ces valeurs plutot que st_dwithin, ca ira bcp plus vite (créez des index sur ces colonnes)

Nicolas

neskuik01 · Fri 01 October 2021 00:08

Héhé c'est beaucoup plus logique effectivement ^^.
J'ai relancé et j'ai eu une erreur de mémoire épuisé "échec d'une requête de taille 620"
Je retenterais demain matin en local j'ai l'impression que le serveur postgres a quelque souci.

J'ai mis le dump en PJ.

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

neskuik01 · Wed 06 October 2021 19:02

J'arrive désormais à travailler à l'échelle de la zone hydrographique, je pense donc partir sur une identification des zones hydro amont/aval et boucler ainsi sur chaque bassin versant.

Par contre je remarque (c'est pas bien génant) que la première valeur est systématiquement exclue (elle apparait bien dans l'addition mais pas en tant que valeurs ). Je pense que ca vient de " t.v + sum(d.v)" qui nécessite un retour de la part du with.

Nicolas Ribot · Thu 07 October 2021 10:43

Bonjour,

Oui telle que la requête est faite en recursion, les premières valeurs ne sont renvoyées.
Si vous les voulez, vous faites un UNION ALL dans la derniere requête, en reprenant la partie SQL du WITH RECURSIVE avant le UNION

Nicolas

neskuik01 · Tue 16 November 2021 19:23

Bonjour,

Juste pour dire un petit merci à ceux qui m'ont aidé . J'ai finis par réussir à faire mes calculs en utilisant Python - Pandas. Je reviendrais poster un tuto pour ceux que ca intéresse.

à suivre

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

#31 Wed 29 September 2021 19:21

Re: répercuter des valeurs d'un segment à un autre

#32 Thu 30 September 2021 12:22

Re: répercuter des valeurs d'un segment à un autre

Code:

Code:

#33 Thu 30 September 2021 14:02

Re: répercuter des valeurs d'un segment à un autre

Code:

Code:

Code:

#34 Thu 30 September 2021 15:32

Re: répercuter des valeurs d'un segment à un autre

Code:

#35 Fri 01 October 2021 00:08

Re: répercuter des valeurs d'un segment à un autre

#36 Wed 06 October 2021 19:02

Re: répercuter des valeurs d'un segment à un autre

#37 Thu 07 October 2021 10:43

Re: répercuter des valeurs d'un segment à un autre

#38 Tue 16 November 2021 19:23

Re: répercuter des valeurs d'un segment à un autre

Pied de page des forums