Le portail francophone de la géomatique

Sylvain M. · Thu 02 November 2017 12:29

Bonjour à tous,

Je dispose d'une table de tronçons (polylignes) dont je souhaite faire des analyses statistiques par mailles (régulières et communales).
Cette table contenant plus d'un million d'entités, j'aimerais optimiser la requête pour ne pas qu'elle s'éternise et pour la mettre à jour régulièrement.
J'ai découvert récemment la puissance des fonctions ST_SnapToGrid() et ST_Expand() combinées (voir ce message où je propose une méthode), pour faire des analyses statistiques sans même faire de jointure spatiale (dans le cas de mailles régulières, et de données ponctuelles en entrée).

Je me demande s'il n'y aurait pas, dans le cas des maillages réguliers (ne marche pas pour les communes), une piste pour le faire sur une couche linéaire ? (en décomposant en nœuds préalablement)

Voila comment je procédais avant, avec les communes :

- création (ou récupération) de la couche des mailles (grille / commune)

- découper les entités lignes par ces mailles :

Code:

SELECT    l.id,
    -- autres attribus pour stats
    geom(ST_dump(ST_Intersection(l.geom,c.geom))) AS geom,
    c.id
FROM    lignes l,
    communes c
WHERE    ST_Intersects(c.geom,l.geom) AND c.geom && l.geom
GROUP BY l.id,
    geom(ST_dump(ST_Intersection(l.geom,c.geom))),
    c.id

