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

Pour sa 21ème année, l’association GeoRezo a toujours besoin de vous !

10€ = 1 mois de frais bancaires ; 15€ = 12 mois de nom de domaine ; 30€ = 1 semaine de location des serveurs …

Faire un don 

Retrouver nos membres bienfaiteurs

#1 Fri 09 November 2018 08:57

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

[PostGIS] : optimisation de requête pour les objets non intersectés

Bonjour,

J'ai deux tables, une contenant des multipolygones et une autre contenant des points. Les deux tables possèdent des index spatiaux.
Je cherche à trouver les points qui ne sont pas contenus dans les polygones.
Voici les requêtes que j'ai testé afin de comparer les temps d'execution:

Code:

select * from "Phil"."import_leve_PointZ" a
left join lateral (
select true t from "Phil"."polygon"b
    where st_intersects(b.geom, a.geom)
    limit 1
) b on true
where b.t is null

Code:

select a.geom
from "Phil"."import_leve_PointZ" a
WHERE not EXISTS (select * from "Phil"."polygon"b 
where st_intersects(b.geom, a.geom)
)

Ma table de points contient 4067720 d'objets et ma table polygone n'en contient qu'un seul à trou ( d'une surface de
16244319.4368 m²)

Les temps de traitement sont d'environ 50 minutes quelque soit la requête.

Est-ce qu'il y aurait un moyen de réduire ce temps de traitement?

D'avance merci
Ted

Hors ligne

 

#2 Fri 09 November 2018 09:23

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3199
Site web

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Bonjour,

Compte tenu que vous n'avez qu'un seul multipolygon je virerais l'index pour être sur que celui qui est pris en compte est celui des points.

Ensuite je raisonnerais en creux. Je chercherais les points qui sont dans le multipolygon puis je ferais une union except entre le résultat et la table point.

Non testé, une idée comme ça.


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

Hors ligne

 

#3 Fri 09 November 2018 09:58

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

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Bonjour,

Deux points:

• Découper le gros polygone en pleins de petits (st_subdivide): le temps de traitement sera réduit de facon... spectaculaire
• Utilisez un anti join pour trouver les geometries sans rapport spatial avec d'autres: les index spatiaux ne marchent bien que pour répondre: "oui, des objets sont en relations spatiales"::

Code:

select p.id 
from points p 
where not exists (
  select null
  from poly_decoupe d
  where st_dwithin(p.geom, d.geom, 0.001) --< precision a regler suivant vos données, ou st_contains
);

Pfff pardon ! c'est votre deuxieme requete ! j'ai lu un peu vite... sad
Découpage donc !

Nicolas

Hors ligne

 

#4 Fri 09 November 2018 10:05

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

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Si les points sont tjs contenus dans les trous du PG, vous pouvez dumper les trous (st_dumprings), indexer cette table, et chercher les points contenus dans ces PG: ca marche souvent tres vite aussi si les trous sont petits (sinon éventuellement les découper)

Nicolas

Hors ligne

 

#5 Fri 09 November 2018 10:08

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3199
Site web

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Bonjour,

Découper le gros polygone en pleins de petits (st_subdivide): le temps de traitement sera réduit de facon... spectaculaire


Pour ce genre de requête en revanche pour des fonction comme st_symdifference() c'est l'inverse.


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

Hors ligne

 

#6 Fri 09 November 2018 10:23

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Bonjour,

merci pour vos éléments de réponse.

Pour St_subdivide, je vais être bloqué car ma version est trop ancienne (PostGIS=2.1.4 GEO 3.4.2) alors qu'il faudrait la version  2.2.0 et 3.5.0.
Et bien évidemment, je n'ai pas la main dessus ;-)

Hors ligne

 

#7 Fri 09 November 2018 10:28

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 Sep 2005
Messages: 3199
Site web

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Bonjour,

Comme l'indique Nicolas, les index ça marche bien pour le OUI. Combien de temps met votre sous requête avec st_within() ?


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

Hors ligne

 

#8 Fri 09 November 2018 10:28

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

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Vous pouvez découper vous meme, avec une grille de découpage (plus long que subdivide, mais ca marche bien)
Pour créer la grille: https://trac.osgeo.org/postgis/wiki/Use … ateFishnet

Nicolas

Hors ligne

 

#9 Fri 09 November 2018 11:39

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

@ ChristopheV, j'ai relancé avec st_within est la requête n'est pas finie. Elle devrait mettre le même temps je pense?

@ Nicolas, j'ai lancé "le filet" et je suis en train de découper pour polygone et ensuite lancer la requête de recherche.

J'attends les résultats et vous tiens au courant dès que possible

Hors ligne

 

#10 Mon 12 November 2018 09:34

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Re bonjour à tous,

Le st_within dans la sous requête me donne un temps de traitement de 51 minutes.

Quant à la requête proposée par Nicolas, j'ai des messages d'erreur :

NOTICE:  gserialized_gist_joinsel: jointype 5 not supported
ERROR:  Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
HINT:  Change argument 2: 'GEOMETRYCOLLECTION(LINESTRING(-1.5977 49.6718641,-1.5977 49.6718465),LINESTRI...'
********** Erreur **********

ERROR: Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
État SQL :XX000
Astuce : Change argument 2: 'GEOMETRYCOLLECTION(LINESTRING(-1.5977 49.6718641,-1.5977 49.6718465),LINESTRI...'

Hors ligne

 

#11 Mon 12 November 2018 10:43

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Pour la requête de Nicolas,
pouvez vous la poster, ainsi que le plan d’exécution ?
On dirait que la découpe génère des géométries
incompatibles avec l'index (!), pour remédier à cela
il faudrait peut être filter au préalable la sortie de votre subdivide maison,
afin de ne retenir que les (multi)polygones.

Pour votre requête initiale, simple remarque :
je ne sais pas si utiliser une requête latérale produira
le plan de requête le plus optimal (même si peut être que dans ce cas-ci,
il sera identique).

Sinon, avec 4 fois moins de points que vous et pour un polygone de surface équivalente,
avec la requête suivante j'arrive à 1min10 :

Code:

SELECT st_dump(st_difference(points, geom)) point FROM (SELECT st_union(point) FROM mes_points) p, (SELECT geom FROM mes_polys LIMIT 1) g;

Il faut ensuite refaire la jointure avec la table des points. (on peut utiliser st_memunion pour être gentil et ne pas consommer trop de mémoire).

EDIT: 2min20 avec une jointure spatiale

EDIT2: 1min30 en joignant sur l'id des points (utilisation d'une des dimensions dispo (M ou Z) pour stocker l'idée du point dans le st_union).

EDIT3: changer st_union avec st_collect n'a pas l'air d'affecter le temps d'exécution.

EDIT4: la dimension M ne peut pas être utilisée pour stocker l'id car elle est effacée par st_difference.

Dernière modification par tumasgiu (Mon 12 November 2018 11:53)

Hors ligne

 

#12 Mon 12 November 2018 13:45

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Merci Tumasgiu,

J'ai filtré les objets en les changeant tous en "single" et je me suis rendu compte qu'il y avait des lignes. Après leurs occultations, la requête ne Nicolas fonctionne parfaitement et son temps de traitement de l'ordre de 3 minutes.

J'ai testé ta requête (post précédent), mais elle me renvoie un résultat vide, est-ce normal?

Ted

Hors ligne

 

#13 Mon 12 November 2018 14:08

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Je ne sais pas, il faudrait voir votre requête.

Hors ligne

 

#14 Mon 12 November 2018 14:15

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

De toutes les manières, la méthode par subdivision est préférable, je pense.

Dernière modification par tumasgiu (Mon 12 November 2018 14:18)

Hors ligne

 

#15 Mon 12 November 2018 14:19

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Ok,
je vais donc rester à la méthode de subdivision qui fonctionne parfaitement.

Encore merci

Hors ligne

 

#16 Mon 12 November 2018 14:24

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Mais je serai curieux de voir la différence si vous voulez bien poster la requête. smile

Hors ligne

 

#17 Mon 12 November 2018 14:51

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Si je comprends vous voulez la requête de Nicolas?
La voici, c'est celle que j'avais posté également au tout début de mon post.

