Le portail francophone de la géomatique

Salut,

de ce que j'ai pu lire sur la mailing list de dev PostgreSQL,
la memoïsation des fonctions est à l'étude
(ou alors c'est inclu dans la v12, je sais plus).

En attendant,
ce que j'ai tendance à faire dans ces cas là,
c'est encapsuler le traitement répetitif dans une sous requête

SELECT
ST_Distance(way::geography, pta_lonlat::geography) +
          ST_Distance(way::geography, ptb_lonlat::geography) +
          ST_Length(ST_LineSubstring(
                way,
                least(lpa, lpb),
                greatest(lpa, lpb)) AS dst_line
        FROM (
            SELECT st_LineLocatePoint(way, pta_lonlat) lpa,
            st_LineLocatePoint(way, ptb_lonlat) lpb,
            way, pta_lonlat, ptb_lonlat
            FROM(
                SELECT St_GeomFromText(way.path) way, pta.lonlat pta_lonlat, 
                        ptb.lonlat ptb_lonlat
                FROM ways, pois pta, pois ptb
                WHERE ways.id = #{way.id}
                                  AND pta.id = #{poi.id}
                  AND ptb.id = #{@paris.id}
                  ) foo    
            )bar

Ici il y a deux niveau d'encapsulation :
* foo pour parser la géométrie.
* bar pour les linelocatePoint.

Pas certain que cette méthode supporte bien la mise à l'échelle,
j'ai jamais pensé à regarder le plan de telles requêtes.

Un autre moyen serait évidemment d'ajouter
une colonne de type geometry à votre table ways
(et de peut être supprimer la geometrie en format texte).

Sinon remarque subsidiaire, je vois que vous transformez des
geometry en geography, je n'ai jamais vraiment utiliser les geography,
mais il y a peut être des précautions à prendre avant de les caster ?

Dernière modification par tumasgiu (Tue 11 December 2018 20:25)

Merci pour vos conseils

tumasgiu : J'ai tenté de faire ta requête avec encapsulation comparé à la requête sans, je vois pas de gains significatifs.

Yves, tu me conseilles de ne pas utiliser le type geography, pourtant, sauf erreur pour sortir des résultats en km, je n'ai pas vraiment le choix ? Si oui, pourrais tu me détailler comment faire ?

Sinon, j'ai regardé de près les CTE, me suis un peu cassé la tête dessus et pour l'instant je n'arrive pas à construire la requête comme il faut

D'après ce que je comprends, il me faudrait faire une requête de base et avec un système de recursivité alimenter les différents select jusqu'à obtenir le résultat attendu ?

Si tu as un petit moment, pourrais tu sur la base de la requête ci-dessus, me montrer quelle forme ça prendrait ?

Dernière modification par Benji12 (Wed 12 December 2018 17:57)

Le type geography est utilisé pour exprimer des coordonnées sphériques (géodésiques).
Ici un petit topo de quand utiliser ce type :
https://postgis.net/docs/using_postgis_ … VSGeometry

Dernière modification par tumasgiu (Thu 13 December 2018 14:35)

Vous ne vous trompez pas dans votre cas précis.
Quand les arguments de st_distance sont de type geometry,
l'unité spatiale de la valeur de retour est celle définie
par le SCR (système de coordonnées de référence) des arguments,
ici votre SCR utilise des degrés, mais d'autres utilisent le mètre.

C'est la raison pour laquelle j'ai posté le lien précédent :
tout dépend de votre cas d'utilisation, et de la précision
à laquelle vous souhaitez arriver dans vos calculs de distance.

Si votre application calcule des distances ente Paris et des points
situés dans la région parisienne, vous avez tout intérêt à transformer
vos géométries dans la projection cartographique appropriée,
et travailler en mètre directement.

Si, en revanche vous travaillez avec des données du monde entier,
vous devriez effectivement utiliser geography, mais dans ce cas, cela serait
sans doute plus propre et efficient de changer le type
de vos colonnes en geography  une fois pour toute.
Si vous n'avez pas besoin d'une grande précision, et voulez accélérer le calcul
vous pouvez utiliser st_distance avec false comme 3eme argument.

Dernière modification par tumasgiu (Thu 13 December 2018 18:29)