Accélérer rendu carte observations fiche Espèce

[jpm-cbna]

La carte des observations de la fiche Espèce peut mettre plus de 10s à afficher les mailles des observations sur les bases de données de plusieurs millions d'observations.

Pour accélérer cette affichage, il est possible de créer une vue matérialisée qui contiendra une pré-agrégation des données par maille.

Voici la proposition de vue matérialisée:

CREATE MATERIALIZED VIEW atlas.vm_observations_meshes_agg AS 
    SELECT obs.cd_ref,
        obs.id_maille AS id_mesh,
        obs.annee AS "year",
        COUNT(obs.id_observation) AS nbr
    FROM atlas.vm_observations_mailles AS obs
    GROUP BY obs.cd_ref, obs.id_maille, obs.annee 
    ORDER BY obs.cd_ref, obs.annee
WITH DATA;

-- View indexes:
CREATE INDEX idx_voma_annee ON atlas.vm_observations_meshes_agg USING btree ("year");
CREATE INDEX idx_voma_id_maille_cd_ref ON atlas.vm_observations_meshes_agg USING btree (id_mesh, cd_ref);

GRANT SELECT ON TABLE atlas.vm_observations_meshes_agg TO geonatatlas;

Sur une base de données de 27,5 millions d'observations, cette vue occupe 656Mo et ses index 272Mo. Elle permet de faire passer le temps d'affichage des mailles sur la carte pour une fiche espèce comprenant 490 000 observations de 10s à 1s.

[jpm-cbna]

Plusieurs questions concernant les modifications du code :

• doit on continuer comme dans le code actuel à utiliser l'ORM SQLAlchemy ou plus simplement comme les autres requêtes de ce fichier, directement du SQL ?
• doit on créer la vue matérialisée avec un nom et des attributs en anglais ?
• peut on se contenter d'insérer le code de création de la VM sans tenir compte des mises à jours de l'Atlas ? Si on part du principe que la base de l'Atlas est entièrement générée depuis la base GN, il me semble plus simple de reconstruire toute la base Atlas au lieu de maintenir une script de mise à jour complexe ...

[TheoLechemia]

Salut, Pour SQLAlchemy / SQL : fait comme tu te sens le plus à l'aise. L'atlas meriterait une grosse refonte et une uniformisation en SQLAchemy, mais en l'état on peut pas obliger les gens à faire du SQLAlchemy. Par contre bien mettre les modèles en cohérence. Pour les noms de champs, tout est en français donc je laisserais en français 3eme question : oui !

[camillemonchicourt]

Pour le troisième point pourquoi pas, mais cela nécessite d'éprouver, de nettoyer, de documenter et de bien clarifier tout le nouveau processus de mise à jour de GeoNature-atlas si c'est celui qui est retenu.

[jpm-cbna]

Par contre bien mettre les modèles en cohérence.

Si j'utilise directement du code SQL ou si je créé un nouveau modèle SQLAlchemy, cela veut dire que le modèle SQLAlchemy de la VM Observations n'est plus utile car inutilisé ailleurs. Est ce que tu veux que je le supprime ?

Si, l'objectif à terme c'est de maintenir l'utilisation de SQLAlchemy, je peux créer un nouveau modèle si tu pense que cela sera réutilisable à l'avenir ?

[jpm-cbna]

Pour le troisième point pourquoi pas, mais cela nécessite d'éprouver, de nettoyer, de documenter et de bien clarifier tout le nouveau processus de mise à jour de GeoNature-atlas si c'est celui qui est retenu.

Je vais proposer un script d'update en attendant. Mais, je pense qu'à l'avenir il serait plus judicieux de n'avoir qu'un script d'installation pour la base de l'Atlas. C'est plus simple à tous les niveaux.

[camillemonchicourt]

Il faut prendre en compte que l'Atlas fonctionne aussi avec un mode où on affiche les observations précises (voir Biodiv'Ecrins).

Oui, on pourrait améliorer et simplifier la méthode d'installation et de mise à jour, mais certainement un chantier global.

[jpm-cbna]

Je viens de mettre à jour la PR #519.

Du coup, comme conseillé par @TheoLechemia, j'ai:

• modifié la MV vm_observations_mailles existante et laissé les champs en français. C'est plus simple comme ça.
• utilisé SQLAlchemy
• vérifié que l'affichage des observations précies fonctionnés toujours (comme dans Biodiv'Écrins)
• ajouté un script d'update reprenant la VM et la table modifiée.

[jpm-cbna]

J'ai loupé 2 truc dans ma PR:

1. Ce n'est plus un "count" qu'il fallait utiliser dans la requête mais un "sum". Corrigé dans 9b856ecbf0d8d1b7191952635b04ded0c364f2eb
2. La fonction Postgresql find_all_taxons_childs() utilisée avec SQLAlchemy me renvoyait des nombres décimaux. Corrigé dans c560a91ec04e60273402b8bf3fb5f031b8cdf1f0