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

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Tue 11 December 2018 18:48

Benji12
Participant occasionnel
Date d'inscription: 5 Apr 2018
Messages: 21

Factorisation requete et optimisation

J'ai par exemple cette requête qui doit me retourner une distance entre Paris et un point donné en suivant un parcours :

Code:

        SELECT
          ST_Distance(way.path::geography, pta.lonlat::geography) +
          ST_Distance(way.path::geography, ptb.lonlat::geography) +
          ST_Length(ST_LineSubstring(
            way.path,
            least(ST_LineLocatePoint(way.path, pta.lonlat::geometry), 
                    ST_LineLocatePoint(way.path, ptb.lonlat::geometry)),
            greatest(ST_LineLocatePoint(way.path, pta.lonlat::geometry), 
                         ST_LineLocatePoint(way.path, ptb.lonlat::geometry))
             )::geography)  AS dst_line
        FROM ways way, pois pta, pois ptb
        WHERE way.id = #{way.id}
          AND pta.id = #{poi.id}
          AND ptb.id = #{@paris.id}

- Comment puis je factoriser ce code ?

Pour différentes raisons, je dois passer en argument une Linestring (au lieu de way.path), j'ai donc modifié ma requête de la fonction suivante :

Code:

 ST_Distance(St_GeomFromText('#{way}')::geography, pta.lonlat::geography) +
          ST_Distance(St_GeomFromText('#{way}')::geography, ptb.lonlat::geography) +
          ST_Length(ST_LineSubstring(
            St_GeomFromText('#{way}'),
            least(ST_LineLocatePoint(St_GeomFromText('#{way}'), pta.lonlat::geometry), 
                    ST_LineLocatePoint(St_GeomFromText('#{way}'), ptb.lonlat::geometry)),
            greatest(ST_LineLocatePoint(St_GeomFromText('#{way}'), pta.lonlat::geometry), 
                         ST_LineLocatePoint(St_GeomFromText('#{way}'), ptb.lonlat::geometry)))::geography)  AS dst_line
        FROM ways way, pois pta, pois ptb
        WHERE pta.id = #{poi.id}
          AND ptb.id = #{@paris.id}

En terme de performance, la variable way doit être parsée plusieurs fois (sachant qu'elle fait plusieurs milliers de lignes), je passe donc de 73 ms  à 177 ms (il n'y a pas bcp de données en bdd).
J'ai pensé faire une fonction pour transformer ma linestring en geom , stocker le résultat en variable et appeler cette variable directement dans ma requête, mais apparement, je n'ai aucun gain.

Que me conseilleriez vous de faire ?

Dernière modification par Benji12 (Tue 11 December 2018 18:49)

Hors ligne

 

#2 Tue 11 December 2018 20:24

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

Re: Factorisation requete et optimisation

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

Code:

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)

Hors ligne

 

#3 Wed 12 December 2018 09:02

Yves
Membre du bureau
Lieu: Aix-les-Bains
Date d'inscription: 22 Mar 2006
Messages: 9853
Site web

Re: Factorisation requete et optimisation

Bonjour,

Mes conseils :
* Faire une CTE (voir la doc de postgresql). Cela permet de mettre en mémoire une partie de la requête.
* Ne pas utiliser le type geography qui n'apporte pas de gain de perf (bien au contraire !)

Y.


Yves Jacolin, bénévole de l'association GeoRezo.net, agit au nom et pour le compte de l'association - Partageons ce qui nous départage !!  - GeoRezo vous aide ? Aidez GeoRezo !

Hors ligne

 

#4 Wed 12 December 2018 16:35

Benji12
Participant occasionnel
Date d'inscription: 5 Apr 2018
Messages: 21

Re: Factorisation requete et optimisation

Merci pour vos conseils wink

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 sad

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)

Hors ligne

 

#5 Thu 13 December 2018 14:19

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

Re: Factorisation requete et optimisation

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


Pas de gain significatif, ou pas de gain du tout ?
cette technique ne donne pas toujours des résultats.

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 ?


Les CTE ne sont pas forcement récursives, vous pouvez les voir comme des sous-requêtes déclarées
en préambule

Je pense que Yves pensait à quelque chose comme çà :

On peut aussi écrire la requête de manière à faire disparaitre les CROSS JOIN,
c'est peut être ici que se joue la latence, le plan n'utilise peut être pas tous les indexs
disponibles (à condition que vous en ayez sur chacune des colonnes id de vos tables).

Code:

WITH foo AS (
    SELECT 
    (SELECT st_geomfromtext(way.path) FROM ways WHERE ways.id = #{way.id} LIMIT 1) way,
    (SELECT pois.lonlat FROM pois WHERE id = #{poi.id} LIMIT 1) pta_lonlat,
    (SELECT pois.lonlat FROM pois WHERE id = #{@paris.id} LIMIT 1) ptb_lonlat,
)
SELECT 
st_distance(way, pta_lonlat) +
st_distance(way, ptb_lonlat) +
ST_Length(ST_LineSubstring(way, 
                            least(st_LineLocatePoint(way, pta_lonlat), st_LineLocatePoint(way, ptb_lonlat)),
                            greatest(st_LineLocatePoint(way, pta_lonlat), st_LineLocatePoint(way, ptb_lonlat))))
as dst_line
FROM foo ;

Hors ligne

 

#6 Thu 13 December 2018 14:31

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

Re: Factorisation requete et optimisation

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 ?


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)

Hors ligne

 

#7 Thu 13 December 2018 16:59

Benji12
Participant occasionnel
Date d'inscription: 5 Apr 2018
Messages: 21

Re: Factorisation requete et optimisation

Oui, j'entends bien, mais les geometry sortent des resultats en degrés (si je ne me trompe pas) et moi j'ai besoin de km, donc du geography wink

Hors ligne

 

#8 Thu 13 December 2018 18:23

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

Re: Factorisation requete et optimisation

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)

Hors ligne

 

#9 Thu 13 December 2018 20:54

Benji12
Participant occasionnel
Date d'inscription: 5 Apr 2018
Messages: 21

Re: Factorisation requete et optimisation

Merci pour ces précision, c'est très sympa.

Je travaille à l'échelle de plusieurs régions (un peu moins de la moitié de la France), mais il est possible que le projet évolue vers une surface plus importante. Pour l'instant je stocke en geography (WGS84:4326). J'ai besoin de résultat assez précis et de pouvoir afficher sur mapbox via une librairie de type leaflet ou open layer.

Si j'ai bien compris, vous me conseillez donc de passer mes points et linestrings en geometry et de faire les traitements précisant la projection utilisée ? Qu'elle est celle à utiliser pour avoir des mètres ? La 2154 ? mais si je m'étend hors métropole, je dois tout repenser et modifier sad

Quel est l'impact à travailler en geography ?  C'est vraiment plus lourd... parce qu'à terme, je ne pense pas avoir une énorme bdd (10000 pois, 100 linestrings)

Je peux également stocker les données sous les deux formats et utiliser celle la plus appropriée pour la methode appellée.

Hors ligne

 

Pied de page des forums

Powered by FluxBB