(très très très long !!! malgré l'utilisation d'index spatiaux, la requête met 1:20min sur un échantillon de 22000 lignes... soit plus d'une heure sur le total)

- calculer les statistiques sur ces lignes découpées avec un GROUP BY et des stats sur les attributs

Dans la requête ci-dessus, je me base sur les communes. La géométrie est complexe, mais le nombre d'entité limité (~200 sur mon territoire).
Mais je souhaite faire la même chose sur des mailles régulières de 1Kmx1Km (voire moins, 250m par exemple), et là, le nombre d'entités va devenir bien plus grand (2500km² soit 10 000 mailles de 500mx500m ou 40 000 mailles de 250mx250m).
Du coup, je n'ose même pas imaginer le temps de calcul si je fais un ST_Intersection !!!

Un petit schéma pour illustrer la donnée. Certains reconnaitront certainement la thématique de travail, mais ça marche aussi bien avec des routes, des cours d'eau, ... ;-)

Merci pour vos conseils !

Sylvain M. · Thu 02 November 2017 12:57

La capture d'écran ne passe pas en pièce jointe (?) : je tente via un hébergement externe :
[img]https://img4.hostingpics.net/pics/591738postgisintersect.png[/img]

Dernière modification par Sylvain M. (Thu 02 November 2017 13:00)

Sylvain M. · Thu 02 November 2017 15:54

Étrange !!??
Je viens finalement d’exécuter ces requêtes d'intersection :
- sur l'ensemble de mes communes, la requête à mis 40 min (un peu moins d'1h attendu, mais ça correspond grosso modo à ce que j'imaginais)
- sur mes mailles carrées (1kmx1Km), soit 2500 mailles, cela n'a mis que 3:40min !!! (alors que je pensais que ce serait beaucoup plus long que les communes !!)

Du coup, le temps de calcul avec les communes vient surtout du fait (j'imagine) de la complexité des polygones qui les composent, plus que du nombre d'intersection avec le linéaire de tronçons.

N'hésitez pas à me signaler si vous voyez malgré tout des optimisations !

tumasgiu · Thu 02 November 2017 16:19

Salut,

pas vraiment une optim mais j'ai du mal à comprendre votre requête,
notamment la partie GROUP BY.

Je loupe quelquechose ou vous pouvez ré-ecrire la requête comme suit ?

Code:

SELECT c.id, l.id, geom
FROM (SELECT c.id, l.id, (st_dump(st_intersection(g.geom, l.geom))).geom
          FROM c JOIN l ON c.geom && l.geom) foo
WHERE NOT st_isempty(geom)

Dernière modification par tumasgiu (Thu 02 November 2017 16:21)

Sylvain M. · Fri 03 November 2017 14:30

j'ai du mal à comprendre votre requête, notamment la partie GROUP BY.

Tu as raison : le GROUP BY est inutile !! oups

Par contre, je crois sauf erreur que pour le reste, nos requêtes sont équivalentes :

Code:

SELECT * FROM table1 JOIN table2 ON table1.truc = table2.bidule

me semble équivalent à

Code:

SELECT * FROM table1, table2 WHERE table1.truc = table2.bidule

Non ?

En tout cas, pour avoir essayé les 2 sur mes données, la durée est identique.

tumasgiu · Fri 03 November 2017 14:38

Tout juste, c'est équivalent.

Je m'étais dit que tester si les géométries s'intersectent puis de calculer leur intersections
serait peut être plus couteux que de calculer toutes les intersections et de ne retenir que celles non-vides,
mais apparemment vos essais disent le contraire.

Une autre façon de faire aurait été de découper vos linéaires avec st_split, puis de vous débrouiller pour
grouper les résultats par coordonnées mais je ne suis pas sur que vous soyez gagnant en performance,
par contre ce qui est sur c'est que votre requête sera plus complexe.
A noter que c'est juste une idée je ne sais pas si c'est réellement faisable.

ChristopheV · Thu 09 November 2017 09:34

Bonjour,

Je fais un peu pareil avec une couche de polygones et un MNT.

Ce qu'il faut déjà c'est un index spatial sur les géométries.
J'inverserai : st_intersects(geom.l,geom.c)

Et l'idée est de constituer des multipolygone ou ici des multi polylignes. En effet chaque polyligne est testée, ( && et st_intersects), même si la géométrie d'une multipolyligne est plus complexe, il est plus rapide de tester l'intersection de deux multi, que l'intersection des composantes de ces deux multi.

Sylvain M. · Fri 10 November 2017 10:41

Salut Christophe,

Ce qu'il faut déjà c'est un index spatial sur les géométries.

Ca, c'est bon ! ;-)

J'inverserai : st_intersects(geom.l,geom.c)

Je suppose que tu voulais dire st_intersects(l.geom, c.geom) ?
Effectivement, je me posais la question si l'ordre avait de l'importance dans une intersection ? Tu semble dire que oui : as-tu des éléments pour me l'expliquer ? (désolé si c'est basique !)

Et l'idée est de constituer des multipolygone ou ici des multi polylignes

En regroupant mes polylignes en amont ? Je ne vois pas trop dans mon cas selon quels critères ? (spatiaux ? attributaires ?)

Dernière modification par Sylvain M. (Fri 10 November 2017 10:42)

ChristopheV · Sat 11 November 2017 08:57

Bonjour
Effectivement j'ai une tendance naturelle à la dyslexie.
L'ordre en fait n'a pas d'importance, mais c'est l'habitude de penser "inclus dans" (st_within() ). Donc ma remarque était inutile.
Pour le reste c'est une histoire de nombre d'éléments à comparer et de complexité de géométrie. Tu as constaté que c'est plus rapide avec des carrés que des polygones avec de nombreux sommets. Effectivement grouper les lignes c'est pas une bonne piste.
Il est parfois difficile de faire autrement que de laisser du temps au calcul. J'ai lancé jeudi une requête pour calculer la différence entre deux multi polygones et à cette heure elle est toujours en cours ....

Pierre · Sat 11 November 2017 20:34

Aloha

Quelques pistes que j'explorerais :
1/ en propageant à tout le réseau un "tag"/attribut de type codcom, cela n'améliorerait-il pas le temps de requête ? Si les mailles ont elles aussi cet attribut. L'avantage est que cet attribut est très simple à mettre à jour via un trigger.
2/faire un explain des requêtes en inversant l'ordre des geométries (réseau et mailles) pour voir laquelle est la moins couteuse.
3/tester les opérateurs && . Quitte à l'utiliser en amont de la requête d'intersection. L'idée étant de ne calculer les intersections maille/réseau que lorsque le critère && est vrai.

Voilà. J'espère que cela pourra être utile.

ChristopheV · Mon 13 November 2017 09:48

Bonjour,

J'ai lancé jeudi une requête pour calculer la différence entre deux multi polygones et à cette heure elle est toujours en cours ....

Cf. pièce jointe

Qui ne s'affiche pas .. un bugg ?

Donc :

Query returned successfully: one row affected, 2 days, 18:2920:15 hours execution time.

Dernière modification par ChristopheV (Mon 13 November 2017 09:50)

Sylvain M. · Mon 13 November 2017 12:23

Merci encore pour vos avis/conseils !

Pierre a écrit:

1/ en propageant à tout le réseau un "tag" [...] simple à mettre à jour via un trigger.

Oui, j'y avais pensé pour les prochaines mises à jour de la donnée.
Mais actuellement, la donnée source n'a pas ce tag.
Et ce tag impliquerait alors de découper chaque tronçon par la limite de la maille (communale). Hors, nos données sources ne sont théoriquement pas découpées aux communes.

Pierre a écrit:

2/faire un explain des requêtes

Oui, je vais essayer ça !

Pierre a écrit:

3/tester les opérateurs &&

je pense que c'est ce que je fais avec cette ligne de mon premier message : "WHERE ST_Intersects(c.geom,l.geom) AND c.geom && l.geom"
Qui doit d'ailleurs être facultative puisque les couches ont des index spatiaux, mais je me dis que ça coute rient de l'ajouter !
Par contre, je devrais peut-être le placer avant le ST_Intersects pour aller plus vite ?

ChristopheV a écrit:

Il est parfois difficile de faire autrement que de laisser du temps au calcul.

En effet, et quelques heures (jours), ce n'est finalement pas beaucoup au regard du volume de données ! Il faut parfois être patient

A+

Sylvain M.

Pierre · Mon 13 November 2017 14:17

Aloha

Pour le 1/ nous avons nous même un réseau qui porte ce type de tag. Le trigger fait une intersection si besoin pour affecter le code de l'entité contenant majoritairement l'objet. il est à noter que sur l'ensemble des objets, plus de 95% sont dans une entité.

Pour le 3/, oui, le mettre avant. Voire l'inclure dans une requête "with".

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 02 November 2017 12:29

[PostGis] Optimiser une requête d'analyse statistiques par mailles

Code:

#2 Thu 02 November 2017 12:57

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#3 Thu 02 November 2017 15:54

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#4 Thu 02 November 2017 16:19

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

Code:

#5 Fri 03 November 2017 14:30

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

Code:

Code:

#6 Fri 03 November 2017 14:38

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#7 Thu 09 November 2017 09:34

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#8 Fri 10 November 2017 10:41

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#9 Sat 11 November 2017 08:57

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#10 Sat 11 November 2017 20:34

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#11 Mon 13 November 2017 09:48

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#12 Mon 13 November 2017 12:23

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

#13 Mon 13 November 2017 14:17

Re: [PostGis] Optimiser une requête d'analyse statistiques par mailles

Pied de page des forums