Code:

select p.id, p.geom 
from "Phil"."import_leve_PointZ" p 
where not exists (
  select null
  from "Phil"."polygondecoup_singlev2" d
  where st_intersects(p.geom, d.geom) 
)

Est-ce cela?

Hors ligne

 

#18 Mon 12 November 2018 14:55

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Ah désolé, je voulais votre test avec le st_difference.

Hors ligne

 

#19 Mon 12 November 2018 15:12

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

En testant séparément, je me suis aperçu que dans la requête :

Code:

SELECT st_dump(st_difference(geom, g.geom)) point FROM 
(SELECT st_union(geom) FROM "Phil"."import_leve_PointZ") p, (SELECT geom FROM "Phil"."polygon" LIMIT 1) g

la partie relative au st_union me renvoyait un résultat vide!

Hors ligne

 

#20 Mon 12 November 2018 15:48

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Oui, en fait, votre requête calcule la différence du polygone avec lui même,
puisque geom et g.geom  font tout deux réference a la colonne geom de g.
Une colonne issue du calcul d'une fonction porte le nom de la fonction si elle n'a pas d'alias,
ici, le nom de la colonne de la table p serait "st_union".
il faudrait donner un alias au st_union, genre points.

Une requête qui devrait marcher, (avec la jointure):

Code:

SELECT z.* 
FROM "Phil"."import_leve_PointZ" z
JOIN ( SELECT (st_dump(st_difference(points, g.geom))).geom point FROM 
     (SELECT st_union(geom) points FROM "Phil"."import_leve_PointZ") p, (SELECT geom FROM "Phil"."polygon" LIMIT 1) g) foo
ON ST_equals(foo.geom, z.geom);

Dernière modification par tumasgiu (Mon 12 November 2018 16:04)

Hors ligne

 

#21 Tue 13 November 2018 09:24

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Tumasgiu, je viens de stopper la requête, car ça faisait 17h30 qu'elle tournait

Hors ligne

 

#22 Tue 13 November 2018 11:45

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Oui effectivement, sage décision.

Je vous embête mais si vous trouvez le temps,
est ce que vous pourriez poster le plan ?
vous pouvez l'obtenir en ajoutant EXPLAIN au début de la requête.

Merci.

Hors ligne

 

#23 Tue 13 November 2018 11:51

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

En fichier joint le résultat du explain.

En espérant que cela vous soit utile


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

Hors ligne

 

#24 Tue 13 November 2018 17:10

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Merci.
Bon Postgres a visiblement décidé de ne pas utiliser l'index
présent sur votre table de point, pour faire la jointure.
L'ajout de l'opérateur && (cf. https://georezo.net/forum/viewtopic.php?pid=300647)
permet de "forcer" son utilisation.

Dernière modification par tumasgiu (Tue 13 November 2018 17:29)

Hors ligne

 

#25 Wed 14 November 2018 13:32

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Merci pour le "&&", sur ta requête elle se termine en 20 minutes (contrairement à la dernière fois où j'avais du la stopper).

Hors ligne

 

#26 Wed 14 November 2018 14:23

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1161

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Merci.

Dernière précisions ensuite je ne vous embête plus.
Pouvez vous me dire combien de temps La requête avec la grille avait mis à terminer ?

Et est ce que vous pourriez me donner la config de la machine
sur laquelle tourne postgresql ainsi que l'OS et éventuellement les modifs
que vous auriez apporté au fichier de configuration ?

Hors ligne

 

#27 Wed 14 November 2018 14:47

Ted
Participant assidu
Date d'inscription: 16 Jan 2007
Messages: 181

Re: [PostGIS] : optimisation de requête pour les objets non intersectés

Avec la grille 3 minutes
là j'obtiens un résultat en 20 minutes, mais le résultat n'est pas bon, il faut que je vois pourquoi. Normalement, je devrais avoir qu'une seule "anomalie", présentement j'en ai 4067733!

La machine est mon PC de travail, RAm 12Go, I7-3773 CPU 3.4Ghz.
Je n'ai apporté aucune modification sur la config.

Hors ligne

 

Pied de page des forums

Powered by FluxBB