Export Geotrek / Retours et évolutions

[camillemonchicourt]

En plus du schéma de randonnées, le dépôt propose une vue prédéfinie permettant d'exporter des randonnées dans une BDD Geotrek au format du schéma (https://github.com/PnX-SI/schema_randonnee/blob/master/geotrek/v_treks_schema.sql).

1. Itineraires parents

Un étape peut être associée à plusieurs itinérances. J'ai donc du remplacer la jointure simple qui pouvait entrainer des doublons par une sous-requête :

parent AS (
        SELECT string_agg(o.parent_id::text, ',') AS liste, t.topo_object_id
        FROM trekking_orderedtrekchild o
        JOIN selected_t t ON t.topo_object_id = o.child_id
        GROUP BY t.topo_object_id
        )

---

2. UUID

Des UUID ont été ajoutés à tous les objets depuis la version 2.70.0 de Geotrek-admin.
On peut donc désormais les récupérer dans le champs du prévu prévu pour cela :

top.uuid AS uuid

On pourrait aussi ajouter les UUID au niveau de chaque média, mais je pense que cela nécessite de publier une nouvelle version du schéma si on ajoute un champs au sous-objet "medias" ?

---

3. Géométrie

L'idée de simplifier la géométrie des tracés comme c'est le cas actuellement, pour alléger grandement le point des données fournies, est intéressante :

-- réduction de la précision des coordonnées à 5 décimales, simplification de la géométrie pour réduire le nombre de points. Poids de la géométrie divisée par 7.5
    st_simplifypreservetopology(st_snaptogrid(st_transform(top.geom, 4326), 0.000027::double precision), 0.000027::double precision) AS geom

Néanmoins, on a fait en sorte de construire les itinéraires sur des tracés de référence, BD topo la plupart du temps, donc diffuser des données qui ne sont plus bien calées sur ce référentiel me pose question.

En rouge la BD topo de l'IGN, en jaune le tracé originale des itinéraires, en bleu la version simplifiée proposée.

Je préfère retirer la simplification, diffuser la géométrie précise et originale calée sur le référentiel et avoir un fichier généré assez volumineux. Ainsi les partenaires auront exactement les mêmes tracés précis non dégradés.

En gardant le géométrie précise, mon fichier avec nos 156 randos fait 20 Mo, ce qui reste très correct : randos_pne_schema.geojson

Je n'ai pas compris l'usage de st_snaptogrid pour la géométrie des parkings. Cela était pour être cohérent avec la simplification des géométries des itinéraires ?
Donc si on enlève la simplification des itinéraires, on enlève le st_snaptogrid de la géométrie des parkings ?

---

4. OSM

Comme certaines randonnées sont présentes dans OSM mais qu'on n'a pas de champs pour stocker l'identifiant de la relation OSM dans la table des itinéraires ou des événements de Geotrek, j'ai ajouté une sous-requête avec les correspondances en dur :

    osm AS (
        SELECT * FROM (VALUES 
          (904199,12412634),
          (904321,13611265),
          (903486,13611430),
          (904197,12076664))
          AS liste (trek_id,id_osm)
    ),

---

5. Durée

Les durées sont arrondies à 1 décimale, mais du coup quand une durée est définie à "4.25" pour 4h15, le résultat est "4.3". Je ne trouve pas cela pertinent.
A la limite, on pourrait plutôt convertir les durées numériques en heure, transformer "4.25" en "4h15".

6. PDIPR

Dans notre cas, on a un réseau de tronçons indiquant ceux qui sont inscrits au PDIPR.
On pourrait croiser les itinéraires avec le réseau de tronçons pour identifier les itinéraires inscrits au PDIPR, mais cela serait assez spécifique et nécessiterait de vérifier que les tronçons intersectés par l'itinéraire sont tous associés au réseau PDIPR.

7. URL Geotrek-rando-v3

Désormais les accents sont supprimés dans les URL de Geotrek-rando-v3. Il faudrait donc peut-être utiliser aussi l'extension unaccent.
Les ' sont remplacés par des tirets dans les URL de Geotrek-rando-v3, ce qui n'est pas le cas actuellement dans la vue, mais je n'ai pas trouvé comment compléter le replace.

[camillemonchicourt]

Un autre point qui m'a interrogé est comment les infos sur les sols sont récupérées :

    sol AS (
        SELECT string_agg(t_1."name", ',') AS liste, e.topo_object_id
        FROM land_physicaledge e
        JOIN land_physicaltype t_1 ON e.physical_type_id = t_1.id
        JOIN selected_t t ON t.topo_object_id =  e.topo_object_id
        GROUP BY e.topo_object_id
        ),

Je ne vois pas comment la jointure entre les id des randos et les id des types de voie peuvent fonctionner.
Il n'y a pas de lien direct entre les 2 à ma connaissance.

Pour retrouver les types de voie d'un rando selon moi il faut passer par les tronçons qu'ils ont éventuellement en commun, ou alors passer par une intersection géographique.

[camillemonchicourt]

Le champs source est obligatoire dans le schéma, mais il ne l'est pas dans Geotrek-admin.
Du coup j’avais des randos qui ne remontaient pas et je ne remontaient pas pourquoi.

Du coup je me demande si il ne faudrait pas passer la jointure en LEFT JOIN quitte à ce que la validation des données ne passe pas et indique une erreur plutôt que de ne pas remonter les données et que l'on ne s'en rende pas compte ?

LEFT JOIN sources ON t.topo_object_id = sources.trek_id

[IdrissaD]

1. Itinéraires parents
   OK pour moi, comme on n'utilise pas les étapes au PNC on avait manqué ce cas.

2. UUID
   Parfait, merci ! Effectivement, si on modifie le schéma en lui-même il faut publier une nouvelle version. Sachant qu'un itinéraire a un UUID et qu'en retrouvant celui-ci dans la BDD d'origine on retrouve à priori les médias associés, je ne sais pas si c'est nécessaire d'ajouter les UUIDs des médias ? Est-ce que tu vois un cas d'usage ?

3. Géométrie
   Effectivement, je suis ok pour enlever la simplification. Pour la géométrie des parkings c'était bien pour garder une cohérence.

4. OSM
   On n'a pas d'autre solution pour l'instant que faire ça en dur effectivement !

5. Durée
   Bien vu, par contre je suis plutôt pour qu'on garde un chiffre et qu'on ne transforme pas le champ en chaîne de caractères. C'est plus universel et facilite les réutilisations et calculs potentiels. Du coup conserver le champ duration tel quel comme proposé dans la PR me semble la meilleure solution.

6. PDIPR
   Plus simple que l'intersection serait de passer par core_path_aggregation :

WITH paths_trek AS (
    SELECT path_id
     FROM core_pathaggregation cpa
       JOIN selected_t t
         ON t.topo_object_id = cpa.topo_object_id
),
network_id AS (
    SELECT id
      FROM core_network
    WHERE network ILIKE '%PDIPR%'
           OR network ILIKE '%Plan Départemental des Itinéraires de Promenade et de Randonnée%')
SELECT *
 FROM paths_trek pt
   JOIN core_path_networks cpn
     ON NOT (pt.path_id = cpn.path_id
                     AND cpn.network_id IN (SELECT id FROM network_id))

Si cette requête renvoie au moins une ligne, alors c'est qu'au moins un des tronçons n'a pas de réseau associé "PDIPR", si elle ne renvoie rien c'est que tous les tronçons font partie du PDIPR.
Je n'ai pas pu tester cette requête, c'est écrit directement ici, mais dans l'idée ça devrait fonctionner beaucoup plus rapidement et fiablement qu'avec des requêtes spatiales.

7. URL Geotrek-rando V3
   De mémoire la regex que je propose pour la V3 fonctionne. En effet, l'URL est corrigée dynamiquement par Geotrek-rando, à peu près quelle que soit la manière dont l'URL est écrite. D'après mes différents tests, la seule chose importante est de séparer le topo_object_id du nom de la randonnée par un tiret.
   Les URLs suivantes chargent toutes la même page :

