fonctionnalites:geonature:synthese-amelioration-performance-test [CBNA SINP]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Synthèse - Tests d'amélioration des performances ======

===== Objectif =====
Trouver des solutions pour supprimer la limite des 100 000 données affichables et exportables.


===== Principe général =====

Afin d'améliorer les performances du module Synthèse de GeoNature, nous pouvons remplacer le web service geojson actuel, servant à la fois au rendu sur la carte et à l'affichage des données en liste, par plusieurs web services spécialisés.
Ces web services devront retourner des données relativement constantes quel que soit le nombre d'observations à afficher, résultant de la recherche effectuée dans la Synthèse.

Nous pouvons distinguer 3 principaux types de web services dans le module Synthèse qui fournissent des données :
  * au tableau d'informations paginée 
  * à l'export des données
  * à la carte
Nous proposerons des solutions pour chacun d'entre eux.

==== Solutions pour le tableau d'information paginée ====
Dans le cas des observations présentées sous forme de tableau paginé, la solution la plus adéquate consiste à **créer un web service paginé côté serveur**. L'avantage de ce type de web service est que le nombre d'informations retournées est constant quels que soient les résultats de la recherche. Son utilisation dans certains modules de GeoNature a montré son efficacité et sa facilité d'utilisation avec le composant Datatable d'Angular Material.

Côté serveur, il sera nécessaire de s'assurer que les requêtes exécutées en base de données soient toujours les plus performantes possibles. Si nous souhaitons garder un tri similaire des observations, il faudra rassembler les observations sélectionnées via la carte en début de liste.

Côté navigateur client, ce mécanisme permet de s'assurer que les performances seront toujours les mêmes. Il faudra toutefois peut être retravailler le mécanisme d'interaction entre les observations de la carte et du tableau. Il semble toutefois possible de s'appuyer sur la valeur du champ ''id_synthese'' pour le maintenir.

==== Solutions pour l'export des données ====
Le **travail effectué en 2023 sur le module Export** a démontré qu'il était possible d'exporter un grand volume de données sans surcharger la mémoire et l'espace disque du serveur. Mais pour cela, il est nécessaire de générer l'export en arrière plan et alerter l'utilisateur lorsque l'export est prêt à être téléchargé.

Pour cela, les nouveaux mécanismes tels que l'**utilisation de tâches Celery** et les **notifications** ajoutés dans les dernières version de GeoNature nous permettrons de mettre en place un export performant avec des limites très largement augmentées.

Toutefois, la mise en place de cette solution nécessitera de revenir sur la branche principale de GeoNature dans laquelle les tâches Celery et les notifications ont été implémentées.

==== Solutions pour la carte ====
L'amélioration des performances d'affichage sur la carte et le point qui nécessite le plus d'expérimentation car aucune solution satisfaisante n'a encore été expérimentée dans GeoNature. Nous réaliserons donc la majorité des nos tests sur ce sujet particulier.

Toutefois, le web service GeoJson actuel a vu ses performances largement améliorées lors de travaux effectués en 2022 avec le regroupement des observations par géométries identiques. Une optimisation de la création du GeoJson a également été effectuée lors des travaux visant à permettre le regroupement et l'affichage des observations par mailles sur la carte de la Synthese.
 
Afin de poursuivre l'amélioration de l'affichage d'une grand nombre d'observations sur la carte, nous envisageons donc d'expérimenter les éléments suivant :
  - Utiliser des tuiles vecteurs ou geojson permettant de paralléliser les requêtes en base de données
  - Mettre en place un web service spécifique à la carto geojson/mvt filtrée sur la bbox de la carte actuellement visualisée
  - Utiliser un affichage différent en fonction du zoom : petit zoom avec mailles, moyen avec cluster/polygones, grand avec points précis.
  - Tester la différence entre l'utilisation de la géométrie avec un SRID 2154 et 4326 pour les intersections
  - Comparer la rapidité des intersections de géométrie à l'aide de : ''st_intersects()'' ou de ''&&''
  - Comparer différents types d'index (GIST, BRIN, SP-GIST) sur le champ géométrie de la table Synthèse
  - Comparer l'utilisation d'une intersection spatiale vis à vis de l'utilisation d'une table relationnel (''cor_area_synthese'')
  - Tenter d'améliorer les performances de ''cor_area_synthese'' :
    - Ajouter une colonne ''area_type_code'' contenant le code du type de zone géographique correspondant à l'''id_area''.
    - Mettre en place [[https://docs.postgresql.fr/15/ddl-partitioning.html| un partitionnement de la table]] basé sur ''area_type_code''
    - Créer une vue matérialisée mettant en cache les données agrégées par maille pour l'affichage par défaut (nombre d'observations par maille sans filtre)
  - Tester l'utilisation d'une table des géométries de ''l_areas'' subdivisées (''st_subdivide()'') pour l'utiliser lors des intersections (''st_intersects()'').
  - Tester l'utilisation d'une vue matérialisée correctement indexée et agrégeant l'ensemble des informations nécessaires aux requêtes de la Synthèse

===== Tests d'amélioration de l'affichage sur la carte =====
==== Principes d'amélioration ====

Pour l'amélioration de l'affichage cartographique, nous envisageons de :
  * paralléliser l'accès aux données
  * diminuer le volume de données restitué par chaque appel de web service
  * agréger les données à petite échelle

Afin de paralléliser l'accès aux données, il est nécessaire de remplacer le "mono" webservice REST GeoJson actuel par un web service de type Tile Map Service (TMS) fournissant des [[https://www.crunchydata.com/blog/dynamic-vector-tiles-from-postgis| données vectorisés sous forme de tuiles]].

Le format de web service TMS, dont le standard est décrit par osgeo.org dans le doc [[https://wiki.osgeo.org/wiki/Tile_Map_Service_Specification| Tile Map Service Specification]], est utilisé par Google Map, mais également [[https://wiki.openstreetmap.org/wiki/Slippy_map_tilenames| par OpenSteetMap dans une version simplifiée]].\\
Ce type de web service doit pouvoir être appelé plusieurs fois en parallèle si l'on souhaite accélérer le rendu sur une carte web. Pour cela l'utilisation d'URLs HTTPS supportant HTTP2, avec des sous-domaines différents (a., b., c., etc) doit permettre aux navigateurs de réaliser un maximum de requêtes simultanées.\\
La possibilité de générer parallèlement plusieurs tuiles vecteurs, de dimension réduite et dont le contenu varie en fonction du niveau de zoom devrait accélérer le rendu.

L'utilisation de tuiles vecteurs permet de maintenir l'interaction avec les objets retournés comme c'est le cas actuellement avec le GeoJson.
Il existe deux format de tuiles vecteurs envisageable : Mapbox Vector Tile (MVT) ou GeoJson. Mais la quasi totalité des exemples et articles sur le web concernent les tuiles MVT. Les tuiles geojson sont-elles moins intéressantes ?

Pour les petits niveaux de zoom, nous utiliserons [[https://www.esri.com/arcgis-blog/products/data-management/mapping/strategies-to-effectively-display-large-amounts-of-data-in-web-apps/|une technique d’agrégation permettant d'afficher une grande quantité d'information]]. Nous essaierons de retourner des données agrégées par maille avec une coloration des mailles en fonction du nombre d'observations contenues. Cela permettra de garantir la lisibilité des données affichées.

L'utilisation de mailles est un facteur important car c'est un objet géographique simple qui ne contient que 5 points. Nous disposons également de mailles de différentes tailles 1, 5 et 10 km par défaut dans GeoNature, ce qui permet de sélectionner la taille la mieux adaptée à son territoire et à la quantité de données hébergée. Enfin, l'intersection de données avec des mailles en base de données peut se faire à l'aide de la fonction Postgis ''ST_Intersects()'' mais également à l'aide de l'opérateur bien plus performant ''&&'' qui, pour les géométries, signifie "l'étendue recouvre ou touche".

Enfin, afin de réduire le volume de données à renvoyer par tuile, il est possible de simplifier la géométrie des objets renvoyés. Dans le cadre du module Synthèse de GeoNature, cette possibilité est envisageable mais n'aura pas forcément beaucoup d'intérêt. En effet, les mailles agrégeant les données à petite échelle ne peuvent pas être plus simplifiées. Cette technique peut éventuellement être utilisée pour simplifier les géométries d'observations de type polygone renvoyées à grande échelle. Mais dans ce cas-là, nous cherchons souvent à garder le maximum de précision.\\
Par contre, il peut être intéressant de simplifier le nombre de décimales des coordonnées des géométries des observations. Avec 5 chiffres après la virgule, une coordonnée est précise au mètre. Il semble donc intéressant de garder 5 (ou 6) chiffres maximum après la virgule.

La parallélisation de l'accès aux données, leur vectorisation et leur agrégation par mailles devrait rendre possible l'affichage d'une grande quantité de données à petite échelle.

==== Tuiles GeoJson ====

Il serait intéressant de maintenir le format GeoJson en proposant des tuiles vecteurs au format "geojson". Les principaux avantages sont :
  * bon support du GeoJson par Leaflet
  * existence d'un plugin [[https://github.com/glenrobertson/leaflet-tilelayer-geojson| Leaflet Tilelayer GeoJson]]
  * le module Synthèse de GeoNature exploite déjà ce format

Cependant, les défauts des tuiles GeoJson comparés aux tuiles MVT sont :
  * le poids des tuiles GeoJson (en texte) générées comparé à celui des tuiles PBF qui utilisent un format binaire est plus important.
  * la quasi absence de ressource sur le web concernant la création de tuiles GeoJson.
  * le support des tuiles GeoJson n'est pas offert nativement par les principaux framework carto web : [[https://github.com/mapbox/mapbox-gl-js/issues/7912| Maplibre]] (pas de support), LeafLet (via le plugin Leaflet Tilelayer GeoJson), OpenLayers (?). 

Au niveau pratique, concernant la création de tuiles GeoJson, Postgis semble offrir la possibilité de découper des géométries en fonction d'un polygone donnée ("clip") à l'aide de la fonction ''[[https://postgis.net/docs/ST_Intersection.html| ST_Intersection()]]''.
Il semble également possible d'utiliser [[https://github.com/5chdn/geojson-map-tiler/blob/master/tiler/geojson.rb#L104C164-L104C167| l'utilitaire ogr2ogr]] qui permet de générer du GeoJson en [[https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-clipsrc|redécoupant les géométrie données en fonction du contour d'une bbox]].

Exemples de code pour créer des tuiles GeoJson :
  * [[https://github.com/tobinbradley/dirt-simple-postgis-http-api/blob/master/routes/geojson.js|Dirt-simple-postgis-http-api - GeoJson]] => en réalité, cela ne crée pas un geojson dont les géométries sont restreintes à la tuile mais renvoie toutes les géométrie intersectant la bbox de la tuile demandée...

==== Tuiles Mapbox Vector Tile ====
Le principal format de tuiles vecteurs est le [[https://docs.mapbox.com/data/tilesets/guides/vector-tiles-standards/|Mapbox Vector Tiles]] (MVT). Les URLs permettant de récupérer ces tuiles se terminent souvent par l'extension ".mvt" mais l’extension ".pbf" est également utilisée car comme [[https://docs.mapbox.com/data/tilesets/guides/vector-tiles-standards/#format| expliqué dans le guide du standard MVT]], les tuiles MVT sont encodées en s'appuyant sur [[https://github.com/google/protobuf| Google Protobufs]] (PBF).

Par contre, le format de fichier PBF d'OpenStreetMap utilise également Google Protobufs mais n'a rien à voir au niveau de son implémentation avec le format utilisé pour les tuiles MVT.

Concernant la création de tuile MVT, Postgis supporte très bien ce format à l'aide des fonctions ''[[https://postgis.net/docs/ST_TileEnvelope.html| ST_TileEnvelope()]]'', ''[[https://postgis.net/docs/ST_AsMVTGeom.html|ST_AsMVTGeom()]]'' et ''[[https://postgis.net/docs/ST_AsMVT.htm|ST_AsMVT()]]''.

==== Simplification des géométries à l'aide de Postgis ====
{{ https://prod-api.symphony.is/assets/07-screen-shot-2021-11-14-at-093219-2.webp?450}}

Comme indiqué précédemment, il peut être utile de simplifier les géométries retournées en réduisant le nombre de chiffres après la virgule des coordonnées en utilisant la fonction Postgis ''[[https://postgis.net/docs/ST_SnapToGrid.html| ST_SnapToGrid()]]''. Pour garder une précision métrique, 5 chiffres après la virgule suffisent : ''ST_SnapToGrid(geom, 0.00001)''.

S'il s'avère nécessaire de simplifier des géométries complexes (Communes, Départements, zones de protection...), il faut utiliser des fonctions qui préservent la topologie des géométries pour éviter de créer des géométries invalides. C'est le cas de la fonctions Postgis ''[[https://postgis.net/docs/ST_SimplifyPreserveTopology.html| ST_SimplyPreserveTopology]]''.

Le web service retournant ces géométrie doit pouvoir [[https://symphony.is/about-us/blog/boosting-postgis-performance| activer et augmenter la simplification des géométries en fonction du niveau de zoom]]. L'utilisation de la formule suivante dans les requêtes est très intéressante car cela permet de simplifier automatiquement les géométries en fonction du niveau de zoom de façon à ce que les écarts apparaissant entre 2 géométries contiguës ne soient pas visibles : ''ST_SimplyPreserveTopology(geom, 0.7 / (2 ^ <zoom-level>))''.

==== Framework carto web et tuiles vecteurs ====

Actuellement, nous utilisons **Leaflet** comme framework de cartographie web. Il est simple, peu verbeux et fournit, jusqu'à présent, toutes les fonctionnalités cartographiques dont GeoNature a besoin. Malheureusement, il ne supporte pas nativement les tuiles vecteurs. Il est possible d'utiliser un plugin pour cela mais il en existe de nombreux qui ne sont pas tous bien maintenus. Nous listerons les plugins Leaflet existants, permettant l'utilisation de tuiles vecteurs, et nous déterminerons s'ils sont toujours actifs et évaluerons s'ils sont bien maintenus.

Enfin, nous testerons la solution dont le rendu des tuiles vecteur est le plus performant dans un navigateur web. Même si cela impliquerait une modification importante du code de GeoNature, les possibilités offertes par ces récents frameworks supportant de nouvelles techniques d'affichage web pourraient s'avérer valoir l'investissement. Nous testerons l'utilisation de tuiles vecteur avec le plus intéressant et évaluerons sa facilité d'intégration avec Angular.

=== Besoins ===
Dans le cas d'un plugin pour Leaflet, les besoins sont :
  * supporter l'affichage de tuiles "vecteur" au format Mapbox Vector Tiles (MVT)
  * être activement maintenu
  * être le plus performant possible pour le rendu de tuiles vecteur dans un navigateur web

Dans le cas d'un framework cartographique différent de Leaflet, en plus des besoins listés précédemment, il doit :
  * supporter l'affichage de tuiles "raster" au format TMS fournie par OpenStreetMap
  * supporter l'affichage de données issues de web service WMS
  * supporter l'affichage de GeoJson
  * permettre l'édition en ligne (point, polygone) directement ou via un plugin

=== Plugins Leaflet ajoutant le support des tuiles vecteur ===
Leaflet consacre [[https://leafletjs.com/plugins.html#vector-tiles| une catégorie pour les plugins]] apportant le support des tuiles vecteur. Les plugins non libres ou s'appuyant sur une API propriétaire ne seront pas pris en compte.
Liste des plugins apportant le support des tuiles MVT ou GeoJson à Leaflet classé dans l'ordre décroissant d'activité/maintenance :
^  Nom                           ^  Dépôt                                                                                                    ^ Formats supportés  ^  Dernière version  ^  Date dernière release  ^  Date dernier commit  ^ Licence                ^
| **Maplibre GL Leaflet**        | [[https://github.com/maplibre/maplibre-gl-leaflet| maplibre/maplibre-gl-leaflet]]                         | MVT                |  v0.0.20           |  19 septembre 2023      |  6 juin 2024          | ISC                    |
| **Leaflet.VectorTileLayer**    | [[https://gitlab.com/jkuebart/Leaflet.VectorTileLayer/| jkuebart/Leaflet.VectorTileLayer]]                | MVT                |  v0.16.0           |  10 octobre 2023        |  20 avril 2024        | BSD-3-Clause license   |
| **Protomaps Leaflet**          | [[https://github.com/protomaps/protomaps-leaflet| protomaps/protomaps-leaflet]]                           | MVT, PMTiles       |  -                 |  -                      |  9 mai 2024           | BSD-3-Clause license   |
| **Vector Grid**                | [[https://github.com/Leaflet/Leaflet.VectorGrid| Leaflet/Leaflet.VectorGrid]]                             | MVT                |  v1.3.0            |  28 août 2017           |  1er septembre 2021   | THE BEER-WARE LICENSE  |
| **Leaflet Tilelayer GeoJson**  | [[https://github.com/glenrobertson/leaflet-tilelayer-geojson/| glenrobertson/leaflet-tilelayer-geojson]]  | GeoJson            |  v1.0.2            |  11 octobre 2016        |  21 octobre 2016      | THE BEER-WARE LICENSE  |
| **Hoverboard**                 | [[https://github.com/summer4096/hoverboard|summer4096/hoverboard]]                                        | MVT, GeoJson       |  v1.1.3            |  27 mars 2015           |  13 mai 2015          | ?                      |

L'utilisation de [[https://leafletjs.com/| Leafleft]] avec le plugin [[https://github.com/maplibre/maplibre-gl-leaflet|Maplibre-Gl-Leaflet]] est la solution qu'il faudrait retenir car la communauté autour de MapLibre (fork de Mapbox GL JS) semble être bien active. Ceci dit, les plugins [[https://gitlab.com/jkuebart/Leaflet.VectorTileLayer/| jkuebart/Leaflet.VectorTileLayer]] et [[https://github.com/protomaps/protomaps-leaflet| protomaps/protomaps-leaflet]] semblent être des solutions envisageables.

=== Framework carto web avec support natif des tuiles vecteur ===
Les frameworks cartographiques web suivant possèdent un support des tuiles vecteur sans l'ajout d'un plugin : 
  - [[https://maplibre.org/maplibre-gl-js/docs/| MapLibre GL]]
  - [[https://openlayers.org/| OpenLayers]]

=== Conclusion ===
Nous testerons l'utilisation du framework MapLibre GL car son développement a été basé dès l'origine sur l'utilisation de tuiles vecteur. Il est censé offrir les meilleures performances lors de l'utilisation de tuiles vecteur.

Ce test sera réalisé en développant [[https://github.com/cbn-alpin/gn_module_syntests/tree/develop| un module GeoNature spécifique "Syntests"]]. Cela permettra d'évaluer la facilité d'intégration du framework à Angular. Nous pourrons aussi concentrer nos efforts sur l'optimisation des performances du rendu des observations issues de la table ''synthese'' de GeoNature sans être contraints par l'implémentation actuelle du module "Synthese".
==== Tests de serveurs de tuiles vecteurs ====

Actuellement, il n'y a pas d'API fournissant de tuiles vecteurs de manière équivalente aux tuiles raster fournies par OpenStreetMap. Cela nécessite donc de **maintenir l'affichage du fond cartographique de base via des tuiles raster**.

Il existe [[https://medium.com/@frederic.rodrigo/web-mapping-comparing-vector-tile-servers-from-postgres-postgis-405055e69084|deux grands types de serveurs de tuiles vecteurs]]  :
  * les serveurs de fonds cartographiques : une tuile contient un très grand nombre de données différentes afin de générer un fond cartographique.
  * les serveurs de couches : une tuile contient uniquement les données de la couche demandée/configurée.

=== Serveurs de tuiles pour fonds cartographiques ===

Il existe des serveurs de tuiles vecteurs open source et/ou gratuit qui sont auto-hébergeables. Par exemple :
  * [[https://github.com/consbio/mbtileserver| Mbtileserver ]]: en Go. Utilise des tuiles issues de fichiers ''mbtiles''.
  * [[https://github.com/maptiler/tileserver-gl|Tileserver-GL]] : open source et gratuit. Utilise des tuiles issues de fichiers ''mbtiles''.
  * [[https://www.maptiler.com/server/|Maptiler Server]] : très simple à installer, à configurer et utiliser. Il est compatible Linux mais il nécessite une licence payante pour une utilisation en production.
  * [[https://github.com/mapbox/awesome-vector-tiles?tab=readme-ov-file#servers| Voir la liste de  serveurs de tuiles vecteurs]] maintenue par Mapbox

L'utilisation de ces serveurs de tuiles vecteur nécessite de les héberger sur son propre serveur. Cela implique :
  * l'installation, la configuration et la maintenance du serveur
  * de disposer de suffisamment d'espace disque : de quelques dizaines à plusieurs centaines de Go suivant les niveaux de zoom supportés et la taille de la carte à rendre (pays, continent, planète).

Dans le cadre de GeoNature, l'utilisation de ce type de serveurs peut être intéressant si le framework cartographique web utilisé pour gérer les cartes supporte bien les tuiles vecteurs. Cela ouvre la voie à de nouveaux usages : vue 3D (intéressant pour mieux visualiser les pentes, vallées...), changement de langue des textes de la carte, changement de style instantané du fond cartographique en fonction des usages (nuit, nature, ...). Dans le cas contraire, il vaut mieux privilégier l'utilisation de tuiles raster.

Pour les cartes de GeoNature, il pourrait être intéressant de **fournir des tuiles vecteurs pour le fond cartographique spécialement conçu pour les zones non urbanisés**. Ce style d'affichage est nommé [[https://openmaptiles.org/styles/#maptiler-terrain|"terrain" ou "outdoor"]]. Il existe [[https://github.com/openmaptiles/maptiler-terrain-gl-style|des fichiers de styles open source pour ce type d'affichage]].
Ce style nécessite l'utilisation de plusieurs types de tuiles :
  * raster-dem : pour les ombres portés du relief
  * vecteurs : pour les contours et les autres éléments 

Pour les contours (lignes de niveaux) et le relief, il est possible d'utiliser [[https://www.data.gouv.fr/fr/datasets/bd-alti-r-25-m-tuiles-pour-courbes-de-niveau-et-ombrage-dynamiques-1/|les fichiers fournis par Makina Corpus]] et qui concernent la France Métropolitaine. Voir également [[https://makina-corpus.com/sig-webmapping/optimisation-tuiles-mnt-rgb-ombrage-dynamique-mapbox-gl-maplibre-gl|l'article concernant la génération des ces tuiles]].

=== Serveurs de tuiles pour couches de données spécifiques ===

Ces serveurs s'appuie sur une base de données Postgis existante pour générer les tuiles vecteur. La configuration de ces serveurs prévoit de pouvoir associer une requête SQL à chaque couche de tuiles vecteur que l'on souhaite générer.

Serveurs de couches intéressant :
  * [[https://github.com/go-spatial/tegola| Tegola]] : en Go, open source et gratuit. Génère des tuiles depuis Postgresql ou des fichiers GPKG.
  * [[https://github.com/maplibre/martin | Martin]]
  * [[https://github.com/CrunchyData/pg_tileserv | pg_tileserver]]

Dans le cadre de GeoNature, ce type de serveur peut être intéressant à installer si l'on souhaite générer des couches de tuiles vecteur spécifiques à des données hébergées dans la base. Par exemple, les différents types de zones géographiques présents dans la table ''ref_geo.l_areas'' pourraient être proposés sous forme de web services de tuiles vecteur activable ou pas en fonction des besoins. Il est possible d'imaginer aussi des web services d’agrégation de données basés sur des vues : intensité de prospection, nombre d'observations, diversité spécifique...

Par contre, les services de tuiles en question ne doivent pas être assujetti à l'utilisation de filtres dépendant du choix de l'utilisateur via l'interface. Il faut pouvoir fournir les tuiles de la même façon quelle que soit la personne en faisant la demande...

==== Web service de tuiles vecteur sur mesure ====
Dans notre cas, il semble **plus intéressant de construire des web services de tuiles vecteur en s'appuyant sur les fonctionnalités offertes par Postgis**. Les données renvoyées dans les tuiles de la Synthèse" seront dépendantes :
  * de l'utilisateur en faisant la demande
  * de nombreux filtres au choix de l'utilisateur

Postgis fournit des fonctions permettant de faciliter la création de tuile MVT :
  * ''ST_TileEnvelope(z, x, y)'' ([[https://postgis.net/docs/ST_TileEnvelope.html|doc]]) : permet de créer un polygone rectangulaire (bbox) dans le SRID 3857 en fonction du niveau de zoom, du x et du y d'une URL d'un [[https://wiki.openstreetmap.org/wiki/TMS|web service TMS]].
  * ''ST_AsMVTGeom()'' ([[https://postgis.net/docs/ST_AsMVTGeom.html|doc]]) : transforme une géométrie dans l'espace de coordonnées d'une tuile MVT (Mapbox Vector Tile), en la coupant aux limites de la tuile si nécessaire.
  * ''UNION'' et ''ST_AsMVTGeom()'' : permettent de [[https://medium.com/@shahzadbacha.gis/composite-mvt-tiles-with-postgis-4b30d6c9f510| stocker plusieurs couches]] (//layers//) de géométries "MVT" dans la même tuile.
  * ''ST_AsMVT()'' ([[https://postgis.net/docs/ST_AsMVT.html|doc]]) : fonction d'agrégation qui renvoie une représentation binaire Mapbox Vector Tile d'un ensemble de lignes correspondant à une couche de tuiles. 

Exemple de requête créant une tuile vecteur basée sur la table ''ref_geo.l_areas'' de GeoNature pour les valeurs zoom = 16, x = 33877 et y = 23672  : <code sql>
WITH bounds AS (
	SELECT ST_TileEnvelope(16, 33877, 23672) AS envelope
), mvtgeom AS (
	SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), b.envelope) AS geom, 
	       t.id_area,
	       t.area_code,
	       t.area_name
	FROM ref_geo.l_areas AS t, bounds AS b
	WHERE ST_Intersects(t.geom, ST_Transform(b.envelope, 2154))
		AND t.id_type = ref_geo.get_id_area_type('COM')
)
SELECT ST_AsMVT(mvtgeom.*) FROM mvtgeom ;
</code>

Exemples de création de tuiles vecteurs en Python à l'aide de Postgis : 
  * Github :
    * [[https://github.com/pramsey/minimal-mvt/blob/master/minimal-mvt.py|pramsey/minimal-mvt]]
    * [[https://github.com/jbdesbas/vectipy/blob/main/app/routes.py|jbdesbas/vectipy]]
    * [[https://github.com/Oslandia/postile| Oslandia/postile]]
    * [[https://github.com/mkeller3/FastVector| Fast Vector - Python FastApi, Postgis]]
  * Articles :
    * [[https://medium.com/tantotanto/vector-tiles-postgis-and-openlayers-258a3b0ce4b6| Vector tiles, Postgis et OpenLayers]]
    * [[https://www.zimmi.cz/posts/2017/serving-mapbox-vector-tiles-with-postgis-nginx-and-python-backend/|Serving Mapbox Vector Tiles with Postgis, Nginx and Python backend]]
    * [[https://gis-ops.com/fastapi-auth-vector-tiles/|Restricted Vector Tile access with FastAPI & PostGIS]]
    * [[https://medium.com/@shahzadbacha.gis/composite-mvt-tiles-with-postgis-4b30d6c9f510| Composite MVT tiles with Postgis]]

==== Principe des tests de performance ====

Nous avons réalisé plusieurs tests à l'aide du module [[https://github.com/cbn-alpin/gn_module_syntests/tree/develop| un module GeoNature spécifique "Syntests"]]. Il nous a permis d'encapsuler rapidement l’exécution de requêtes SQL afin de comparer leur rendu sous forme de tuiles vecteur affichées à l'aide du framework carto web Maplibre GL.

Nous avons également utilisé les données de la base du SINP AURA qui comprend 23,5 millions d'observations dans la table ''synthese'' et 378 millions de lignes dans la table ''cor_area_synthese''. Nous avons pu ainsi tester l'efficacité des tuiles vecteurs sur une base de données comprenant un nombre conséquent d'observations.


=== Comparaison SRID 2154 et 4326 ===

Les [[#annexe_1_-_comparaison_srid_4326_et_2154| résultats de la comparaison]] du nombre de données récupérées et des temps d'obtention entre l'utilisation du SRID 4326 et 2154 ne montrent pas de différences majeures. Mais l'utilisation du SRID 4326 semble légèrement plus rapide et  demande moins de traitement pour son utilisation avec le format GeoJson.

//Conclusion// : **privilégier le SRID 4326** pour les champs de type géométrie.

=== Comparaison opérateur && et st_intersects avec index GIST ===

Le test de [[#annexe_2_-_comparaison_operateur_et_st_intersects_avec_index_gist| comparaison des opérateurs ''&&'' et de la fonction ''ST_Intersects'' avec un index GIST]] montre que l'opérateur ''&&'' est plus rapide. Cependant il ne peut être utilisé qu'avec des géométries de type "bounding box" (des rectangles).

//Conclusion// : **privilégier l'opérateur ''&&''** quand c'est possible sinon utiliser ''st_intersects''.

=== Comparaison opérateur && et st_intersects avec index BRIN ===

Le [[#annexe_3_-_comparaison_operateur_et_st_intersects_avec_index_brin| test d'utilisation d'un index de type BRIN]] sans trier les lignes sur la colonne géométrique utilisée montre des temps d'exécution plus long qu'avec l'index GIST. Ces temps sont plus longs pour l'opérateur ''&&'' comme pour la fonction ''ST_Intersects''.

//Conclusion// : **utiliser un index GIST** sur le champ contenant les géométries des observations de la Synthese.

=== Comparaison index GIST et SP-GIST ===
En suivant les informations fournies par ce document "[[https://public.dalibo.com/exports/formation/manuels/modules/j5/j5.handout.pdf| Dalibo - Indexation avancée]]", nous avons mis en place un index de type SP-GIST sur le champ géométrie de la Synthèse. Les [[#annexe_4_-_comparaison_index_gist_et_sp-gist| résultats de la requête utilisée]] montre un léger avantage pour l'index de type SP-GIST.

//Conclusion// : **utiliser un index SP-GIST** pour améliorer les performances sur la colonne géométrie de la Synthese.

=== Comparaison agrégation via table relation et via intersection ===

La [[#annexe_5_-_comparaison_agregation_via_table_relation_et_via_intersection| comparaison de l'agrégation des observations]] via l'utilisation de la table ''cor_area_synthese'' ou via l'opération spatiale ''&&'' et des index SP-GIST mais bien en évidence qu'il est plus performant d'utiliser une table relationnelle pour pré-stocker les intersections.

L’agrégation par maille de 10km à partir du zoom 8 met 6 secondes pour la table ''cor_area_synthese'' et 52 secondes avec l'intersection spatiale ! Et cela malgré les 378 millions de lignes de la table ''cor_area_synthese''.

Il faut donc maintenir l'utilisation de la table ''cor_area_synthese'' et chercher des solutions visant à optimiser ce mécanisme :
  * Ajout d'une colonne indiquant le code du type de zone géographique dans la table cor_area_synthese : ''area_type_code''
    * Partitionnement de la table basé sur le type de zone géographique
  * Création d'une table de relation spécialisée pour les mailles M10 : ''id_synthese'', ''id_area''
  * Mise en cache du nombre d'observations par maille M10 : ''id_area'', ''observation_nbr''

//Conclusion// : **pré-calculer et stocker dans une table les intersections de géométries**.

=== Test de l'ajout d'un champ area_type_code à la table cor_area_synthese ===
La table ''cor_area_synthese'' comprenant un très grand nombre de lignes (378 millions dans notre cas), il pourrait s'avérer plus performant de permettre à Postgresql d'éliminer un grand nombre d'entre elles en spécifiant le type de la géométrie liée. Pour cela, nous proposons [[#annexe_6_-_ajout_champ_area_type_code_a_cor_area_synthese| d'ajouter un champ ''area_type_code'']]. Nous avons également tester l'utilisation de [[#annexe_7_-_test_de_differents_index_sur_area_type_code| différents index sur ce champ]].

//Conclusion// : **l'utilisation du champ ''area_type_code'' améliore les performances lorsqu'il est utilisé avec un index '''btree(area_type_code, id_area)''**.

