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 !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 jeu. 02 novembre 2017 12:29

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 175

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

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.

Hors ligne

 

#2 jeu. 02 novembre 2017 12:57

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 175

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

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

Dernière modification par Sylvain M. (jeu. 02 novembre 2017 13:00)


Sylvain M.

Hors ligne

 

#3 jeu. 02 novembre 2017 15:54

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 175

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

É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 !


Sylvain M.

Hors ligne

 

#4 jeu. 02 novembre 2017 16:19

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 502

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

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 (jeu. 02 novembre 2017 16:21)

Hors ligne

 

#5 ven. 03 novembre 2017 14:30

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 175

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

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.


Sylvain M.

Hors ligne

 

#6 ven. 03 novembre 2017 14:38

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 502

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

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.

Hors ligne

 

#7 jeu. 09 novembre 2017 09:34

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2406
Site web

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

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.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#8 ven. 10 novembre 2017 10:41

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 175

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

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. (ven. 10 novembre 2017 10:42)


Sylvain M.

Hors ligne

 

#9 sam. 11 novembre 2017 08:57

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2406
Site web

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

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


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#10 sam. 11 novembre 2017 20:34

Pierre
DesCartesPourUnMondeMeilleur
Date d'inscription: 22 sept. 2005
Messages: 1558

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

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.


art X I. Déclaration des Droits de l’Homme et du Citoyen 1789
La libre communication des pensées et des opinions est un des droits les plus précieux de l’Homme : tout Citoyen peut donc parler, écrire, imprimer librement, sauf à répondre de l’abus de cette liberté, dans les cas déterminés par la Loi.

Hors ligne

 

#11 lun. 13 novembre 2017 09:48

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2406
Site web

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

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 (lun. 13 novembre 2017 09:50)


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#12 lun. 13 novembre 2017 12:23

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 175

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

Merci encore pour vos avis/conseils ! smile

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 ! wink Il faut parfois être patient smile

A+

Sylvain M.


Sylvain M.

Hors ligne

 

#13 lun. 13 novembre 2017 14:17

Pierre
DesCartesPourUnMondeMeilleur
Date d'inscription: 22 sept. 2005
Messages: 1558

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

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


art X I. Déclaration des Droits de l’Homme et du Citoyen 1789
La libre communication des pensées et des opinions est un des droits les plus précieux de l’Homme : tout Citoyen peut donc parler, écrire, imprimer librement, sauf à répondre de l’abus de cette liberté, dans les cas déterminés par la Loi.

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |