Forums PostgreSQL.fr

Jean-Marie

Membre

Optimisation du calcul du nombre de points dans une surface projetée

Bonjour

je cherche à optimiser une requête calculant le nombre de points dans une surface projetée ;
les coordonnées des points dans la base de données sont stockées en degré
mais les limites de la surface sont spécifiées en projeté (exemple LambertIIe)

une première solution est de convertir en projeté chaque point et de tester s'il appartient à la surface en projeté (délimitée par 4 points) :

select count(*) from table where  st_transform
(coord,27572) && st_setsrid('BOX(-111740.67231994 452481.05159311, 1763272.204062 1722480.3657935)'::box2d,27572)

cette solution donne un résultat juste mais le temps de traitement augmente très vite avec le nombre de points

pour réduire cette durée j'ai pensé convertir en degré le rectangle de manière à éviter la conversion sur les points ; je constate qu'effectivement la requête est plus rapide mais le résultat est faux

select count(*) from table where coord && ST_
Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-111740.67231994, 452481.05159311), ST_Point(1763272.204062, 1722480.3657935)),27572), 4326)

j'en déduis que la transformation des points projetés en points en degrés ne délimite pas la même surface

est-ce vraiment impossible de déterminer une surface en degré qui soit équivalente à la surface projetée ?
si c'est vraiment le cas, y-a-t'il un autre moyen pour optimiser le temps de traitement ?

merci d'avance

Jean-Marie

Marc Cousin

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

Stocker les données projetées dans la bonne projection dès le départ ? (dans une colonne supplémentaire de la même table par exemple, voire même simplement créer un index sur st_transform(coord,27572)).

Ça devrait rendre plus rapide la sélection (mais évidemment ralentir les insertions).

---

Marc.

Jean-Marie

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

Ce n'est pas envisageable car nous pouvons être amené à utiliser différentes projections selon la région et le pays concerné

Jean-Marie

Marc Cousin

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

Dans ce cas, vous ne pouvez pas avoir de solution magique (en tout cas je n'en vois pas): il va bien falloir que PostgreSQL projette les points que vous avez (en degrés, vous voulez dire en latitude/longitude je présume ?), afin d'ensuite pouvoir déterminer si ils sont bien dans votre surface.

---

Marc.

vincentp

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

Bonjour,
Vous pouvez vérifier avec EXPLAIN si il y a bien des indexes géographiques qui sont utilisés.
À priori non, puisque même si vous en avez défini un sur la géométrie des points, le st_transform empêche de l'utiliser.
La solution de Marc de créer un index sur le st_transform devrait résoudre la situation.

Quant au résultat faux, comment l'évaluez vous ?

Jean-Marie

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

Bonjour

effectivement il n'y a pas d'index utilisé ;
mais créer un index sur le st_transform ne me parait pas envisageable car nous sommes amenés à utiliser différentes projections

l'évaluation du résultat se fait en comparant le nombre de points obtenu par la requête à postgres avec l'affichage de ces points réalisé au moyen d'une requête WMS à mapserver

dans le cas de la requête 'longue" où chaque point est converti en degré, il y a bien correspondance

pour revenir à la requête plus rapide mais fausse, je remarque que la conversion en degré des limites projetés
renvoie 5 points (voir ci-dessous la description du polygone) ; il serait intéressant d'obtenir un polygone comportant davantage de points afin d'obtenir une meilleure précision) ; mais je ne sais pas comment faire

SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-111740.67231994, 452481.05159311), ST_Point(1763272.204062, 1722480.3657935)),27572), 4326)) As wgs_geom;

wgs_geom                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
POLYGON((-4.86421028855627 30.9545265233267,-6.2658910764966 42.1561991488799,16.3040533297558 41.5763321847958,14.0513693532454 30.482942611268,-4.86421028855627 30.9545265233267))

vincentp

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

Bonjour

effectivement il n'y a pas d'index utilisé ;
mais créer un index sur le st_transform ne me parait pas envisageable car nous sommes amenés à utiliser différentes projections

«différentes» étant combien ? Sont elles nombreuses ? Sont elles prédéfinies ? Quelles sont les zones sur lesquelles vous travaillez, leurs étendues, et leur éventuelle indépendance ? Quelle est la précision requise des calculs d'intersection ? Quel est le ratio lecture/écriture ?

Pour rendre le travail plus simple et plus performant on segmente souvent un domaine en sous domaines, avec des projections spécifiques à chaque fois. Les imports/exports de données vont éventuellement faire automatiquement l'enregistrement dans le bon domaine (= la bonne table = la bonne projection).

Une autre approche dans le cas où la compartimentation des données n'est pas possible, est d'effectivement utiliser seulement des coordonnées géodésiques (sphériques) en latitude / longitude. PostGIS permet de travailler sur de telles géométries avec le type «geography». Cependant ce type de données n'est pas supporté par toutes les fonctions de PostGIS, les calculs géométriques sont plus lents et certains calculs sont faits par projection locale et reconversion sphérique. Tout cela est documenté dans le manuel :

http://postgis.org/documentation/manual … yFunctions

http://postgis.org/documentation/manual … _Geography

pour revenir à la requête plus rapide mais fausse, je remarque que la conversion en degré des limites projetés
renvoie 5 points (voir ci-dessous la description du polygone) ; il serait intéressant d'obtenir un polygone comportant davantage de points afin d'obtenir une meilleure précision) ; mais je ne sais pas comment faire

SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-111740.67231994, 452481.05159311), ST_Point(1763272.204062, 1722480.3657935)),27572), 4326)) As wgs_geom;

wgs_geom                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
POLYGON((-4.86421028855627 30.9545265233267,-6.2658910764966 42.1561991488799,16.3040533297558 41.5763321847958,14.0513693532454 30.482942611268,-4.86421028855627 30.9545265233267))

La conversion ne renvoie que 4 points et pas 5 : le dernier point d'un ring de polygone doit être le même que le premier pour le fermer. C'est logique : vous créez un rectangle avec makebox2d, et vous faites une conversion, les 4 coordonnées sont converties.

Le calcul d'intersection sur des géométries (type geometry) définies en EPSG 4326 (lat/lon) se fait dans un espace euclidien (projeté, 2D). Il est donc faux par définition, et d'autant plus faux que les polygones sont grands.

C'est sûrement ce qui se passe ici.

Il y a plusieurs pistes pour avancer, mais il manque des informations pour dire quelle est la meilleure. Globalement :
* on peut utiliser geography, qui fera un meilleur travail sur les données en lat/lon
* on peut compartimenter les données en zones projetées différemment, et indexer
* on peut réduire la taille des objets et augmenter leur nombre, ou les subdiviser (avec st_segmentize par exemple sur le makebox2D)
* on peut définir des indexes sur les transformations généralement utilisées

Mes 2€c,
Vincent

Jean-Marie

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

merci pour précisions que je vais étudier

les projections ne sont pas pré-définies
la surface peut varier d'une région à la planète entière

dans le cas de la projection sphérique Mercator il est je semble à priori possible
de travailler directement sur l'expression en degré des limites de la surface projetée
ce qui correspond à une requête ayant la syntaxe suivante :

select count(*) from table where coord && ST_
Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin), ST_Point(xmax, ymax)),900913), 4326) and date ...

ce qui nous permet d'avoir un traitement plus rapide et d'autant plus intéressant que plus la surface
est grande plus il y a de points

Jean-Marie

vincentp

Membre

Re : Optimisation du calcul du nombre de points dans une surface projetée

merci pour précisions que je vais étudier

les projections ne sont pas pré-définies
la surface peut varier d'une région à la planète entière

dans le cas de la projection sphérique Mercator il est je semble à priori possible
de travailler directement sur l'expression en degré des limites de la surface projetée
ce qui correspond à une requête ayant la syntaxe suivante :

select count(*) from table where coord && ST_
Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin), ST_Point(xmax, ymax)),900913), 4326) and date ...

ce qui nous permet d'avoir un traitement plus rapide et d'autant plus intéressant que plus la surface
est grande plus il y a de points

Jean-Marie

Heu non, c'est exactement la même chose, faire des calculs de géométrie euclidienne (intersection ici), donc en 2D, avec des coordonnées sphériques (lat/lon, 4326) n'a pas de sens, le résultat sera faux.