=== Test vue matérialisée pour relations entre observations ''synthese'' et mailles M10 ===
La [[#annexe_9_-_creation_vue_materialisee_cor_m10_synthese| création d'une vue matérialisée spécialisée pour stocker les relations entre observations ''synthese'' et mailles M10]] a permis de [[#annexe_10_-_test_utilisation_de_la_table_cor_m10_synthese| tester son utilisation]]. Nous avons constaté des résultats très proches de ceux obtenus avec le simple ajout du champ ''area_type_code'' sur la table ''cor_area_synthese''.

//Conclusion// : **continuer d'utiliser ''cor_area_synthese''** plutôt que des vues matérialisées par type de zones géoégraphiques.

=== Test de vues matérialisées pour mettre en cache le nombre d'observations ''synthese'' par mailles ===

La [[#annexe_11_-_creation_vue_materialisee_m10_observation_nbr| création d'une première vue matérialisée]] ''m10_observation_nbr'' a permis de mettre en cache le nombre d'observations ''synthese'' par maille M10. La [[#annexe_12_-_creation_vue_materialisee_observation_nbr| création d'une seconde vue matérialisée]] ''observation_nbr'' a permis de mettre en cache le nombre d'observations ''synthese'' pour les différents types de mailles (M10, M5 et M1).

Cela nous a permis de [[#annexe_13_-_tests_utilisation_vue_materialisee_m10_observation_nbr| tester l'utilisation d'une vue contenant un nombre réduit de données]], uniquement pour les mailles 10km (table ''m10_observation_nbr''), vis à vis de [[#annexe_14_-_tests_utilisation_vue_materialisee_observation_nbr| la vue contenant la mise en cache du nombre d'observations pour l'ensemble des mailles]] (table ''observation_nbr'').

Nous constatons bien que les requêtes effectuées sur la vue contenant la mise en cache de calcul seulement pour les mailles M10, est plus rapide que celle contenant l'ensemble des mailles. Mais dans les 2 cas et pour l'ensemble des requêtes, nous obtenons de très bon temps de réponse (moins de 0,1s).
Même si l'utilisation d'une vue matérialisée spécifique à un type de maille est plus rapide, il semble plus intéressant d'utiliser la vue matérialise prenant en compte l'ensemble des mailles car le rafraîchissement des données d'une seule vue matérialisée est plus simple à gérer.

Par ailleurs, la mise en cache de calcul sur de très grandes quantités de données est donc une solution très satisfaisante pour générer des tuiles vecteurs. Leur affichage dans l'interface est très fluide. Malheureusement, cette méthode peut être difficilement appliquée sur le module Synthese qui utilise de nombreux filtres spécifiques et dont les résultats sont également dépendant des droits de l'utilisateur.

//Conclusion// : 
  * **mettre les calculs (ex. ''COUNT'') en "cache" dès que possible**
  * trouver le juste milieu entre performance et facilité de maintenance

=== Test de la création d'une table "cor_area_synthese" partitionnée ===
//Ressources// :
    * [[https://blog.capdata.fr/index.php/postgresql-evolution-du-partitionnement-de-9-6-a-12-1-2/| PostgreSQL : évolution du partitionnement de 9.6 à 12 (1/2)]] : Capdata team, 14 novembre 2019.
    * [[https://blog.capdata.fr/index.php/postgresql-evolution-du-partitionnement-de-9-6-a-12-2-2/| PostgreSQL : évolution du partitionnement de 9.6 à 12 (2/2)]] : Capdata team, 22 novembre 2019. 

Nous avons [[#annexe_15_-_creation_d_une_table_cor_area_synthese_partitionnee| créé une table partitionnée ''cor_area_synthese'']]. Cela nous a permis [[#annexe_16_-_infos_sur_table_cor_area_synthese_partitionnee| d'obtenir des informations sur sa composition]], [[#annexe_17_-_test_d_utilisation_d_une_table_cor_area_synthese_partitionnee| effectuer des tests]] dessus et mieux appréhender le principe du partitionnement.

Les résultats sur la mise en cache des données pour l'ensemble des mailles vis à vis d'un seul type (M10) a montré que la requête s'exécutant sur un seul type était plus rapide. Le partitionnement subdivisant la table initiale en une multitude de table par type, nous pouvons penser que cela pourra améliorer les résultats.

//Notes// :
  * Il n'est pas possible de réutiliser une table standard pour la partitionnée, il faut recréer une nouvelle table partitionnée dès sa création.
  * Il est nécessaire d'ajouter une colonne ''area_type_code'' pour l'utiliser comme origine du partitionnement.
  * Toutes les colonnes présentes dans les partitions doivent faire partie de la clé primaire.
  * Il peut être intéressant d'activer le partitionnement d'une table lorsque la taille de celle-ci dépasse la quantité de mémoire vive disponible.

//Conclusions// : 
  * l'utilisation du partitionnement **ne semble pas plus performant que l'ajout d'un champ ''area_type_code''** sur la table ''cor_area_synthese''. Par contre, il **facilite la gestion des données de la table ''cor_area_synthese''**. Avec l'augmentation du nombre d'observations dans la ''synthese'', le partitionnement pourrait s'avérer plus performant à terme.
  * Il est **envisageable de gérer le partitionnement de la table ''cor_area_synthese'' sans toucher au code de GeoNature** à partir du moment où la table ''cor_area_synthese'' possède un champ ''area_type_code''.

=== Test de la fonction st_subdivide() ===
Dans le cadre de la création d'une table ''cor_area_synthese'' partitionnée, nous avons utilisé la fonction Postgis [[https://postgis.net/docs/ST_Subdivide.html| st_subdivide()]] afin de créer une table contenant une ensemble de géométries simplifiées pour chaque géométrie complexe (toutes sauf mailles) présentent dans la table ''l_areas''.

Les intersections effectuées à l'aide de la fonction [[https://postgis.net/docs/ST_Intersects.html| st_intersects()]] se sont avérées largement plus rapides lorsqu'elles sont exécutées sur les géométries simplifiées de cette table et cela même si elles en contient un plus grand nombre. Voir [[https://blog.cleverelephant.ca/2019/11/subdivide.html| cette article de Paul Ramsey sur le sujet]].

//Conclusion// : **utiliser st_intersects() sur des géométries complexes simplifiées via st_subdivide()**

=== Test utilisation table "cor_area_synthese" sans lien vers la "synthese" ===

Nous avons voulu [[#annexe_18_-_test_utilisation_table_cor_area_synthese_sans_lien_vers_la_synthese| tester une requête s’exécutant seulement sur une seule table]] pour évaluer l'impact des jointures. La requête effectuée seulement sur la table ''cor_area_synthese'' sans relation vers la table ''synthese'' divise le temps d'exécution par 5 !

Par ailleurs, nous avons confirmé dans le cadre de l'utilisation d'une autre base de données (Simethis) que des requêtes effectuées sur une seule table contenant des millions d'observations sans aucune jointure permet de générer un GeoJson agrégeant plusieurs millions d'observations (~8 millions) sous forme de mailles sur une surface correspondant à une région pour un temps d'exécution de 30 secondes !

//Conclusion// : **réaliser des requêtes sur une seule table bien indexée** !

=== Test requêtes Synthese sur VM indexée ===

Le test précédent a montré une très grande efficacité et nous avons pu la confirmer dans le cadre de l'utilisation d'une autre base de données (Simethis). Les requêtes effectuées sur une seule table contenant des millions d'observations sans aucune jointure permet de générer un GeoJson agrégeant plusieurs millions d'observations (~8 millions) sous forme de mailles sur une surface correspondant à une région pour temps d'exécution de 30 secondes !

Nous avons commencer à tester cela en générant une vue matérialisé v_synthese_for_web_app bien indexée. Ensuite, nous avons commencé à modifier le fichier [[https://github.com/PnX-SI/GeoNature/blob/develop/backend/geonature/core/gn_synthese/utils/query_select_sqla.py|query_select_sqla.py]] dans lequel la majorité des requêtes effectuées sur la Synthese sont générées.

Nous avons lancé des interrogations dans la Synthese en utilisant les filtres par taxon ou par commune. Une fois les requêtes modifiées pour n'utiliser que les champs de la nouvelle VM et après l'utilisation de EXPLAIN pour vérifier leur comportement, nous avons pu constaté une amélioration importante des performances.

Par ailleurs, cette approche permet de réutiliser un mécanisme déjà en place dans le module Synthese, l'utilisation de la vue ''v_synthese_for_web_app''. L'idée ici est donc de limiter toutes les requêtes à cette vue. //Vue// qui peut devenir //matérialisée// pour exploiter des index sur des bases de données GeoNature à forte volumétrie. Cette solution ne demande finalement qu'un minimum de modifications de code.

Nous pouvons continuer à utiliser du GeoJson pour le rendu à partir du moment où les observations sont agrégées par mailles dès que leur nombre devient trop important à afficher sur la carte. Une bascule automatique vers ce type d'affichage doit être mise en place également.

//Conclusion// : **exécuter des requêtes pour le module Synthese uniquement sur une vue matérialisée ''v_synthese_for_web_app'' bien indexée.**

==== Liste des améliorations ====

L'ensemble de ces tests nous permettent lister les éléments d'amélioration des performances suivant, utiliser :
  * le SRID 4326 pour les intersections
  * l'opérateur ''&&'' plutôt que ''st_intersects'' si possible
  * un index ''SP-GIST'' si les géométrie ne se chevauchent pas, sinon ''GIST'' plutôt que ''BRIN''. 
    * Pour ''synthese'' => SP-GIST, pour ''l_areas'' => GIST.
  * une table de relation (''cor_area_synthese'') plutôt qu'une recherche spatiale.
    * ajout d'une colonne ''area_type_code'' sur ''cor_area_synthese''.
  * ''st_intersects()'' sur des géométries complexes simplifiées via ''st_subdivide()''.
  * une  table/VM de cache stockant le nombre d'observations par maille pour gérer l'affichage par défaut sans filtre.
    * sinon utiliser seulement la table ''cor_area_synthese'' (sans relation avec la table ''synthese'') pour les requêtes avec des tailles de mailles appropriées au niveau de zoom pour gérer l'affichage par défaut sans filtre.
  * des requêtes s'exécutant uniquement sur une seule table bien indexée sans jointure.
  * des requêtes pour le module Synthese s'exécutant uniquement sur une vue matérialisée ''v_synthese_for_web_app'' bien indexée.

==== Conclusion sur l'utilisation des tuiles vecteurs ====

Concernant l'amélioration des performances du module Synthese, les différents tests effectués nous ont permis de nous rendre compte que **l'utilisation des tuiles vecteurs n'était pas à elle seule une solution concluante**. A certains niveaux de zoom, lorsque les bounding box des tuiles vecteurs se répartissent bien les mailles d’agrégation ou les observations affichées, la parallélisation des requêtes effectuées en base est intéressante.\\
Malheureusement, cette parallélisation des requêtes n'est pas valable à tous les niveaux de zoom. Avec les petits zoom, nous avons peu de requêtes parallélisées et elles s'exécutent sur de très grandes surfaces et quantités de données. Les performances sont dégradées.

En outre, pour que l'affichage sur la carte soit fluide et garantisse de bonnes conditions d'utilisation à l'utilisateur final, il est nécessaire que la création des  tuiles s'effectue très rapidement. À l'exception des requêtes exécutées sur une table mettant en cache le nombre d'observation par mailles, il n'a pas été possible de générer les tuiles assez vite pour garantir une impression de fluidité lors de l'affichage des tuiles sur la carte.

==== Conclusion sur le framework MapLibre GL ====

Le framework carto MapLibre GL a été facilement intégré à Angular lors de son utilisation dans le module Syntests. Une modification minime du cœur de GeoNature a toutefois été nécessaire. Nous avons pu constaté qu'il répondait bien à tous les besoins attendus d'une nouveau framework cartographique utilisable dans le cadre de GeoNature. Il apporte de nouvelles fonctionnalités très intéressantes : affichage du relief, rotation de la carte, traduction instantanée des libellées des cartes, changements de thèmes du rendu cartographique... C'est donc un choix très intéressant pour le remplacement de Leaflet.

Cependant, ce framework étant assez récent, il ne possède pas tous les plugins offerts par Leaflet. L'ajout des fonctionnalités d'édition de la carte s'est avéré ainsi plus complexe qu'avec Leaflet.

Au vu de la forte intégration de Leaflet aux composants génériques du frontend de GeoNature et au fait que l'utilisation des tuiles vecteurs ne résout pas les problèmes de performance, la bascule vers ce nouveau framework ne semble pas nécessaire. Il semble donc plus pertinent de continuer à utiliser Lealflet avec un rendu des observations à l'aide de GeoJson.

===== Conclusion générale =====

Nous proposons d'améliorer les performances du module Synthese en :
  * séparant les web services des principales fonctionnalités du module (carte, liste paginée, export).
  * créant un web service paginé côté serveur pour la liste des observations affichées sous forme de tableau
  * s'appuyant sur les mécanismes utilisés dans le module Export (limitation de l'utilisation mémoire), une génération de l'export exécuté en arrière plan (tâche Celery) et l'utilisation des notifications à l'utilisateur pour les téléchargements
  * utilisant une seule VM bien indexée pour exécuter toutes les requêtes SQL de la Synthese
  * maintenant l'utilisation de Leaflet et du GeoJson
  * vérifiant systématiquement les performances des requêtes à l'aide de ''EXPLAIN'' et son [[https://explain.dalibo.com/| outil de visualisation]] ou d'outils de statistiques comme ''pg_stat_statements''
  * en basculant automatiquement sur une agrégation par mailles des observations en fonction du nombre d'observation à afficher sur la carte




===== Annexes =====

==== Annexe 1 - Comparaison SRID 4326 et 2154 ====
<code sql>
-- 14, 8372, 5916  -- 10 row(s) fetched - 0,007s, on 2024-03-22 at 17:52:27
-- 13, 4185, 2957  -- 45 row(s) fetched - 0,042s (0,001s fetch), on 2024-03-22 at 17:46:28
-- 12, 2092, 1478  -- 545 row(s) fetched - 0,051s (0,014s fetch), on 2024-03-22 at 17:55:10
-- 11, 1046, 739   -- 2223 row(s) fetched - 0,152s (0,044s fetch), on 2024-03-22 at 17:54:35
-- 10, 523, 369    -- 18639 row(s) fetched - 1s (0,283s fetch), on 2024-03-22 at 17:54:54
WITH tile AS (
	SELECT ST_TileEnvelope(12, 2092, 1478) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON ST_Intersects(b.envelope_4326, s.the_geom_4326)
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;


-- 14, 8372, 5916  -- 13 row(s) fetched - 0,012s (0,001s fetch), on 2024-03-22 at 17:51:53
-- 13, 4185, 2957  -- 47 row(s) fetched - 0,045s (0,001s fetch), on 2024-03-22 at 17:52:51
-- 12, 2092, 1478  -- 564 row(s) fetched - 0,076s (0,015s fetch), on 2024-03-22 at 17:53:16
-- 11, 1046, 739   -- 2272 row(s) fetched - 0,229s (0,048s fetch), on 2024-03-22 at 17:53:33
-- 10, 523, 369    -- 18886 row(s) fetched - 1s (0,321s fetch), on 2024-03-22 at 17:53:50
WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 2154) AS envelope_2154
     FROM tile AS t
),
observations AS (
    SELECT
        s.the_geom_local AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON ST_Intersects(b.envelope_2154, s.the_geom_local)
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;
</code>

==== Annexe 2 - Comparaison opérateur && et st_intersects avec index GIST ====

<code sql>
DROP INDEX IF EXISTS gn_synthese.idx_synthese_the_geom_4326_brin;

CREATE INDEX i_synthese_the_geom_4326 ON gn_synthese.synthese USING GIST (the_geom_4326) ;

-- 14, 8372, 5916  -- 10 row(s) fetched - 0,022s (0,001s fetch), on 2024-03-22 at 18:13:39
-- 13, 4185, 2957  -- 45 row(s) fetched - 0,046s (0,001s fetch), on 2024-03-22 at 18:14:47
-- 12, 2092, 1478  -- 545 row(s) fetched - 0,058s (0,013s fetch), on 2024-03-22 at 18:14:30
-- 11, 1046, 739   -- 2223 row(s) fetched - 0,179s (0,044s fetch), on 2024-03-22 at 18:15:06
-- 10, 523, 369    -- 18639 row(s) fetched - 1s (0,288s fetch), on 2024-03-22 at 18:15:21
WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON ST_Intersects(b.envelope_4326, s.the_geom_4326)
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;


-- 14, 8372, 5916  -- 10 row(s) fetched - 0,004s (0,001s fetch), on 2024-03-22 at 18:15:52
-- 13, 4185, 2957  -- 46 row(s) fetched - 0,010s (0,002s fetch), on 2024-03-22 at 18:16:04
-- 12, 2092, 1478  -- 545 row(s) fetched - 0,043s (0,013s fetch), on 2024-03-22 at 18:16:17
-- 11, 1046, 739   -- 2223 row(s) fetched - 0,162s (0,044s fetch), on 2024-03-22 at 18:16:43
-- 10, 523, 369    -- 18640 row(s) fetched - 0,954s (0,419s fetch), on 2024-03-22 at 18:16:57
WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON b.envelope_4326 && s.the_geom_4326
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;
</code>

==== Annexe 3 - Comparaison opérateur && et st_intersects avec index BRIN ====
<code sql>
DROP INDEX gn_synthese.i_synthese_the_geom_4326;
CREATE INDEX idx_synthese_the_geom_4326_brin ON gn_synthese.synthese USING BRIN(the_geom_4326) WITH (pages_per_range = 1);

-- 14, 8372, 5916  -- 10 row(s) fetched - 2s (0,001s fetch), on 2024-03-22 at 18:02:09
-- 13, 4185, 2957  -- 45 row(s) fetched - 1s (0,001s fetch), on 2024-03-22 at 18:02:40
-- 12, 2092, 1478  -- 545 row(s) fetched - 1s (0,014s fetch), on 2024-03-22 at 18:03:00
-- 11, 1046, 739   -- 2223 row(s) fetched - 1s (0,051s fetch), on 2024-03-22 at 18:03:21
-- 10, 523, 369    -- 18639 row(s) fetched - 2s (0,300s fetch), on 2024-03-22 at 18:03:42
EXPLAIN (analyze,verbose,timing,costs,buffers) WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON ST_Intersects(b.envelope_4326, s.the_geom_4326)
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;


-- 14, 8372, 5916  -- 10 row(s) fetched - 1s, on 2024-03-22 at 18:04:51
-- 13, 4185, 2957  -- 46 row(s) fetched - 1s (0,001s fetch), on 2024-03-22 at 18:05:13
-- 12, 2092, 1478  -- 545 row(s) fetched - 1s (0,012s fetch), on 2024-03-22 at 18:05:30
-- 11, 1046, 739   -- 2223 row(s) fetched - 1s (0,042s fetch), on 2024-03-22 at 18:05:48
-- 10, 523, 369    -- 18640 row(s) fetched - 2s (0,291s fetch), on 2024-03-22 at 18:06:05
WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON b.envelope_4326 && s.the_geom_4326
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;
</code>

==== Annexe 4 - Comparaison index GIST et SP-GIST ====

<code sql>
-- 14, 8372, 5916  -- 10 row(s) fetched - 0,004s (0,001s fetch), on 2024-03-22 at 18:15:52
-- 13, 4185, 2957  -- 46 row(s) fetched - 0,010s (0,002s fetch), on 2024-03-22 at 18:16:04
-- 12, 2092, 1478  -- 545 row(s) fetched - 0,043s (0,013s fetch), on 2024-03-22 at 18:16:17
-- 11, 1046, 739   -- 2223 row(s) fetched - 0,162s (0,044s fetch), on 2024-03-22 at 18:16:43
-- 10, 523, 369    -- 18640 row(s) fetched - 0,954s (0,419s fetch), on 2024-03-22 at 18:16:57
WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON b.envelope_4326 && s.the_geom_4326
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;

DROP INDEX IF EXISTS gn_synthese.i_synthese_the_geom_4326;

CREATE INDEX i_synthese_the_geom_4326 ON gn_synthese.synthese USING SPGIST (the_geom_4326) ;

-- 14, 8372, 5916  -- 10 row(s) fetched - 0,005s, on 2024-03-22 at 20:36:49
-- 13, 4185, 2957  -- 46 row(s) fetched - 0,011s (0,001s fetch), on 2024-03-22 at 20:35:37
-- 12, 2092, 1478  -- 545 row(s) fetched - 0,037s (0,012s fetch), on 2024-03-22 at 20:35:53
-- 11, 1046, 739   -- 2223 row(s) fetched - 0,156s (0,042s fetch), on 2024-03-22 at 20:36:29
-- 10, 523, 369    -- 18640 row(s) fetched - 0,871s (0,287s fetch), on 2024-03-22 at 20:37:08
WITH tile AS (
	SELECT ST_TileEnvelope(10, 523, 369) AS envelope
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
observations AS (
    SELECT
        st_snaptogrid(s.the_geom_4326, 0.00001) AS geom,
        ST_Dimension(s.the_geom_local) AS dimension,
        round(ST_Perimeter(s.the_geom_local)) AS perimeter,
      	s.id_synthese AS id,
        s."precision"
    FROM gn_synthese.synthese AS s
        JOIN bounds AS b
            ON b.envelope_4326 && s.the_geom_4326
    WHERE round(ST_Perimeter(s.the_geom_local)) <= 4000
)
SELECT
    ST_Transform(o.geom, 3857),
    o.dimension,
    o.perimeter,
    count(o.id) AS nbr,
    json_agg(o.id) AS ids
FROM observations AS o
GROUP BY o.geom, o.dimension, o.perimeter ;
</code>

==== Annexe 5 - Comparaison agrégation via table relation et via intersection ====
<code sql>
DROP INDEX IF EXISTS ref_geo.idx_l_areas_geom_4326;

CREATE INDEX idx_l_areas_geom_4326 ON ref_geo.l_areas USING spgist(geom_4326) ;

-- Via table de relation cor_are_synthese
-- 14, 8372, 5915  ×   -- 
-- 13, 4186, 2957  ×   --
-- 12, 2093, 1478  M1  -- 64 row(s) fetched - 0,218s, on 2024-03-22 at 21:08:05
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,347s (0,002s fetch), on 2024-03-22 at 21:08:24
-- 10, 523, 369    M5  -- 47 row(s) fetched - 0,805s (0,001s fetch), on 2024-03-22 at 21:09:05
-- 9, 261, 184     M5  -- 118 row(s) fetched - 1s (0,001s fetch), on 2024-03-22 at 21:09:29
-- 8, 131, 92      M10 -- 128 row(s) fetched - 6s (0,001s fetch), on 2024-03-22 at 21:09:56
-- 7, 65, 46       M10 -- 187 row(s) fetched - 9s (0,002s fetch), on 2024-03-22 at 21:10:39
-- 6, 32, 23       M10 -- 219 row(s) fetched - 10s (0,002s fetch), on 2024-03-22 at 21:11:06
-- 5, 16, 11       M10 -- 819 row(s) fetched - 1m 9s (0,011s fetch), on 2024-03-28 at 15:46:54
WITH tile AS (
	SELECT ST_TileEnvelope(12, 2093, 1478) AS envelope -- SRID 3857
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        a.id_area AS id,
        a.geom,
        a.area_code AS code
    FROM ref_geo.l_areas AS a, bounds AS b
    WHERE a.geom_4326 && b.envelope_4326
    	AND a.id_type = ref_geo.get_id_area_type('M10')
)
SELECT
    ST_Transform(a.geom, 3857),
    a.code,
    COUNT(s.id_synthese) AS nbr
FROM gn_synthese.synthese AS s
    JOIN gn_synthese.cor_area_synthese AS cas
        ON s.id_synthese = cas.id_synthese
    JOIN areas AS a
        ON a.id = cas.id_area
GROUP BY a.geom, a.code ;


-- Via opérateur d'intersection &&
-- 12, 2093, 1478  M1  -- 64 row(s) fetched - 0,125s (0,002s fetch), on 2024-03-22 at 21:17:50
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,315s (0,003s fetch), on 2024-03-22 at 21:18:15
-- 10, 523, 369    M5  -- 47 row(s) fetched - 1s (0,001s fetch), on 2024-03-22 at 21:18:43
-- 9, 261, 184     M5  -- 118 row(s) fetched - 1s (0,004s fetch), on 2024-03-22 at 21:19:04
-- 8, 131, 92      M10 -- 128 row(s) fetched - 52s (0,002s fetch), on 2024-03-22 at 21:20:21
-- 7, 65, 46       M10 -- ×
-- 6, 32, 23       M10 -- ×
-- 5, 16, 11       M10 -- ×
WITH tile AS (
	SELECT ST_TileEnvelope(8, 131, 92) AS envelope -- SRID 3857
),
bounds AS (
    SELECT
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
	    a.geom_4326 AS geom,
    	a.id_area AS id,
        a.area_code AS code
    FROM ref_geo.l_areas AS a, bounds AS b
    WHERE a.geom_4326 && b.envelope_4326
    	AND a.id_type = ref_geo.get_id_area_type('M10')
)
SELECT
    ST_Transform(a.geom, 3857),
    a.code,
    COUNT(s.id_synthese) AS nbr
FROM gn_synthese.synthese AS s
    JOIN areas AS a
        ON a.geom && s.the_geom_4326 
GROUP BY a.geom, a.code ;
</code>

==== Annexe 6 - Ajout champ area_type_code à cor_area_synthese ====

<code sql>
-- Add column area_type_code to cor_area_synthese
ALTER TABLE gn_synthese.cor_area_synthese 
ADD area_type_code VARCHAR(25) DEFAULT NULL;

UPDATE gn_synthese.cor_area_synthese AS cas SET 
	area_type_code = t.type_code 
FROM ref_geo.l_areas AS a
	JOIN ref_geo.bib_areas_types AS t
		ON a.id_type = t.id_type 
WHERE a.id_area = cas.id_area ;


CREATE OR REPLACE FUNCTION gn_synthese.fct_trig_l_areas_insert_cor_area_synthese_on_each_statement()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
	DECLARE
	BEGIN
		-- Intersection de toutes les observations avec les nouvelles zones et écriture dans cor_area_synthese
		INSERT INTO gn_synthese.cor_area_synthese (id_area, id_synthese, area_type_code)
			SELECT
				new_areas.id_area AS id_area,
				s.id_synthese as id_synthese,
				bat.type_code
			FROM NEW AS new_areas
				JOIN ref_geo.bib_areas_types AS bat
					ON new_areas.id_type = bat.id_type
				JOIN gn_synthese.synthese AS s
					ON public.ST_INTERSECTS(s.the_geom_local, new_areas.geom)
			WHERE new_areas."enable" IS TRUE
				AND (
					ST_GeometryType(s.the_geom_local) = 'ST_Point'
					OR
					NOT public.ST_TOUCHES(s.the_geom_local, new_areas.geom)
			);
		RETURN NULL;
	END;
$function$ ;
</code>

==== Annexe 7 - Test de différents index sur area_type_code ====
<code sql>
-- Tests de 3 types d'index :
-- 379 millions de ligne 

CREATE INDEX idx_cas_area_type_code ON gn_synthese.cor_area_synthese USING hash(area_type_code) ;
--> création en + de 22h => abandon !

CREATE INDEX idx_cas_area_type_code ON gn_synthese.cor_area_synthese USING btree(area_type_code) ;
--> création en ~2mn => 2,4G d'espace disque

CREATE INDEX idx_cas_area_type_code_id_area ON gn_synthese.cor_area_synthese USING btree(area_type_code, id_area) ;
--> création en 3mn 16s => 2,5G d'espace disque

CREATE INDEX idx_cas_area_include_synthese ON gn_synthese.cor_area_synthese USING btree(area_type_code, id_area) INCLUDE (id_synthese);
--> création en 3mn 51s => 12G d'espace disque (avec mailles M20, M50)
</code>

==== Annexe 8 - Test des différents index sur area_type_code ====

<code sql>
-- Agrégation avec champ "area_type_code" dans "cor_area_synthese" avec idx btree(area_type_code)
-- 12, 2093, 1478  M1  -- 64  row(s) fetched - 0,253s, on 2024-03-24 at 21:28:13
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,465s (0,002s fetch), on 2024-03-24 at 21:27:45
-- 10, 523, 369    M5  -- 47  row(s) fetched - 0,804s (0,001s fetch), on 2024-03-24 at 21:29:06
-- 9, 261, 184     M5  -- 118 row(s) fetched - 1s (0,002s fetch), on 2024-03-24 at 21:29:26
-- 9, 261, 184     M10 -- 35  row(s) fetched - 2s, on 2024-03-24 at 21:26:28
-- 8, 131, 92      M10 -- 128 row(s) fetched - 6s (0,002s fetch), on 2024-03-24 at 21:30:04
-- 7, 65, 46       M10 -- 187 row(s) fetched - 9s (0,002s fetch), on 2024-03-24 at 21:30:35
-- 6, 32, 23       M10 -- 219 row(s) fetched - 10s (0,003s fetch), on 2024-03-24 at 21:22:44
-- 5, 16, 11       M10 -- 819 row(s) fetched - 1m 27s (0,009s fetch), on 2024-03-24 at 21:32:47

-- Agrégation avec champ "area_type_code" dans "cor_area_synthese" avec idx btree(area_type_code, id_area)
-- 12, 2093, 1478  M1  -- 64  row(s) fetched - 0,114s, on 2024-03-24 at 11:28:08
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,588s (0,003s fetch), on 2024-03-24 at 11:26:21
-- 10, 523, 369    M5  -- 47  row(s) fetched - 0,732s, on 2024-03-24 at 11:23:10
-- 9, 261, 184     M5  -- 118 row(s) fetched - 1s (0,002s fetch), on 2024-03-24 at 11:22:44
-- 9, 261, 184     M10 -- 35  row(s) fetched - 1s (0,001s fetch), on 2024-03-24 at 21:38:29
-- 8, 131, 92      M10 -- 128 row(s) fetched - 6s (0,001s fetch), on 2024-03-24 at 21:39:33
-- 7, 65, 46       M10 -- 187 row(s) fetched - 9s (0,003s fetch), on 2024-03-24 at 21:40:24
-- 6, 32, 23       M10 -- 219 row(s) fetched - 10s (0,003s fetch), on 2024-03-24 at 21:40:53
-- 5, 16, 11       M10 -- 819 row(s) fetched - 55s (0,009s fetch), on 2024-03-24 at 21:15:34
WITH tile AS (
    SELECT 
        'M10' AS type_code,
        ST_TileEnvelope(5, 16, 11) AS envelope -- SRID 3857
),
bounds AS (
    SELECT
    	t.type_code,
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        l.id_area AS id,
        l.geom,
        l.area_code AS code,
        b.type_code
    FROM ref_geo.l_areas AS l, bounds AS b
    WHERE l.geom_4326 && b.envelope_4326
    	AND l.id_type = ref_geo.get_id_area_type(b.type_code)
)
SELECT
    ST_Transform(a.geom, 3857),
    a.code,
    COUNT(s.id_synthese) AS nbr
FROM areas AS a
    JOIN gn_synthese.cor_area_synthese AS cas
    	ON (a.id = cas.id_area AND cas.area_type_code = a.type_code)
    JOIN gn_synthese.synthese AS s
        ON s.id_synthese = cas.id_synthese
GROUP BY a.geom, a.code ;
</code>

==== Annexe 9 - Création vue matérialisée cor_m10_synthese ====

<code sql>
-- Create a specialized table to store the relationship between synthese observations and M10 meshes
CREATE MATERIALIZED VIEW IF NOT EXISTS gn_synthese.cor_m10_synthese AS
	SELECT
        s.id_synthese,
        a.id_area
    FROM ref_geo.l_areas AS a
        JOIN gn_synthese.synthese AS s
            ON (a.geom && s.the_geom_local) -- Postgis operator && : https://postgis.net/docs/geometry_overlaps.html
    WHERE a.id_type = ref_geo.get_id_area_type('M10') ;

CREATE UNIQUE INDEX pk_cor_m10_synthese ON gn_synthese.cor_m10_synthese USING btree (id_synthese, id_area) ;
CREATE INDEX i_cor_m10_synthese_id_area ON gn_synthese.cor_m10_synthese USING btree (id_area) ;
</code>

==== Annexe 10 - Test utilisation de la table "cor_m10_synthese" ====

<code sql>
-- Agrégation avec utilisation de la table cor_m10_synthese
-- 8, 131, 92      M10 -- 128 row(s) fetched - 6s  (0,001s fetch), on 2024-03-25 at 21:05:11
-- 7, 65, 46       M10 -- 187 row(s) fetched - 8s  (0,003s fetch), on 2024-03-25 at 21:06:16
-- 6, 32, 23       M10 -- 219 row(s) fetched - 10s (0,003s fetch), on 2024-03-25 at 21:04:51
-- 5, 16, 11       M10 -- 819 row(s) fetched - 53s (0,010s fetch), on 2024-03-25 at 21:04:26
WITH tile AS (
	SELECT 
		'M10' AS type_code,
		ST_TileEnvelope(7, 65, 46) AS envelope -- SRID 3857
),
bounds AS (
    SELECT
    	t.type_code,
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        l.id_area AS id,
        l.geom,
        l.area_code AS code
    FROM ref_geo.l_areas AS l, bounds AS b
    WHERE l.geom_4326 && b.envelope_4326
    	AND l.id_type = ref_geo.get_id_area_type(b.type_code)
)
SELECT
    ST_Transform(a.geom, 3857),
    a.code,
    COUNT(s.id_synthese) AS nbr
FROM gn_synthese.synthese AS s
    JOIN gn_synthese.cor_m10_synthese AS cas
        ON s.id_synthese = cas.id_synthese
    JOIN areas AS a
        ON (a.id = cas.id_area)
GROUP BY a.geom, a.code ;
</code>

==== Annexe 11 - Création vue matérialisée m10_observation_nbr ====

<code sql>
-- Create a cache table to store the number of observations per M10 mesh only
CREATE MATERIALIZED VIEW IF NOT EXISTS gn_synthese.m10_observation_nbr AS
	SELECT
        a.id_area,
		COUNT(s.id_synthese) AS nbr
    FROM gn_synthese.synthese AS s
        JOIN gn_synthese.cor_area_synthese AS cas
            ON s.id_synthese = cas.id_synthese
        JOIN ref_geo.l_areas AS a
            ON a.id_area = cas.id_area
    WHERE a.id_type = ref_geo.get_id_area_type('M10') 
  	GROUP BY a.id_area ;
   
CREATE UNIQUE INDEX pk_m10_observation_nbr ON gn_synthese.m10_observation_nbr USING btree (id_area) ;
</code>

==== Annexe 12 - Création vue matérialisée observation_nbr ====

<code sql>
-- Create a cache table to store the number of observations per mesh (all size)
DROP MATERIALIZED VIEW IF EXISTS gn_synthese.observation_nbr ;
CREATE MATERIALIZED VIEW IF NOT EXISTS gn_synthese.observation_nbr AS
	SELECT
        a.id_area,
        bat.type_code AS type_code,
		COUNT(s.id_synthese) AS obs_nbr
    FROM gn_synthese.synthese AS s
        JOIN gn_synthese.cor_area_synthese AS cas
            ON s.id_synthese = cas.id_synthese
        JOIN ref_geo.l_areas AS a
            ON a.id_area = cas.id_area
        JOIN ref_geo.bib_areas_types AS bat
        	ON a.id_type = bat.id_type
    WHERE a.id_type IN (
    	ref_geo.get_id_area_type('M10'),
    	ref_geo.get_id_area_type('M5'),
    	ref_geo.get_id_area_type('M1')
    )
  	GROUP BY a.id_area, bat.type_code
  	ORDER BY bat.type_code, a.id_area ;
   
CREATE UNIQUE INDEX pk_observation_nbr ON gn_synthese.observation_nbr USING btree (id_area, type_code) ;
</code>

==== Annexe 13 - Tests utilisation vue matérialisée m10_observation_nbr ====

<code sql>
-- Agrégation avec utilisation de la table m10_observation_nbr
-- 8, 131, 92      M10 -- 128 row(s) fetched - 0,013s (0,003s fetch), on 2024-03-29 at 17:16:08
-- 7, 65, 46       M10 -- 187 row(s) fetched - 0,014s (0,007s fetch), on 2024-03-29 at 17:15:50
-- 6, 32, 23       M10 -- 219 row(s) fetched - 0,017s (0,007s fetch), on 2024-03-29 at 17:16:34
-- 5, 16, 11       M10 -- 819 row(s) fetched - 0,048s (0,021s fetch), on 2024-03-29 at 17:12:34
WITH tile AS (
	SELECT 
		'M10' AS type_code,
		ST_TileEnvelope(8, 131, 92) AS envelope -- SRID 3857
),
bounds AS (
    SELECT
    	t.type_code,
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        l.id_area AS id,
        l.geom,
        l.area_code AS code,
        b.type_code
    FROM ref_geo.l_areas AS l, bounds AS b
    WHERE l.geom_4326 && b.envelope_4326
    	AND l.id_type = ref_geo.get_id_area_type(b.type_code)
)
SELECT
    ST_Transform(a.geom, 3857),
    a.code,
    o.nbr
FROM gn_synthese.m10_observation_nbr AS o
	JOIN areas AS a
        ON a.id = o.id_area ;
</code>

==== Annexe 14 - Tests utilisation vue matérialisée observation_nbr ====

<code sql>
-- Agrégation avec utilisation de la table observation_nbr
-- 12, 2093, 1478  M1  -- 64  row(s) fetched - 0,007s (0,002s fetch), on 2024-03-28 at 11:09:28
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,010s (0,004s fetch), on 2024-03-28 at 11:10:04
-- 10, 523, 369    M5  -- 47  row(s) fetched - 0,008s (0,002s fetch), on 2024-03-28 at 11:08:21
-- 9, 261, 184     M5  -- 118 row(s) fetched - 0,011s (0,004s fetch), on 2024-03-28 at 11:08:00
-- 8, 131, 92      M10 -- 128 row(s) fetched - 0,010s (0,003s fetch), on 2024-03-28 at 11:06:01
-- 7, 65, 46       M10 -- 187 row(s) fetched - 0,012s (0,003s fetch), on 2024-03-28 at 11:06:23
-- 6, 32, 23       M10 -- 219 row(s) fetched - 0,015s (0,006s fetch), on 2024-03-28 at 11:05:05
-- 5, 16, 11       M10 -- 819 row(s) fetched - 0,036s (0,016s fetch), on 2024-03-28 at 11:05:27
WITH tile AS (
	SELECT 
		'M1' AS type_code,
		ST_TileEnvelope(11, 1046, 739) AS envelope -- SRID 3857
),
bounds AS (
    SELECT
    	t.type_code,
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        l.id_area AS id,
        l.geom,
        l.area_code AS code,
        b.type_code
    FROM ref_geo.l_areas AS l, bounds AS b
    WHERE l.geom_4326 && b.envelope_4326
    	AND l.id_type = ref_geo.get_id_area_type(b.type_code)
)
SELECT
    ST_Transform(a.geom, 3857),
    a.code,
    o.obs_nbr
FROM gn_synthese.observation_nbr AS o
	JOIN areas AS a
        ON (a.id = o.id_area AND a.type_code = o.type_code) ;
</code>

==== Annexe 15 - Création d'une table cor_area_synthese partitionnée ====

Création d'une table partitionnée nativement sur le code du type de zone géo reliée à une observation de la synthese :
<code sql>
DROP TABLE IF EXISTS gn_synthese.cor_area_synthese_partitioned ;

CREATE TABLE gn_synthese.cor_area_synthese_partitioned (
    id_synthese int4 NOT NULL,
	id_area int4 NOT NULL,
	area_type_code varchar(25) DEFAULT NULL::character varying NULL,
	CONSTRAINT pk_casp PRIMARY KEY (id_synthese, id_area, area_type_code),
	CONSTRAINT fk_casp_id_area FOREIGN KEY (id_area) REFERENCES ref_geo.l_areas(id_area) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT fk_casp_id_synthese FOREIGN KEY (id_synthese) REFERENCES gn_synthese.synthese(id_synthese) ON DELETE CASCADE ON UPDATE CASCADE
) PARTITION BY list(area_type_code);

	CREATE INDEX idx_casp_id_area ON gn_synthese.cor_area_synthese_partitioned USING btree (id_area);
	CREATE INDEX idx_casp_area_type_code_id_area ON gn_synthese.cor_area_synthese_partitioned USING btree (area_type_code, id_area);

	CREATE TABLE gn_synthese.cor_area_synthese_m10 PARTITION OF gn_synthese.cor_area_synthese_partitioned
	    FOR VALUES IN ('M10');
	
	CREATE TABLE gn_synthese.cor_area_synthese_m5 PARTITION OF gn_synthese.cor_area_synthese_partitioned
	    FOR VALUES IN ('M5');
	
	CREATE TABLE gn_synthese.cor_area_synthese_m1 PARTITION OF gn_synthese.cor_area_synthese_partitioned
	    FOR VALUES IN ('M1');
	
	CREATE TABLE gn_synthese.cor_area_synthese_com PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('COM');
   	
    CREATE TABLE gn_synthese.cor_area_synthese_dep PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('DEP');
   	
    CREATE TABLE gn_synthese.cor_area_synthese_sinp PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('SINP');
   
    CREATE TABLE gn_synthese.cor_area_synthese_aa PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('AA');
    
    CREATE TABLE gn_synthese.cor_area_synthese_apb PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('APB');
    
    CREATE TABLE gn_synthese.cor_area_synthese_metr PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('METR');
    
    CREATE TABLE gn_synthese.cor_area_synthese_pnr PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('PNR');
    
    CREATE TABLE gn_synthese.cor_area_synthese_rbiol PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('RBIOL');
   
    CREATE TABLE gn_synthese.cor_area_synthese_rbios PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('RBIOS');
    
    CREATE TABLE gn_synthese.cor_area_synthese_reg PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('REG');
   
    CREATE TABLE gn_synthese.cor_area_synthese_ripn PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('RIPN');
    
    CREATE TABLE gn_synthese.cor_area_synthese_rncfs PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('RNCFS');
    
    CREATE TABLE gn_synthese.cor_area_synthese_rnn PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('RNN');
    
    CREATE TABLE gn_synthese.cor_area_synthese_rnr PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('RNR');
    
    CREATE TABLE gn_synthese.cor_area_synthese_scen PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('SCEN');
    
    CREATE TABLE gn_synthese.cor_area_synthese_scl PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('SCL');
    
    CREATE TABLE gn_synthese.cor_area_synthese_sic PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('SIC');
    
    CREATE TABLE gn_synthese.cor_area_synthese_sram PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('SRAM');
    
    CREATE TABLE gn_synthese.cor_area_synthese_zbiog PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('ZBIOG');
    
    CREATE TABLE gn_synthese.cor_area_synthese_zc PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('ZC');
    	
    CREATE TABLE gn_synthese.cor_area_synthese_zico PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('ZICO');
    
    CREATE TABLE gn_synthese.cor_area_synthese_znieff1 PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('ZNIEFF1');
    
    CREATE TABLE gn_synthese.cor_area_synthese_znieff2 PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('ZNIEFF2');
    	
    CREATE TABLE gn_synthese.cor_area_synthese_zps PARTITION OF gn_synthese.cor_area_synthese_partitioned
    	FOR VALUES IN ('ZPS');
  
DROP TABLE IF EXISTS ref_geo.tmp_subdivided_areas ;

CREATE TABLE IF NOT EXISTS ref_geo.tmp_subdivided_areas AS
    SELECT
        random() AS gid,
        a.id_area AS area_id,
        bat.type_code,
        st_subdivide(a.geom, 250) AS geom
    FROM ref_geo.l_areas AS a
    	JOIN ref_geo.bib_areas_types AS bat
    		ON a.id_type = bat.id_type
    WHERE a."enable" = TRUE
        AND bat.type_code NOT IN ('M10', 'M5', 'M1') ;

      CREATE INDEX IF NOT EXISTS idx_tmp_subdivided_geom ON ref_geo.tmp_subdivided_areas USING gist (geom);
      CREATE INDEX IF NOT EXISTS idx_tmp_subdivided_area_id ON ref_geo.tmp_subdivided_areas USING btree(area_id) ;

TRUNCATE TABLE gn_synthese.cor_area_synthese_partitioned ;

INSERT INTO gn_synthese.cor_area_synthese_partitioned
    SELECT DISTINCT
        s.id_synthese,
        a.area_id,
        a.type_code
    FROM gn_synthese.synthese AS s
        JOIN ref_geo.tmp_subdivided_areas AS a
            ON public.st_intersects(s.the_geom_local, a.geom) ;

INSERT INTO gn_synthese.cor_area_synthese_partitioned
    SELECT
        s.id_synthese,
        a.id_area,
        bat.type_code
    FROM ref_geo.l_areas AS a
    	JOIN ref_geo.bib_areas_types AS bat
    		ON a.id_type = bat.id_type
        JOIN gn_synthese.synthese AS s
            ON (a.geom && s.the_geom_local) -- Postgis operator && : https://postgis.net/docs/geometry_overlaps.html
    WHERE bat.type_code IN ('M10', 'M5', 'M1') ;
</code> 

==== Annexe 16 - Infos sur table "cor_area_synthese" partitionnée ====
  * Temps d’exécution du script précédent : **4h05mn**
  * Nombre d'entrées dans la table ''gn_synthese.synthese'' : ** 23 523 902 ** 
Détails des zones géo par types de la table ''ref_geo.l_areas'' :
<code sql>
SELECT 
	bat.type_code,
	bat.type_name,
	count(a.id_area) AS "geom count"
FROM ref_geo.bib_areas_types AS bat
	JOIN ref_geo.l_areas AS a 
		ON a.id_type = bat.id_type 
GROUP BY bat.type_code, bat.type_name 
ORDER BY bat.type_code ;
</code>
<code>
|type_code|type_name                                         |geom count|
|---------|--------------------------------------------------|----------|
|AA       |Aire d'adhésion des Parcs nationaux               |3         |
|APB      |Aires de protection de biotope                    |184       |
|COM      |Communes                                          |4 095     |
|DEP      |Départements                                      |12        |
|M1       |Mailles1*1                                        |72 230    |
|M10      |Mailles10*10                                      |819       |
|M5       |Mailles5*5                                        |3 078     |
|METR     |Métropoles et Communautés de Communes             |1         |
|PNR      |Parcs naturels régionaux                          |9         |
|RBIOL    |Réserves biologiques                              |34        |
|RBIOS    |Réserves de biosphère                             |7         |
|REG      |Région                                            |1         |
|RIPN     |Réserves intégrales de parc national              |1         |
|RNCFS    |Réserves nationales de chasse et faune sauvage    |2         |
|RNN      |Réserves naturelles nationales                    |37        |
|RNR      |Réserves naturelles regionales                    |17        |
|SCEN     |Sites acquis des Conservatoires d'espaces naturels|236       |
|SCL      |Sites du Conservatoire du Littoral                |26        |
|SIC      |Natura 2000 - Sites d'importance communautaire    |240       |
|SINP     |Territoire SINP                                   |1         |
|SRAM     |Sites Ramsar                                      |3         |
|TERRITORY|Territoire                                        |1         |
|ZBIOG    |Zones biogéographiques                            |4         |
|ZC       |Coeurs des Parcs nationaux                        |3         |
|ZICO     |Zone d'importance pour la conservation des oiseaux|32        |
|ZNIEFF1  |ZNIEFF1                                           |3 388     |
|ZNIEFF2  |ZNIEFF2                                           |272       |
|ZPS      |Natura 2000 - Zones de protection spéciales       |56        |
</code>

Taille de la table ''cor_area_synthese'' par défaut :
<code sql>
SELECT 
	'gn_synthese.cor_area_synthese' AS "table",
	pg_size_pretty(pg_relation_size('gn_synthese.cor_area_synthese')) AS internal,
	pg_size_pretty(pg_table_size('gn_synthese.cor_area_synthese') - pg_relation_size('gn_synthese.cor_area_synthese')) AS "external", -- toast
	pg_size_pretty(pg_indexes_size('gn_synthese.cor_area_synthese')) AS "indexes",
	pg_size_pretty( pg_total_relation_size('gn_synthese.cor_area_synthese') ) AS total,
	COUNT(*) AS "rows"
FROM gn_synthese.cor_area_synthese ;
</code>
<code>
|table                        |internal|external|indexes|total|rows       |
|-----------------------------|--------|--------|-------|-----|-----------|
|gn_synthese.cor_area_synthese|29 GB   |8304 kB |26 GB  |55 GB|378 815 208|
</code>

Résumé de la place occupée par la table ''cor_area_synthese'' partitionnée :
<code sql>
SELECT
   pi.inhparent::regclass AS "Partitioned table name",
   sum(psut.n_live_tup) AS "rows count",
   pg_size_pretty(sum(pg_relation_size(psu.relid))) AS internal,
   pg_size_pretty(sum(pg_table_size(psu.relid) - pg_relation_size(psu.relid))) AS "external", -- toast
   pg_size_pretty(sum(pg_indexes_size(psu.relid))) AS "indexes",
   pg_size_pretty(sum(pg_total_relation_size(psu.relid))) AS total
FROM pg_catalog.pg_statio_user_tables AS psu
	JOIN pg_class AS pc 
		ON psu.relname = pc.relname
	JOIN pg_database AS pd 
		ON pc.relowner = pd.datdba
	JOIN pg_inherits AS pi 
		ON pi.inhrelid = pc.oid
	JOIN pg_stat_user_tables AS psut
		ON psut.relid = psu.relid
WHERE pd.datname = 'gn2_default'
GROUP BY pi.inhparent
ORDER BY sum(pg_total_relation_size(psu.relid)) DESC;
</code>
<code>
|Partitioned table name                   |rows count |internal|external|indexes|total|
|-----------------------------------------|-----------|--------|--------|-------|-----|
|gn_synthese.cor_area_synthese_partitioned|378 815 305 |16 GB   |5304 kB |20 GB  |36 GB|
</code>

Calcul de la place occupée par les partitions :
<code sql>
SELECT
    child.relname AS "partition",
    psut.n_live_tup AS "rows count",
    pg_size_pretty(pg_relation_size(concat(nmsp_child.nspname, '.', child.relname))) AS internal,
    pg_size_pretty(pg_table_size(concat(nmsp_child.nspname, '.', child.relname)) - pg_relation_size(concat(nmsp_child.nspname, '.', child.relname))) AS external, -- toast
    pg_size_pretty(pg_indexes_size(concat(nmsp_child.nspname, '.', child.relname))) AS "indexes",
    pg_size_pretty(pg_total_relation_size(child.oid)) AS total
FROM pg_inherits
    JOIN pg_class AS parent
    	ON pg_inherits.inhparent = parent.oid
    JOIN pg_class AS child
    	ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace AS nmsp_child
		ON nmsp_child.oid   = child.relnamespace
	JOIN pg_stat_user_tables AS psut
		ON psut.relid = child.oid
WHERE parent.relname = 'cor_area_synthese_partitioned'
ORDER BY child.relname ;
</code>

<code>
|partition                  |rows count |internal|external|indexes |total  |
|---------------------------|-----------|--------|--------|--------|-------|
|cor_area_synthese_aa       |341 435    |14 MB   |32 kB   |17 MB   |31 MB  |
|cor_area_synthese_apb      |934 481    |39 MB   |40 kB   |50 MB   |90 MB  |
|cor_area_synthese_com      |32 497 851 |1372 MB |416 kB  |1703 MB |3076 MB|
|cor_area_synthese_dep      |23 819 618 |1006 MB |304 kB  |1223 MB |2229 MB|
|cor_area_synthese_m1       |114 355 715|4829 MB |1384 kB |6623 MB |11 GB  |
|cor_area_synthese_m10      |28 629 845 |1209 MB |368 kB  |1505 MB |2714 MB|
|cor_area_synthese_m5       |33 117 680 |1399 MB |416 kB  |1759 MB |3158 MB|
|cor_area_synthese_metr     |265 394    |11 MB   |32 kB   |14 MB   |25 MB  |
|cor_area_synthese_others   |930 895    |39 MB   |40 kB   |48 MB   |87 MB  |
|cor_area_synthese_pnr      |4 944 797  |209 MB  |80 kB   |256 MB  |465 MB |
|cor_area_synthese_rbiol    |120 425    |5208 kB |32 kB   |6464 kB |11 MB  |
|cor_area_synthese_reg      |23 495 584 |992 MB  |304 kB  |1196 MB |2188 MB|
|cor_area_synthese_ripn     |13 221     |576 kB  |32 kB   |768 kB  |1376 kB|
|cor_area_synthese_rncfs    |41 321     |1792 kB |32 kB   |2200 kB |4024 kB|
|cor_area_synthese_rnn      |728 092    |31 MB   |32 kB   |37 MB   |68 MB  |
|cor_area_synthese_rnr      |190 592    |8248 kB |32 kB   |10032 kB|18 MB  |
|cor_area_synthese_scen     |812 249    |34 MB   |40 kB   |42 MB   |76 MB  |
|cor_area_synthese_scl      |122 572    |5304 kB |32 kB   |6440 kB |12 MB  |
|cor_area_synthese_sic      |6 033 103  |255 MB  |96 kB   |311 MB  |565 MB |
|cor_area_synthese_sinp     |23 495 584 |992 MB  |304 kB  |1203 MB |2196 MB|
|cor_area_synthese_sram     |224 062    |9696 kB |32 kB   |11 MB   |21 MB  |
|cor_area_synthese_territory|23 495 584 |1169 MB |352 kB  |1474 MB |2644 MB|
|cor_area_synthese_zbiog    |23 552 681 |995 MB  |304 kB  |1200 MB |2195 MB|
|cor_area_synthese_zc       |274 089    |12 MB   |32 kB   |14 MB   |25 MB  |
|cor_area_synthese_zico     |3 982 270  |168 MB  |72 kB   |202 MB  |370 MB |
|cor_area_synthese_znieff1  |13 676 029 |578 MB  |192 kB  |721 MB  |1299 MB|
|cor_area_synthese_znieff2  |15 162 334 |640 MB  |208 kB  |776 MB  |1416 MB|
|cor_area_synthese_zps      |3 557 802  |150 MB  |64 kB   |184 MB  |334 MB |
</code>

==== Annexe 17 - Test d'utilisation d'une table "cor_area_synthese" partitionnée ====
  * Explain : https://explain.dalibo.com/plan/71eh98d2e11fcceb
<code sql>
-- Agrégation avec champ "area_type_code" dans "cor_area_synthese_partioned" avec idx btree(area_type_code, id_area)
-- 12, 2093, 1478  M1  -- 64 row(s)  fetched - 0,102s (0,001s fetch), on 2024-03-29 at 16:10:11
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,229s (0,002s fetch), on 2024-03-29 at 16:10:35
-- 10, 523, 369    M5  -- 47  row(s) fetched - 0,683s (0,001s fetch), on 2024-03-29 at 16:11:51
-- 9, 261, 184     M5  -- 118 row(s) fetched - 1s (0,001s fetch), on 2024-03-29 at 16:12:14
-- 9, 261, 184     M10 -- 35  row(s) fetched - 2s, on 2024-03-29 at 16:12:39
-- 8, 131, 92      M10 -- 128 row(s) fetched - 6s (0,002s fetch), on 2024-03-29 at 16:13:19
-- 7, 65, 46       M10 -- 187 row(s) fetched - 9s (0,002s fetch), on 2024-03-29 at 15:54:10
-- 6, 32, 23       M10 -- 219 row(s) fetched - 10s (0,003s fetch), on 2024-03-29 at 15:55:55
-- 5, 16, 11       M10 -- 819 row(s) fetched - 55s (0,009s fetch), on 2024-03-29 at 15:57:08
WITH tile AS (
	SELECT
        'M10' AS type_code,
        ST_TileEnvelope(8, 131, 92) AS envelope
),
bounds AS (
    SELECT
        t.type_code,
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        l.id_area AS id,
        l.geom,
        l.area_code AS code,
        b.type_code
    FROM ref_geo.l_areas AS l, bounds AS b
    WHERE l.geom_4326 && b.envelope_4326
        AND l.id_type = ref_geo.get_id_area_type(b.type_code)
), 
observations AS (
	SELECT
	    a.geom,
	    a.code,
	    COUNT(s.id_synthese) AS nbr
	FROM areas AS a
	    JOIN gn_synthese.cor_area_synthese_partitioned AS casp
	        ON (casp.id_area = a.id AND casp.area_type_code = a.type_code)
	    JOIN gn_synthese.synthese AS s
	        ON casp.id_synthese = s.id_synthese
	GROUP BY a.geom, a.code
)
SELECT
    ST_Transform(o.geom, 3857) AS geom,
    o.nbr,
    o.code
FROM observations AS o;
</code>

==== Annexe 18 - Test utilisation table "cor_area_synthese" sans lien vers la "synthese" ====
  * Explain : https://explain.dalibo.com/plan/9ddfc18gfc61g83b
<code sql>
-- Agrégation avec champ "area_type_code" dans "cor_area_synthese_partioned" avec idx btree(area_type_code, id_area) sans relation vers "synthese"
-- 12, 2093, 1478  M1  -- 64  row(s) fetched - 0,035s (0,001s fetch), on 2024-03-29 at 17:06:16
-- 11, 1046, 739   M1  -- 204 row(s) fetched - 0,071s (0,002s fetch), on 2024-03-29 at 17:05:58
-- 10, 523, 369    M5  -- 47  row(s) fetched - 0,188s (0,001s fetch), on 2024-03-29 at 17:05:44
-- 9, 261, 184     M5  -- 118 row(s) fetched - 0,365s (0,002s fetch), on 2024-03-29 at 17:05:27
-- 9, 261, 184     M10 -- 35  row(s) fetched - 0,844s, on 2024-03-29 at 17:05:12
-- 8, 131, 92      M10 -- 128 row(s) fetched - 1s (0,002s fetch), on 2024-03-29 at 17:04:43
-- 7, 65, 46       M10 -- 187 row(s) fetched - 2s (0,002s fetch), on 2024-03-29 at 17:07:44
-- 6, 32, 23       M10 -- 219 row(s) fetched - 2s (0,003s fetch), on 2024-03-29 at 17:07:58
-- 5, 16, 11       M10 -- 819 row(s) fetched - 10s (0,009s fetch), on 2024-03-29 at 17:08:35
-- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) 
WITH tile AS (
	SELECT
        'M10' AS type_code,
        ST_TileEnvelope(8, 131, 92) AS envelope
),
bounds AS (
    SELECT
        t.type_code,
        t.envelope,
        ST_Transform(t.envelope, 4326) AS envelope_4326
     FROM tile AS t
),
areas AS (
    SELECT
        l.id_area AS id,
        l.geom,
        l.area_code AS code,
        b.type_code
    FROM ref_geo.l_areas AS l, bounds AS b
    WHERE l.geom_4326 && b.envelope_4326
        AND l.id_type = ref_geo.get_id_area_type(b.type_code)
), 
observations AS (
	SELECT
	    a.geom,
	    a.code,
	    COUNT(casp.id_synthese) AS nbr
	FROM areas AS a
	    JOIN gn_synthese.cor_area_synthese_partitioned AS casp
	        ON (casp.id_area = a.id AND casp.area_type_code = a.type_code)
	GROUP BY a.geom, a.code
)
SELECT
    ST_Transform(o.geom, 3857) AS geom,
    o.nbr,
    o.code
FROM observations AS o;
</code>

==== Annexe 19 - Test requêtes Synthese uniquement sur VM v_synthese_for_webapp ====

<code sql>
CREATE EXTENSION IF NOT EXISTS intarray;


DROP TABLE IF EXISTS ref_geo.areas_subdivided ;

CREATE TABLE IF NOT EXISTS ref_geo.areas_subdivided AS
	SELECT
		random() AS gid,
		g.area_id,
		g.type_code,
		g.geom
	FROM (
		SELECT
	        a.id_area AS area_id,
	        bat.type_code,
	        st_subdivide(a.geom_4326, 250) AS geom
	    FROM ref_geo.l_areas AS a
	    	JOIN ref_geo.bib_areas_types AS bat
	    		ON a.id_type = bat.id_type
	    WHERE a."enable" = TRUE
	        AND a.id_type NOT IN (
	        	ref_geo.get_id_area_type('M50'),
	        	ref_geo.get_id_area_type('M20'),
	            ref_geo.get_id_area_type('M10'),
	            ref_geo.get_id_area_type('M5'),
	            ref_geo.get_id_area_type('M1')
	        )
	    UNION
	    SELECT
	        a.id_area AS area_id,
	        bat.type_code,
	        a.geom_4326 AS geom
	    FROM ref_geo.l_areas AS a
	    	JOIN ref_geo.bib_areas_types AS bat
	    		ON a.id_type = bat.id_type
	    WHERE a."enable" = TRUE
	        AND a.id_type IN (
	        	ref_geo.get_id_area_type('M50'),
	        	ref_geo.get_id_area_type('M20'),
	            ref_geo.get_id_area_type('M10'),
	            ref_geo.get_id_area_type('M5'),
	            ref_geo.get_id_area_type('M1')
	        )
	) AS g
;
CREATE INDEX IF NOT EXISTS idx_areas_subdivided_geom ON ref_geo.areas_subdivided USING gist (geom);
CREATE INDEX IF NOT EXISTS idx_areas_subdivided_area_id ON ref_geo.areas_subdivided USING btree(area_id) ;
CREATE INDEX IF NOT EXISTS idx_areas_subdivided_type_code ON ref_geo.areas_subdivided USING btree(type_code) ;


DROP MATERIALIZED VIEW IF EXISTS gn_synthese.v_synthese_for_web_app_full ;

CREATE MATERIALIZED VIEW gn_synthese.v_synthese_for_web_app_full AS
	SELECT
		s.id_synthese,
		s.unique_id_sinp,
		s.unique_id_sinp_grp,
		s.id_source,
		s.entity_source_pk_value,
		s.count_min,
		s.count_max,
		s.nom_cite,
		s.meta_v_taxref,
		s.sample_number_proof,
		s.digital_proof,
		s.non_digital_proof,
		s.altitude_min,
		s.altitude_max,
		s.depth_min,
		s.depth_max,
		s.place_name,
		s."precision",
		s.the_geom_4326,
		st_asgeojson(s.the_geom_4326)::jsonb  AS st_asgeojson,
		-- ADD blurring geom
		s.date_min,
		s.date_max,
		s.validator,
		s.validation_comment,
		s.observers,
		s.id_digitiser,
		s.determiner,
		s.comment_context,
		s.comment_description,
		s.meta_validation_date,
		s.meta_create_date,
		s.meta_update_date,
		s.last_action,
		d.id_dataset,
		d.dataset_name,
		d.id_acquisition_framework,
		(SELECT array_agg(DISTINCT cda.id_organism) FROM gn_meta.cor_dataset_actor AS cda WHERE cda.id_dataset = d.id_dataset AND cda.id_organism IS NOT NULL) AS organisms,
		s.id_nomenclature_geo_object_nature,
		s.id_nomenclature_info_geo_type,
		s.id_nomenclature_grp_typ,
		s.grp_method,
		s.id_nomenclature_obs_technique,
		s.id_nomenclature_bio_status,
		s.id_nomenclature_bio_condition,
		s.id_nomenclature_naturalness,
		s.id_nomenclature_exist_proof,
		s.id_nomenclature_valid_status,
		s.id_nomenclature_diffusion_level,
		s.id_nomenclature_life_stage,
		s.id_nomenclature_sex,
		s.id_nomenclature_obj_count,
		s.id_nomenclature_type_count,
		s.id_nomenclature_sensitivity,
		s.id_nomenclature_observation_status,
		s.id_nomenclature_blurring,
		s.id_nomenclature_source_status,
		s.id_nomenclature_determination_method,
		s.id_nomenclature_behaviour,
		s.reference_biblio,
		sources.name_source,
		sources.url_source,
		t.cd_nom,
		t.cd_ref,
		t.nom_valide,
		t.lb_nom,
		t.nom_vern,
		s.id_module,
		t.group1_inpn,
		t.group2_inpn,
		t.group3_inpn,
		-- TODO : use blurring geom instead
		(SELECT a.geom FROM ref_geo.areas_subdivided AS a WHERE s.the_geom_point && a.geom AND a.type_code = 'M5' LIMIT 1) AS m5,
		(SELECT a.geom FROM ref_geo.areas_subdivided AS a WHERE s.the_geom_point && a.geom AND a.type_code = 'M10' LIMIT 1) AS m10,
		(SELECT array_agg(DISTINCT a.area_id) FROM ref_geo.areas_subdivided AS a WHERE st_intersects(s.the_geom_4326, a.geom) AND a.type_code = 'COM') AS municipalities,
		(SELECT array_agg(DISTINCT a.area_id) FROM ref_geo.areas_subdivided AS a WHERE st_intersects(s.the_geom_4326, a.geom) AND a.type_code = 'DEP') AS departements
	FROM gn_synthese.synthese AS s
		JOIN taxonomie.taxref AS t
			ON t.cd_nom = s.cd_nom
		JOIN gn_meta.t_datasets AS d
			ON d.id_dataset = s.id_dataset
		JOIN gn_synthese.t_sources AS sources
			ON sources.id_source = s.id_source
    WHERE s.the_geom_4326 IS NOT NULL
        AND s.the_geom_4326 != ''
	ORDER BY s.id_synthese
	--LIMIT 100000
	--OFFSET 0
;

CREATE UNIQUE INDEX idx_sfwa_id_synthese_cd_nom ON gn_synthese.v_synthese_for_web_app_full USING btree (id_synthese) INCLUDE (cd_nom);
CREATE INDEX idx_sfwa_altitude_max ON gn_synthese.v_synthese_for_web_app_full USING btree (altitude_max);
CREATE INDEX idx_sfwa_altitude_min ON gn_synthese.v_synthese_for_web_app_full USING btree (altitude_min);
CREATE INDEX idx_sfwa_cd_nom ON gn_synthese.v_synthese_for_web_app_full USING btree (cd_nom);
CREATE INDEX idx_sfwa_date_max ON gn_synthese.v_synthese_for_web_app_full USING btree (date_max DESC);
CREATE INDEX idx_sfwa_date_min ON gn_synthese.v_synthese_for_web_app_full USING btree (date_min DESC);
CREATE INDEX idx_sfwa_id_dataset ON gn_synthese.v_synthese_for_web_app_full USING btree (id_dataset);
CREATE INDEX idx_sfwa_id_sources ON gn_synthese.v_synthese_for_web_app_full USING btree (id_source);
CREATE INDEX idx_sfwa_the_geom_4326 ON gn_synthese.v_synthese_for_web_app_full USING spgist (the_geom_4326);

CREATE INDEX idx_sfwa_geojson ON gn_synthese.v_synthese_for_web_app_full USING gist (st_asgeojson);
CREATE INDEX idx_sfwa_observers ON gn_synthese.v_synthese_for_web_app_full USING btree (observers);
CREATE INDEX idx_sfwa_id_acquisition_framework ON gn_synthese.v_synthese_for_web_app_full USING btree (id_acquisition_framework);
CREATE INDEX idx_sfwa_organisms ON gn_synthese.v_synthese_for_web_app_full USING gin (organisms);
CREATE INDEX idx_sfwa_municipalities ON gn_synthese.v_synthese_for_web_app_full USING gin (municipalities);
CREATE INDEX idx_sfwa_departements ON gn_synthese.v_synthese_for_web_app_full USING gin (departements);
--CREATE INDEX idx_sfwa_m5 ON gn_synthese.v_synthese_for_web_app_full USING gist (m5);
--CREATE INDEX idx_sfwa_m10 ON gn_synthese.v_synthese_for_web_app_full USING gist (m10);

CREATE INDEX idx_sfwa_id_digitiser ON gn_synthese.v_synthese_for_web_app_full USING btree (id_digitiser);
CREATE INDEX idx_sfwa_id_module ON gn_synthese.v_synthese_for_web_app_full USING btree (id_module);
CREATE INDEX idx_sfwa_id_nomenclature_bio_condition ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_bio_condition);
CREATE INDEX idx_sfwa_id_nomenclature_bio_status ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_bio_status);
-- CREATE INDEX idx_sfwa_id_nomenclature_biogeo_status ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_biogeo_status);
CREATE INDEX idx_sfwa_id_nomenclature_blurring ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_blurring);
CREATE INDEX idx_sfwa_id_nomenclature_determination_method ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_determination_method);
CREATE INDEX idx_sfwa_id_nomenclature_diffusion_level ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_diffusion_level);
CREATE INDEX idx_sfwa_id_nomenclature_exist_proof ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_exist_proof);
CREATE INDEX idx_sfwa_id_nomenclature_geo_object_nature ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_geo_object_nature);
-- CREATE INDEX idx_sfwa_id_nomenclature_id_nomenclature_grp_typ ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_id_nomenclature_grp_typ);
CREATE INDEX idx_sfwa_id_nomenclature_info_geo_type ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_info_geo_type);
CREATE INDEX idx_sfwa_id_nomenclature_life_stage ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_life_stage);
CREATE INDEX idx_sfwa_id_nomenclature_obj_count ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_obj_count);
CREATE INDEX idx_sfwa_id_nomenclature_obs_technique ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_obs_technique);
CREATE INDEX idx_sfwa_id_nomenclature_observation_status ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_observation_status);
CREATE INDEX idx_sfwa_id_nomenclature_sensitivity ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_sensitivity);
CREATE INDEX idx_sfwa_id_nomenclature_sex ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_sex);
CREATE INDEX idx_sfwa_id_nomenclature_source_status ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_source_status);
CREATE INDEX idx_sfwa_id_nomenclature_type_count ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_type_count);
CREATE INDEX idx_sfwa_id_nomenclature_valid_status ON gn_synthese.v_synthese_for_web_app_full USING btree (id_nomenclature_valid_status);
CREATE INDEX idx_sfwaf2_geojson ON gn_synthese.v_synthese_for_web_app_full USING gin ("st_asgeojson");
</code>

==== Annexe 20 - Ressources Web ====
  * Module externe de test : [[https://github.com/cbn-alpin/gn_module_syntests|Syntests]]
  * Listes des tickets GeoNature traitant du sujet :
    * [[https://github.com/PnX-SI/GeoNature/issues/1093| #1093 - Synthèse : affichage de mailles et observations géo-confidentielles]]
    * [[https://github.com/PnX-SI/GeoNature/issues/560| #560 - Synthèse - Travail sur les performances]]
    * [[https://github.com/PnX-SI/GeoNature/issues/559| #559 - Synthese: ajout plugin Leaflet clusterMarker]] 
    * [[https://github.com/PnX-SI/GeoNature/issues/728| #728 - CARTO - permettre l'affichage de plusieurs couches simultanément]]
  * Sources de données :
    * [[https://openmaptiles.org/about/| Ressources proposées par OpenMapTiles]]
    * [[https://guides.data.gouv.fr/reutiliser-des-donnees/utiliser-les-api-geographiques/utiliser-les-tuiles-vectorielles| Présentation des tuiles vecteurs proposée par Etalab]]
    * [[https://geoservices.ign.fr/documentation/services/api-et-services-ogc/tuiles-vectorielles-tmswmts/styles| Tuiles et styles proposés par IGN]]
  * Amélioration des index spatiaux :
    * [[https://www.crunchydata.com/blog/the-many-spatial-indexes-of-postgis| (The Many) Spatial Indexes of PostGIS]] : Paul Ramsey, 5 mai 2021.
    * [[https://www.linkedin.com/advice/0/what-best-practices-simplifying-geometries-reducing-attributes| Quelles sont les meilleures pratiques pour simplifier les géométries et réduire les attributs dans PostGIS pour les tuiles vectorielles ?]] : IA et communauté Linkedin.
    * [[https://www.alibabacloud.com/blog/postgresql-best-practices-selection-and-optimization-of-postgis-spatial-indexes-gist-brin-and-r-tree_597034| PostgreSQL Best Practices: Selection and Optimization of PostGIS Spatial Indexes (GiST, BRIN, and R-tree)]] : Digoal, 18 décembre 2020.
    * [[https://symphony.is/about-us/blog/boosting-postgis-performance| Boosting PostGIS Performance]] : Symphony, 7 mars 2022.
    * [[https://blog.cleverelephant.ca/2021/05/indexes-and-queries.html| Spatial Indexes and Bad Queries]] : Paul Ramsey, 4 mai 2021.
    * [[https://blog.cleverelephant.ca/2018/09/postgis-external-storage.html| 5x Faster Spatial Join with this One Weird Trick]] : Paul Ramsey, 28 septembre 2018. 

==== Annexe 21 - Analyse du nombres d'observation max par communes et taxons ====
Requêtes Communes :
<code sql>
SELECT 
	a.area_name, 
	a.area_code,
	COUNT(s.id_synthese) AS obs_nbr
FROM gn_synthese.synthese AS s
	JOIN gn_synthese.cor_area_synthese AS cas
		ON (cas.id_synthese = s.id_synthese AND cas.area_type_code = 'COM')
	JOIN ref_geo.l_areas AS a 
		ON cas.id_area = a.id_area 	
GROUP BY a.area_name, a.area_code 
ORDER BY obs_nbr DESC ;

SELECT 
	a.area_name, 
	a.area_code,
	COUNT(s.id_synthese) AS obs_nbr
FROM gn_synthese.synthese AS s
	JOIN gn_synthese.cor_area_synthese AS cas
		ON cas.id_synthese = s.id_synthese 
	JOIN ref_geo.l_areas AS a 
		ON (cas.id_area = a.id_area AND a.id_type = ref_geo.get_id_area_type_by_code('COM'))
GROUP BY a.area_name, a.area_code 
ORDER BY obs_nbr DESC ;
</code>
Résultats [[fonctionnalites:geonature:synthese-amelioration-performance-test:obs-nbr-communes|pour les Communes]] sur la base SINP AURA de 23 millions de données du 2024-05-20 : 
  * max d'observation pour une commune : 245 043

Requêtes Taxons :
<code sql>
SELECT 
	t2.cd_nom AS sciname_code,
	t2.lb_nom AS sciname, 
	t2.nom_vern AS vernaname,
	COUNT(s.id_synthese) AS obs_nbr
FROM gn_synthese.synthese AS s
	JOIN taxonomie.taxref AS t1
		ON t1.cd_nom = s.cd_nom 
	JOIN taxonomie.taxref AS t2 
		ON t2.cd_nom = t1.cd_ref	
GROUP BY t2.lb_nom, t2.cd_nom
ORDER BY obs_nbr DESC 
LIMIT 2000 ;
</code>
Résultats [[fonctionnalites:geonature:synthese-amelioration-performance-test:obs-nbr-taxons|pour les Taxons]] sur la base SINP AURA de 23 millions de données du 2024-05-20 : 
  * taxon avec le max d'observation : 613 465 obs
  * taxons avec plus de 150 000 obs : 25 / 32 465 taxons

==== Annexe 22 - Test utilisation de mailles 20km et 50km ====
  * Source des mailles :  https://inpn.mnhn.fr/telechargement/cartes-et-information-geographique
  * Procédure : 
    * Télécharger manuellement [[https://inpn.mnhn.fr/telechargement/cartes-et-information-geographique| les archives Zip]]
    * Extraire manuellement les archives
    * Utiliser [[https://postgis.net/docs/using_postgis_dbmanagement.html#shp2pgsql_usage| Shp2pgsql]] : 
<color #ed1c24>Note : le paquet postgis doit être isntallé pour pouvoir utiliser Shp2pgsql.</color>
      * M20 : ''shp2pgsql METROP_L9320X20.shp ref_geo.m20 | psql -h localhost -p 5432 -U geonatadmin -d gn2_default''
      * M50 : ''shp2pgsql METROP_L9350X50.shp ref_geo.m50 | psql -h localhost -p 5432 -U geonatadmin -d gn2_default''
    * Insérer les mailles M20 :<code sql>
INSERT INTO ref_geo.bib_areas_types (
	type_name,
	type_code,
	type_desc,
	ref_name,
	ref_version,
	num_version,
	size_hierarchy
) VALUES (
	'Mailles20*20',
	'M20',
	'Type maille INPN 20*20km',
	NULL,
	16082021,
	NULL,
	20000
);

INSERT INTO ref_geo.l_areas (
	id_type,
	area_name,
	area_code,
	geom,
	geom_4326,
	centroid,
	"source",
	"comment",
	"enable",
	additional_data
)
	SELECT DISTINCT
		ref_geo.get_id_area_type('M20'),
		m.cd_sig,
		m.code_20km,
		m.geom,
		st_transform(ST_SetSRID(m.geom, 2154), 4326),
		st_centroid(m.geom),
		'INPN',
		NULL,
		TRUE,
		NULL
	FROM ref_geo.m20 AS m 
		JOIN ref_geo.tmp_subdivided_areas AS a 
			ON (m.geom && a.geom AND a.type_code = 'SINP') ;

CREATE TABLE gn_synthese.cor_area_synthese_m20 PARTITION OF gn_synthese.cor_area_synthese_partitioned
	    FOR VALUES IN ('M20');
</code>
    * Insérer les mailles M50 :<code sql>
INSERT INTO ref_geo.bib_areas_types (
	type_name,
	type_code,
	type_desc,
	ref_name,
	ref_version,
	num_version,
	size_hierarchy
) VALUES (
	'Mailles50*50',
	'M50',
	'Type maille INPN 50*50km',
	NULL,
	16082021,
	NULL,
	50000
);


INSERT INTO ref_geo.l_areas (
	id_type,
	area_name,
	area_code,
	geom,
	geom_4326,
	centroid,
	"source",
	"comment",
	"enable",
	additional_data
)
	SELECT DISTINCT
		ref_geo.get_id_area_type('M50'),
		m.cd_sig,
		m.code_50km,
		m.geom,
		st_transform(ST_SetSRID(m.geom, 2154), 4326),
		st_centroid(m.geom),
		'INPN',
		NULL,
		TRUE,
		NULL
	FROM ref_geo.m50 AS m 
            JOIN ref_geo.tmp_subdivided_areas AS a 
                ON (m.geom && a.geom AND a.type_code = 'SINP') ; 

CREATE TABLE gn_synthese.cor_area_synthese_m50 PARTITION OF gn_synthese.cor_area_synthese_partitioned
	    FOR VALUES IN ('M50');
</code>
  * Si besoins mettre à jour le champ ''area_type_code'' : <code sql>
WITH m20 AS (
	SELECT id_area 
	FROM ref_geo.l_areas 
	WHERE id_type = ref_geo.get_id_area_type('M20')
)
UPDATE gn_synthese.cor_area_synthese AS cas SET
	area_type_code = 'M20'
FROM m20
WHERE cas.id_area = m20.id_area;

WITH m50 AS (
	SELECT id_area 
	FROM ref_geo.l_areas 
	WHERE id_type = ref_geo.get_id_area_type('M50')
)
UPDATE gn_synthese.cor_area_synthese AS cas SET
	area_type_code = 'M50'
FROM m50
WHERE cas.id_area = m50.id_area;
</code>
  * Activer seulement les mailles M20 de la région SINP : <code sql>
UPDATE ref_geo.l_areas AS a
	SET "enable" = FALSE
WHERE a.id_type = ref_geo.get_id_area_type('M20');

UPDATE ref_geo.l_areas AS a
	SET "enable" = TRUE
FROM ref_geo.tmp_subdivided_areas AS sa 
WHERE a.id_type = ref_geo.get_id_area_type('M20') 
	AND a.geom && sa.geom 
	AND sa.type_code = 'SINP'; 
</code>
  * Activer seulement les mailles M50 de la région SINP :<code sql>
UPDATE ref_geo.l_areas AS a
	SET "enable" = FALSE
WHERE a.id_type = ref_geo.get_id_area_type('M50');

UPDATE ref_geo.l_areas AS a
	SET "enable" = TRUE
FROM ref_geo.tmp_subdivided_areas AS sa 
WHERE a.id_type = ref_geo.get_id_area_type('M50') 
	AND a.geom && sa.geom 
	AND sa.type_code = 'SINP';
</code>

==== Annexe 23 - Notes sur les outils utilisés durant les tests ====

=== Utilisation de pg_stat_statements ===
  * Ressource : [[https://docs.postgresql.fr/15/pgstatstatements.html| Doc Postgresql 15 "pg_stat_statements", en français]]
  * ''pg_stat_statements'' est une extension Postgresql qui permet d'avoir un historique des requêtes lancés, leur fréquence, durée...
    * Étant données que les index spécialisées doivent être créé en fonction des requêtes les plus fréquentes, cet outil va nous servir à les identifiés et suivre leurs évolutions.
  * Procédure de mise en place :
    * Définir les paramètres de config Postgresql suivant : <code>
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all
</code>
    * Ajouter l'extension suivante à la base de données à surveiller : <code sql>CREATE EXTENSION pg_stat_statements ;</code>
    * Vous pourrez ensuite consultez les requêtes exécutés avec : <code sql>
SELECT pd.datname, pr.rolname, pss.query, pss.calls, (pss.mean_exec_time/1000) AS sec_mean_exec_time
FROM pg_stat_statements AS pss
	JOIN pg_roles AS pr ON (pr."oid" = pss.userid)
	JOIN pg_catalog.pg_database AS pd ON (pd."oid" = pss.dbid)
WHERE pr.rolname = 'geonatadmin'
	AND pd.datname = 'gn2_default' 
	AND pss.mean_exec_time > 500
ORDER BY pss.calls DESC, pss.mean_exec_time DESC;
</code>
  * ''pg_stat_statements'' permet de connaître les requêtes les plus fréquentes, ce qui permet ensuite de les analyser avec ''EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)'' et [[l'outil en ligne Explain de Dalibo| https://explain.dalibo.com/]].
  * Voir aussi [[https://www.crunchydata.com/blog/cleaning-up-your-postgres-database| cet article pour identifiez les index inutilisés]].

=== Test du serveur de fond carto : Tileserver-GL ==
  * Utiliser Docker pour installer le TileServer GL. Debian ne possède pas nativement des paquets pour toutes les dépendances.

=== Debugger de tuiles vecteurs ===
  * [[https://chromewebstore.google.com/detail/mfikcokdfehaofebfhoehbajfgbofdpk| Extension Chrome Mapbox Vector Tile]] : ajoute un onglet dans les outils développeur]]
  * [[https://stevage.github.io/vector-inspector/| Vector Inspector]]
  * [[https://openlayers.org/en/latest/examples/canvas-tiles.html| Affichage z,x,y des tuiles - OpenLayers]]
  * [[https://onthegomap.github.io/planetiler-demo/| Affichage z,x,y des tuiles - Maplibre Planetiler]]

=== Framework carto web Maplibre ===
  * Création des outils de base de visualisation de la carte  très facile : zoom, passage en 3D, geolocalisation, 
  * Liste des plugins Maplibre GL : https://maplibre.org/maplibre-gl-js/docs/plugins/
    * Plugin d'outils d'édition //Mapbox-Gl-Draw// : https://github.com/mapbox/mapbox-gl-draw
      * Comment ajouter un mode (fonctionnalité d'édition) : 
      * Commenta ajouter une icone d'outil :
      * Mode édition de rectangle :
      * Mode édition de cercle : 
    * Plugin d'ajout de légende : https://github.com/watergis/mapbox-gl-legend
    * Plugin de gestion de couches et de leur opacité : https://github.com/mug-jp/maplibre-gl-opacity
    * Plugin tuiles GeoJson : https://github.com/mkeller3/mapbox-gl-ogc-feature-collection
    * Plugin d'inspection des features (debug) : https://github.com/maplibre/maplibre-gl-inspect
  * Intégration de Mapblibre avec Angular "@maplibre/ngx-maplibre-gl" : https://maplibre.org/ngx-maplibre-gl/demo/display-map
    * Documentation API Angular "@maplibre/ngx-maplibre-gl" : https://maplibre.org/ngx-maplibre-gl/doc
    * Code source des exemples Maplibre et Angular : https://github.com/maplibre/ngx-maplibre-gl/tree/c04efff199c1d21016c9bd2d6ba774286c5c3e77/projects/showcase/src/app/demo/examples

==== Annexe 24 - Test d'utilisation de mailles hexagonales ====
  * Ressources : 
    * [[https://www.crunchydata.com/blog/tile-serving-with-dynamic-geometry| Tile Serving with Dynamic Geometry]] : Paul Ramsey, 24 mars 2020.
    * [[https://medium.com/geolytix/fishnets-honeycombs-and-footballs-better-spatial-models-with-hexagonal-grids-768bdf92d3bb| Fishnets, Honeycombs and Footballs; Better spatial models with hexagonal grids]] : Dennis Bauszus, 3 mars 2017.
    * [[https://medium.com/geolytix/hex-grid-algorithm-for-postgis-4ac45f61d093| Hex grid algorithm for PostGIS]] : Dennis Bauszus, 3 novembre 2017.

Code utilisé :
<code sql>
CREATE OR REPLACE
FUNCTION
public.hexagon(i integer, j integer, edge float8)
RETURNS geometry
AS $$
DECLARE
h float8 := edge*cos(pi()/6.0);
cx float8 := 1.5*i*edge;
cy float8 := h*(2*j+abs(i%2));
BEGIN
RETURN ST_MakePolygon(ST_MakeLine(ARRAY[
            ST_MakePoint(cx - 1.0*edge, cy + 0),
            ST_MakePoint(cx - 0.5*edge, cy + -1*h),
            ST_MakePoint(cx + 0.5*edge, cy + -1*h),
            ST_MakePoint(cx + 1.0*edge, cy + 0),
            ST_MakePoint(cx + 0.5*edge, cy + h),
            ST_MakePoint(cx - 0.5*edge, cy + h),
            ST_MakePoint(cx - 1.0*edge, cy + 0)
         ]));
END;
$$
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
PARALLEL SAFE;

CREATE OR REPLACE
FUNCTION public.hexagonCoordinates(bounds geometry, edge float8,
                            OUT i integer, OUT j integer)
RETURNS SETOF record
AS $$
    DECLARE
        h float8 := edge*cos(pi()/6);
        mini integer := floor(st_xmin(bounds) / (1.5*edge));
        minj integer := floor(st_ymin(bounds) / (2*h));
        maxi integer := ceil(st_xmax(bounds) / (1.5*edge));
        maxj integer := ceil(st_ymax(bounds) / (2*h));
    BEGIN
    FOR i, j IN
    SELECT a, b
    FROM generate_series(mini, maxi) a,
         generate_series(minj, maxj) b
    LOOP
       RETURN NEXT;
    END LOOP;
    END;
$$
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
PARALLEL SAFE;

CREATE OR REPLACE
FUNCTION public.tileHexagons(z integer, x integer, y integer, step integer,
                      OUT geom geometry(Polygon, 3857), OUT i integer, OUT j integer)
RETURNS SETOF record
AS $$
    DECLARE
        bounds geometry;
        maxbounds geometry := ST_TileEnvelope(0, 0, 0);
        edge float8;
    BEGIN
    bounds := ST_TileEnvelope(z, x, y);
    edge := (ST_XMax(bounds) - ST_XMin(bounds)) / pow(2, step);
    FOR geom, i, j IN
    SELECT ST_SetSRID(hexagon(h.i, h.j, edge), 3857), h.i, h.j
    FROM hexagoncoordinates(bounds, edge) h
    LOOP
       IF maxbounds ~ geom AND bounds && geom THEN
            RETURN NEXT;
         END IF;
     END LOOP;
     END;
$$
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
PARALLEL SAFE;

CREATE OR REPLACE
FUNCTION public.hexagons(z integer, x integer, y integer, step integer default 4)
RETURNS bytea
AS $$
WITH
bounds AS (
    -- Convert tile coordinates to web mercator tile bounds
    SELECT ST_TileEnvelope(z, x, y) AS geom
 ),
 rows AS (
    -- All the hexes that interact with this tile
    SELECT h.i, h.j, h.geom
    FROM TileHexagons(z, x, y, step) h
 ),
 mvt AS (
     -- Usual tile processing, ST_AsMVTGeom simplifies, quantizes,
     -- and clips to tile boundary
    SELECT ST_AsMVTGeom(rows.geom, bounds.geom) AS geom,
           rows.i, rows.j
    FROM rows, bounds
)
-- Generate MVT encoding of final input record
SELECT ST_AsMVT(mvt, 'hexagons') FROM mvt
$$
LANGUAGE 'sql'
STABLE
STRICT
PARALLEL SAFE;

COMMENT ON FUNCTION public.hexagons IS 'Hex coverage dynamically generated. Step parameter determines how approximately many hexes (2^step) to generate per tile.';

-- Test de requête
-- 10, 523, 369
WITH bounds AS (
    -- Convert tile coordinates to web mercator tile bounds
    SELECT ST_TileEnvelope(11, 1046, 739) AS geom
),
rows AS (
    -- All the hexes that interact with this tile
    SELECT
        h.i,
        h.j,
        h.geom,
        st_transform(h.geom, 4326) AS geom_4326
    FROM tileHexagons(9, 261, 184, 4) AS h
 ),
observations AS (
    SELECT
        r.geom,
        r.i,
        r.j,
        COUNT(s.id_synthese) AS nbr
    FROM gn_synthese.synthese AS s
        JOIN "rows" AS r
            ON st_intersects(s.the_geom_4326, r.geom_4326)
    GROUP BY r.geom, r.i, r.j
)
-- Usual tile processing, ST_AsMVTGeom simplifies, quantizes,
 -- and clips to tile boundary
SELECT
    ST_AsMVTGeom(o.geom, b.geom) AS geom,
    o.i,
    o.j,
    o.nbr
FROM observations AS o, bounds AS b 
WHERE ST_AsMVTGeom(o.geom, b.geom) IS NOT NULL;
</code>


**Résultats** : l'agrégation d'observations sur des mailles hexagonales générées dynamiquement est assez rapide aux niveaux de zoom 10 et plus. 

==== Annexe 25 - Tests de requête SQL d'aggrégation ====
<code sql>
SELECT 
   t.id_area,
   t.area_code,
   t.area_name,
   t.geom
FROM ref_geo.l_areas AS t
WHERE ST_Intersects(t.geom, ST_Transform(ST_TileEnvelope(12, 2117, 1479), 2154))
	AND t.id_type = ref_geo.get_id_area_type('COM');

SELECT public.squares(12, 2117, 1479);

-- Communes
SELECT ST_TileEnvelope(12, 2117, 1479) AS envelope; -- 9 row(s) fetched - 0,010s (0,001s fetch), on 2024-03-19 at 16:32:36
SELECT ST_TileEnvelope(9, 264, 184) AS envelope; -- 155 row(s) fetched - 0,073s (0,019s fetch), on 2024-03-19 at 16:32:17
SELECT ST_TileEnvelope(8, 132, 92) AS envelope; -- 408 row(s) fetched - 0,188s (0,071s fetch), on 2024-03-19 at 16:36:21
SELECT ST_TileEnvelope(7, 66, 46) AS envelope; -- 873 row(s) fetched - 0,366s (0,097s fetch), on 2024-03-19 at 16:35:29
SELECT ST_TileEnvelope(6, 33, 23) AS envelope; -- 1311 row(s) fetched - 0,490s (0,113s fetch), on 2024-03-19 at 16:37:22
SELECT ST_TileEnvelope(6, 32, 23) AS envelope; -- 6763 row(s) fetched - 1s (0,439s fetch), on 2024-03-19 at 16:37:55
SELECT ST_TileEnvelope(5, 16, 11) AS envelope; -- 24849 row(s) fetched - 4s (0,924s fetch), on 2024-03-19 at 16:31:44
SELECT ST_TileEnvelope(4, 8, 5) AS envelope; -- 30521 row(s) fetched - 4s (0,690s fetch), on 2024-03-19 at 16:34:23

WITH bounds AS (
	SELECT ST_TileEnvelope(8, 132, 92) AS envelope
)
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), b.envelope) AS geom, 
       t.id_area,
       t.area_code,
       t.area_name
FROM ref_geo.l_areas AS t, bounds AS b
WHERE t.geom && ST_Transform(b.envelope, 2154)
	AND t.id_type = ref_geo.get_id_area_type('COM') ;

-- Maille M1
SELECT ST_TileEnvelope(12, 2117, 1479) AS envelope; -- 72 row(s) fetched - 0,003s (0,001s fetch), on 2024-03-19 at 16:57:41
SELECT ST_TileEnvelope(8, 132, 92) AS envelope; -- 13161 row(s) fetched - 0,211s (0,079s fetch), on 2024-03-19 at 16:59:17
SELECT ST_TileEnvelope(4, 8, 5) AS envelope; -- 481502 row(s) fetched - 6s (3s fetch), on 2024-03-19 at 17:00:30

WITH bounds AS (
	SELECT ST_TileEnvelope(4, 8, 5) AS envelope
)
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), b.envelope) AS geom, 
       t.id_area,
       t.area_code,
       t.area_name
FROM ref_geo.l_areas AS t, bounds AS b
WHERE t.geom && ST_Transform(b.envelope, 2154)
	AND t.id_type = ref_geo.get_id_area_type('M1') ;


-- Maille M5
SELECT ST_TileEnvelope(12, 2117, 1479) AS envelope; -- 6 row(s) fetched - 0,005s (0,001s fetch), on 2024-03-19 at 16:58:06
SELECT ST_TileEnvelope(8, 132, 92) AS envelope; --  533 row(s) fetched - 0,021s (0,006s fetch), on 2024-03-19 at 16:58:32
SELECT ST_TileEnvelope(4, 8, 5) AS envelope; -- 18856 row(s) fetched - 0,329s (0,119s fetch), on 2024-03-19 at 17:01:26

WITH bounds AS (
	SELECT ST_TileEnvelope(4, 8, 5) AS envelope
)
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), b.envelope) AS geom, 
       t.id_area,
       t.area_code,
       t.area_name
FROM ref_geo.l_areas AS t, bounds AS b
WHERE t.geom && ST_Transform(b.envelope, 2154)
	AND t.id_type = ref_geo.get_id_area_type('M5') ;

-- Maille M10
SELECT ST_TileEnvelope(12, 2117, 1479) AS envelope; -- 2 row(s) fetched - 0,002s, on 2024-03-19 at 17:03:21
SELECT ST_TileEnvelope(8, 132, 92) AS envelope; -- 156 row(s) fetched - 0,005s (0,001s fetch), on 2024-03-19 at 17:02:58
SELECT ST_TileEnvelope(4, 8, 5) AS envelope; -- 6013 row(s) fetched - 0,088s (0,036s fetch), on 2024-03-19 at 17:02:07

WITH bounds AS (
	SELECT ST_TileEnvelope(12, 2117, 1479) AS envelope
)
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), b.envelope) AS geom, 
       t.id_area,
       t.area_code,
       t.area_name
FROM ref_geo.l_areas AS t, bounds AS b
WHERE t.geom && ST_Transform(b.envelope, 2154)
	AND t.id_type = ref_geo.get_id_area_type('M10') ;

WITH bounds AS (
	SELECT ST_TileEnvelope(12, 2117, 1479) AS envelope
),
observations AS (
	SELECT 
		l.area_code AS code,
		ST_Transform(l.geom, 3857) AS geom,
		COUNT(s.id_synthese) AS nbr
	FROM gn_synthese.synthese AS s 
		JOIN gn_synthese.cor_area_synthese AS cas 
			ON s.id_synthese = cas.id_synthese 
		JOIN ref_geo.l_areas AS l
			ON l.id_area = cas.id_area 
		, bounds AS b
	WHERE l.geom && ST_Transform(b.envelope, 2154)
		AND l.id_type = ref_geo.get_id_area_type('M10')
	GROUP BY l.area_code, l.geom
)
SELECT ST_AsMVTGeom(o.geom, b.envelope) AS geom, 
       o.nbr,
       o.code 
FROM observations AS o, bounds AS b ;

WITH bounds AS (
	SELECT ST_TileEnvelope(9, 264, 184) AS envelope
)
SELECT 
	l.area_code AS code,
	ST_Transform(l.geom, 3857) AS geom,
	COUNT(s.id_synthese) AS nbr
FROM gn_synthese.synthese AS s 
	JOIN gn_synthese.cor_area_synthese AS cas 
		ON s.id_synthese = cas.id_synthese 
	JOIN ref_geo.l_areas AS l
		ON l.id_area = cas.id_area 
	, bounds AS b
WHERE l.geom && ST_Transform(b.envelope, 2154)
	AND l.id_type = ref_geo.get_id_area_type('M10')
GROUP BY l.area_code, l.geom ; -- 32 row(s) fetched - 7s (0,001s fetch), on 2024-03-19 at 17:20:06

WITH bounds AS (
	SELECT ST_TileEnvelope(9, 264, 184) AS envelope
)
SELECT l.id_area, l.geom, l.area_code
FROM ref_geo.l_areas AS l, bounds AS b
WHERE l.geom && ST_Transform(b.envelope, 2154)
	AND l.id_type = ref_geo.get_id_area_type('M10') ; -- 32 row(s) fetched - 0,007s (0,001s fetch), on 2024-03-19 at 17:29:37

WITH bounds AS (
	SELECT ST_TileEnvelope(9, 264, 184) AS envelope
),
m10 AS (
	SELECT 
		l.id_area, 
		l.geom, 
		l.area_code AS code
	FROM ref_geo.l_areas AS l, bounds AS b
	WHERE l.geom && ST_Transform(b.envelope, 2154)
		AND l.id_type = ref_geo.get_id_area_type('M10')
)
SELECT 
	m10.code,
	ST_Transform(m10.geom, 3857) AS geom,
	COUNT(s.id_synthese) AS nbr
FROM gn_synthese.synthese AS s
	JOIN gn_synthese.cor_area_synthese AS cas 
		ON s.id_synthese = cas.id_synthese
	JOIN m10 
		ON cas.id_area = m10.id_area
GROUP BY m10.code, m10.geom ; -- 32 row(s) fetched - 7s, on 2024-03-19 at 17:39:22

WITH bounds AS (
	SELECT ST_TileEnvelope(9, 264, 184) AS envelope
),
m10 AS (
	SELECT l.id_area, l.geom, l.area_code AS code
	FROM ref_geo.l_areas AS l, bounds AS b
	WHERE l.geom && ST_Transform(b.envelope, 2154)
		AND l.id_type = ref_geo.get_id_area_type('M10')
)
SELECT 
	m10.code,
	ST_Transform(m10.geom, 3857) AS geom,
	COUNT(s.id_synthese) AS nbr
FROM gn_synthese.synthese AS s
	JOIN m10 
		ON s.the_geom_local && m10.geom
GROUP BY m10.code, m10.geom ; -- 32 row(s) fetched - 27s (0,001s fetch), on 2024-03-19 at 17:35:57


WITH bounds AS (
	SELECT ST_TileEnvelope(8, 132, 92) AS envelope
), mvtgeom AS (
	SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), b.envelope) AS geom, 
	       t.id_area,
	       t.area_code,
	       t.area_name
	FROM ref_geo.l_areas AS t, bounds AS b
	WHERE ST_Intersects(t.geom, ST_Transform(b.envelope, 2154))
		AND t.id_type = ref_geo.get_id_area_type('COM')
)
SELECT ST_AsMVT(mvtgeom.*) FROM mvtgeom ;
</code>