• https://rando.ecrins-parcnational.fr/trek/904080-Cabane-du-Pre-d-Antoni (URL obtenue en cliquant sur la randonnée depuis l'interface web)
• https://rando.ecrins-parcnational.fr/trek/904080- Cabane du Pre d Antoni
• https://rando.ecrins-parcnational.fr/trek/904080-CabaneduPredAntoni
• https://rando.ecrins-parcnational.fr/trek/904080-Cabane du Pré d'Antoni
• https://rando.ecrins-parcnational.fr/trek/904080-CabaneduPréd'Antoni
• etc.
  Même les points et les virgules sont automatiquement enlevés, donc on pourrait simplifier la requête en remplaçant replace(btrim(regexp_replace(t."name", '[^\w -]', '', 'g')), ' ', '-') par replace(t."name", ' ', '-'). Cela remplacerait juste les espaces par des tirets, même si ce n'est pas 100% nécessaire c'est tout de même plus robuste de ne pas laisser d'espaces dans les URLs. Tout le reste serait réinterprété par Geotrek-rando lors du chargement de la page.

8. Types de sol
   Tout à fait, comme on ne l'utilise pas c'était resté très théorique et on a dû penser que les land_physicaledge étaient associés à des topologies d'itinéraires. Sur le même principe que le PDIPR on peut passer par core_pathaggregation :

• obtention de tous les id de core_topology WHERE kind = 'LANDEDGE' qui ont un path_id en commun avec notre trek dans core_pathaggregation
• agrégation au sein d'une array de tous les land_physicaltype associés aux land_landedge associés aux core_topology précédemment obtenus

9. Sources
   Oui c'est mieux avec ta proposition, au moins on comprend le problème !

Merci pour toutes les modifications, en ce moment je ne peux rien tester directement sur Geotrek donc mes réponses restent assez théoriques par contre !

[camillemonchicourt]

OK merci pour ces retours.
Oui pour l'URL des randos, seul l'ID de la rando compte et est utilisé.
Même en allant sur https://rando.ecrins-parcnational.fr/trek/904080-Toto, on sera redirigé sur une URL unique de la rando, avec son nom nettoyé de tout accent ou espace. Mais c'est pour essayer de fournir néanmoins l'URL exacte directement si on y arrive.

OK pour les PDIPR et SOL, à creuser, j'ai pas encore bien trouvé la bonne méthode, mais tes pistes me semblent bien coller.

[IdrissaD]

Pour l'URL, est-ce que tu pourrais essayer la requête suivante ?

(SELECT url_rando FROM constants LIMIT 1) || 'trek/' || t.topo_object_id || '-' || regexp_replace(btrim(unaccent(t."name")), '[^\d\w]', '-', 'g')

Elle est beaucoup plus simple :

• unaccent(t."name") : enlève tous les accents (" Cabane du Pré d'Antoni " => " Cabane du Pre d'Antoni ") ;
• btrim(string) : enlève les espaces au début ou à la fin du nom (" Cabane du Pre d'Antoni " => "Cabane du Pre d'Antoni") ;
• regexp_replace(string, '[^\d\w]', '-', 'g') : remplace tous les caractères qui ne sont pas (^) un chiffre (\d) ou une lettre (\w) par un tiret. 'g' signifie que tous les caractères sont remplacés et pas seulement le premier qui correspond à l'expression régulière ("Cabane du Pre d'Antoni" => "Cabane-du-Pre-d-Antoni").

Et on peut toujours laisser un commentaire disant de supprimer la partie unaccent() en cas de problème avec l'extension, ça produira toujours des URLs valides (seulement pour Geotrek-rando V3) même si pas "parfaites".

Ça doit être possible de simplifier aussi la construction de l'URL pour Rando V2, je m'y pencherai.

[IdrissaD]

La majorité des modifications de ce ticket étant intégrées, je le ferme et j'ai reporté la discussion sur les champs type_sol et pdipr_inscription dans un nouveau ticket #20.