Construction d'APIs Géographiques à base d'Open Data, PostgreSQL & PostGIS

Sébastien Prunier - @sebprunier

Guillaume Soldera - @gsoldera

#DevoxxFR

Construction d'APIs Géographiques à base d'Open Data, PostgreSQL & PostGIS

• Le contexte
• Les Open Data
• Les données géographiques (formats et représentations)
• Le stockage des données
• L’insertion et le requêtage
• La création des API
• Les tests
• Les performances et l’optimisation

#DevoxxFR

Guillaume

Sébastien

Développeur @SerliFr

@gsoldera

Développeur @SerliFr

@sebprunier

Le contexte

« Êtes-vous au courant des risques naturels et technologiques autour de chez vous ? »

Cibles

Le grand public

Les professionnels

• Les agences immobilières
• Les assureurs
• Les collectivités
• Des partenaires B2B

• Les futurs acheteurs immobiliers
• Les vacanciers
• Les curieux :-)

Beaucoup de sources de données

Inconvénients

• Sources de données hétérogènes
• Souvent uniquement de la visualisation via des layers de carte
• Difficultés pour :
• Agréger les données
• Exemple : fournir un résumé des risques pour une adresse
• Croiser les données
• Exemple : trouver des usines Seveso en zone inondable
• Fournir des statistiques

Notre mission

• Regrouper les sources de données (Open Data)
• Construire des API proposant :
• La recherche de données
• pour une adresse
• pour une commune
• pour une zone délimitée (« bounding box »)
• Un design adapté à la visualisation sur des cartes
• Des statistiques
• Des traitements de masse (« bulk »)

Les Open Data

Open Data en France

• Les Open Data sont fournies par des organismes fiables
• Gouvernement, INSEE, IGN …
• COVADIS : COmmission de VAlidation des Données pour l’Information Spatialisée
• La dynamique en France est bonne !
• 18 décembre 2013 : création de data.gouv.fr
• Possibilité d’y contribuer (https://schema.data.gouv.fr/)
• 1er janvier 2017 : ouverture de la base SIRENE (répertoire des entreprises et établissements)
• https://www.gouvernement.fr/action/l-ouverture-des-donnees-publiques

Open Data en France

• https://opendatabarometer.org/ (2017)

Open Data en France

• https://opendatabarometer.org/ (2017)

Open Data en France

• https://oecd.org/ (Index OURData - 2019)

Open Data en France

• Evolution de l’indice OURData entre 2017 et 2019

Les formats de jeux de données géographiques

4 formats principaux

CSV

• « Comma-Separated Value »
• Fichier texte
• Variantes selon le séparateur
• Plus difficile à parser qu’il n’y paraît, privilégiez des libs qui font ça très bien

JSON

• « JavaScript Object Notation »
• Fichier texte
• Permet de représenter une information structurée
• Spécifié selon la RFC 7159

GeoJSON

• « Geographic JSON »
• Fichier texte
• Permet de décrire des données géospatiales selon différentes formes
• Geometry
• Représentation spatiale (point, segment, polygone….)
• Feature
• « Entité » qui a une représentation spatiale
• FeatureCollection
• Liste de Feature
• Spécifié selon la RFC 7946

GeoJSON - Notion de base

• La position d’un point est représentée par un tableau de nombres décimaux
• Premier élément : longitude (ou abscisse)
• Deuxième élément : latitude (ou ordonnée)
• Troisième élément optionnel : élévation du point
• ⚠️ ATTENTION : l’ordre est important !!

GeoJSON - Geometry

{

"type": "Point",

"coordinates": [30, 10]

}

Source : https://fr.wikipedia.org/wiki/GeoJSON

Point

GeoJSON - Geometry

{

"type": "MultiPoint",

"coordinates": [

[10, 40], [40, 30], [20, 20], [30, 10]

]

}

Ensemble de points

Source : https://fr.wikipedia.org/wiki/GeoJSON

GeoJSON - Geometry

{

"type": "LineString",

"coordinates": [

[30, 10], [10, 30], [40, 40]

]

}

Segment

Source : https://fr.wikipedia.org/wiki/GeoJSON

GeoJSON - Geometry

{

"type": "MultiLineString",

"coordinates": [

[

[10, 10], [20, 20], [10, 40]

],

[

[40, 40], [30, 30], [40, 20], [30, 10]

]

]

}

Ensemble de segments

Source : https://fr.wikipedia.org/wiki/GeoJSON

GeoJSON - Geometry

{

"type": "Polygon",

"coordinates": [

[

[30, 10], [40, 40], [20, 40], [10, 20], [30, 10]

]

]

}

​

Polygone

Source : https://fr.wikipedia.org/wiki/GeoJSON

GeoJSON - Geometry

{

"type": "Polygon",

"coordinates": [

[

[35, 10], [45, 45], [15, 40], [10, 20], [35, 10]

],

[

[20, 30], [35, 35], [30, 20], [20, 30]

]

]

}

Polygone (avec trous)

Source : https://fr.wikipedia.org/wiki/GeoJSON

GeoJSON - Geometry

{

"type": "MultiPolygon",

"coordinates": [

[

[

[30, 20], [45, 40], [10, 40], [30, 20]

]

],

[

[

[15, 5], [40, 10], [10, 20], [5, 10], [15, 5]

]

]

]

}

​

Ensemble de polygones

Source : https://fr.wikipedia.org/wiki/GeoJSON

GeoJSON - Feature

• Feature = Geometry + properties
• geometry
• Représentation spatiale de « l’entité »
• properties
• Contient des éléments descriptifs de « l’entité »

{

"type": "Feature",

"geometry": {

"type": "Point",

"coordinates": [102.0, 0.5]

},

"properties": {

"prop": "value"

}

}

GeoJSON - FeatureCollection

• Liste de Feature

{

"type": "FeatureCollection",

"features": [

{

"type": "Feature",

"geometry": {

"type": "Point",

"coordinates": [102.0, 0.5]

},

"properties": {

"prop": "value"

}

},

{

"type": "Feature",

"geometry": {

"type": "LineString",

"coordinates": [

[102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]

]

},

"properties": {

"prop1": 0.0

}

}

]

}

GeoJSON - Visualisation

geojson.io

Shapefile

• Format de fichier pour les Systèmes d’Informations Géographiques (SIG)
• Développé par l’ESRI (Environmental Systems Research Institute)
• Est devenu un standard de fait
• Spécifications ouvertes
• https://www.esri.com/content/dam/esrisites/sitecore-archive/Files/Pdfs/library/whitepapers/pdfs/shapefile.pdf

Shapefile

• Plusieurs fichiers, dont notamment :
• .shp : contient l’information liée à la géométrie de l’objet
• .dbf : contient les attributs relatifs aux objets
• .shx : permet entre autres de faire le lien entre les données (dbf) et la géométrie (shp)
• .prj : système de coordonnées utilisé

Shapefile - Visualisation

QGIS

Shapefile vs. GeoJSON

La représentation des

données géospatiales

Systèmes de coordonnées

• Référentiel qui permet de représenter et situer des éléments dans l’espace
• Les coordonnées peuvent être exprimées en degrés, en mètres…
• Les coordonnées peuvent être exprimées dans des référentiels différents
• « European Petroleum Survey Group » (EPSG) créé en 1985 avec ELF en vue de corriger les informations de localisation
• Définition d’une liste de systèmes de coordonnées géoréférencées et assocation des codes pour les identifier, les « codes EPSG »
• Codes utilisés dans les standards de l’Open Geospatial Consortium et dans la plupart des outils de SIG
• https://epsg.io

Système de coordonnées géographiques

• Localisations directement sur la Terre (modélisée comme une sphère ou une ellipsoïde)
• Longitude - (degrés, radians) ouest/est par rapport à un méridien “d’origine”
• Latitude - (degrés, radians) nord/sud par rapport à l’équateur
• Système très utilisé notamment avec les GPS
• Différents systèmes existent
• WGS84 (World Geodetic System 1984) - EPSG 4326
• RGF93 (Réseau Géodésique Français 1993) - EPSG 4171

EPSG.IO

Système de coordonnées projetées

• Modélise la Terre (ou une partie du globe) sur un plan
• Localisations depuis une origine arbitraire et des axes X et Y
• Exprimées généralement en mètre
• Longueurs, surfaces constants dans les deux dimensions

⇒ Plus facilement exploitables que des valeurs angulaires

⇒ ⚠️ les calculs de distance/surface ne prennent pas en compte la courbure de la Terre

​

Système de coordonnées projetées

• Avant les satellites, pas de système de représentation globale, donc chaque partie du globe avait son système de projection
• Précision uniquement sur la partie du globe “référence”
• Origine du système différente selon les parties du globe
• Un même point a des coordonnées différentes selon le système

Système de coordonnées projetées

(1,1)

Système de coordonnées projetées

(5,3)

Système de coordonnées projetées

• La forme de la surface terrestre est géométriquement imparfaite, il existe donc différentes techniques qui permettent de la représenter sur une surface plane (projection)
• Différents types de projection
• Projection cylindrique
• On projette l’ellipsoïde sur un cylindre qui l’englobe et on déroule le cylindre pour obtenir la carte
• Projection conique
• On projette l'ellipsoïde sur un cône tangent à un cercle ou sécant en deux cercles, puis on déroule le cône pour obtenir la carte
• Projection azimutale

​

Système de coordonnées projetées

Projection

azimutale

Projection

conique

Projection

cylindrique

Projection Mercator (cylindrique)

• Projection tangente à l’équateur
• Planisphère de référence
• Projection dite « conforme » car conservation des angles
• Déformation sur les distances et les surfaces au fur et à mesure qu’on se rapproche des pôles
• Utilisée par GoogleMaps, OpenStreetMap
• EPSG:3857

EPSG.IO

Projection de Peters (cylindrique)

• Projection dite « équivalente » car conservation des proportions des surfaces
• Localement, ne conserve pas les angles donc déformation des continents
• SR-ORG:22

Projection Lambert 93 (conique)

• Projection dite “conforme” car conservation des angles
• Projection officielle française car les déformations sont minimisées pour la France métropolitaine
• EPSG:2154

EPSG.IO

Système de coordonnées

Coordonnées de Paris

⚠️ S’assurer que les données sont dans le même système de projection avant de les traiter

Le stockage des

données géographiques dans PostgreSQL avec PostGIS

PostgreSQL

• Besoin de croiser des données ⇒ Base relationnelle
• On aime bcp PG :-)
• Libre
• Colonnes JSON
• Type ENUM
• Disponible sur notre PaaS favori (Clever Cloud)
• Extension PostGIS
• Gère les données géospatiales

PostGIS

CREATE EXTENSION IF NOT EXISTS postgis;

​

SELECT postgis_full_version();

• PostGIS ajoute de nouvelles fonctionnalité à PostgreSQL :
• « Spatial Types » : geometry / geography
• « Spatial Indexes »
• « Spatial Functions »

Tables « Metadata »

• Open Geospatial Consortium (OGC) a défini un standard pour spécifier le stockage et l’accès à des données géospatiales (https://www.ogc.org/standards/sfa)
• PostGIS se met en conformité avec ce standard via 3 tables :
• spatial_ref_sys
• définit les différents systèmes de références spatiales gérés
• geometry_columns
• vue qui contient les différents champs geometry
• geography_columns
• vue qui contient les différents champs geography

PostGIS - SPATIAL_REF_SYS

SELECT *

FROM spatial_ref_sys;

SRID : Spatial Reference IDentifier

• SRID = Spatial Reference IDentifier
• 1 SRID ⇔ 1 Code EPSG
• Le SRID peut (doit !) être précisé lors de la création des données géospatiales
• ST_GeomFromText('POINT(0 0)', 4326)
• 'SRID=4326;POINT(2.5559 49.0083)'::geometry
• ST_SetSRID(ST_MakePoint(2.5559, 49.0083), 4326)

​

SRID

SELECT

ST_SRID('POINT(2.5559 49.0083)'::geography) as default_srid_geography,

ST_SRID('POINT(2.5559 49.0083)'::geometry) as default_srid_geometry

;

​

-- default_srid_geography : 4326

-- default_srid_geometry : 0

SRID

SELECT ST_Equals(

ST_GeomFromText('POINT(0 0)', 4326),

ST_GeomFromText('POINT(0 0)', 26918)

);

ERROR: ST_Equals: Operation on mixed SRID geometries (Point, 4326) != (Point, 26918)

SRID

• ST_Transform
• ST_SetSRID
• ST_SRID

​

⚠️ Attention : les index sont construits en utilisant les SRID des « geometries » stockées, donc si les transformations de SRID sont faites au moment de la requête, l’index risque de ne pas être utilisé

Geometry vs Geography

• Geometry
• Les coordonnées sont placées sur un plan cartésien et représentent une distance linéaire depuis une origine
• Supporte tous les SRID

​

• Geography
• Les coordonnées sont placées sur un globe et spécifient un angle de rotation depuis un méridien de référence (longitude) et l’angle depuis l’équateur (latitude)
• Fonctionne uniquement avec des systèmes de coordonnées latitude/longitude

Geometry

• Distance entre les aéroports de Paris et Los Angeles

SELECT ST_Distance(

'SRID=4326;POINT(-118.4079 33.9434)'::geometry, -- Los Angeles (LAX)

'SRID=4326;POINT(2.5559 49.0083)'::geometry -- Paris (CDG)

) as distance_geom;

​

-- Résultat : 121.898285970107

Geometry

• Résultat en degrés car le SRID est 4326 (latitude / longitude)

​

Crédits : Boundless - Paul Ramsey

Coordonnées « lat / lon »

• Les degrés ne sont pas une unité de distance ou de surface

Crédits : Boundless - Paul Ramsey

Geography

• Distance entre les aéroports de Paris et Los Angeles

SELECT ST_Distance(

'SRID=4326;POINT(-118.4079 33.9434)'::geography, -- Los Angeles (LAX)

'SRID=4326;POINT(2.5559 49.0083)'::geography -- Paris (CDG)

) as distance_geog;

​

-- Résultat : 9124665.27317673

Geography

• Résultat en mètres

Crédits : Boundless - Paul Ramsey

Geometry

• Distance entre les aéroports de Tokyo et Los Angeles

SELECT ST_Distance(

ST_GeometryFromText('SRID=4326;Point(-118.4079 33.9434)'), -- LAX (Los Angeles)

ST_GeometryFromText('SRID=4326;Point(139.733 35.567)') -- NRT (Tokyo/Narita)

) as distance_geom;

​

-- Résultat : 258.146005837336

⇒ distance 2 fois plus grande que Paris / Los Angeles

Geometry

Crédits : Boundless - Paul Ramsey

Geography

• Distance entre les aéroports de Tokyo et Los Angeles

SELECT ST_Distance(

ST_GeographyFromText('Point(-118.4079 33.9434)'), -- LAX (Los Angeles)

ST_GeographyFromText('Point(139.733 35.567)') -- NRT (Tokyo/Narita)

) as distance_geog;

​

-- Résultat : 8833954.77277118

⇒ distance moins grande que Paris / Los Angeles

Geography

Crédits : Boundless - Paul Ramsey

Geometry - Distances correctes

• ST_DistanceSphere

SELECT

ST_Distance(

ST_GeographyFromText('Point(-118.4079 33.9434)'), -- LAX

ST_GeographyFromText('Point(139.733 35.567)')) -- NRT (Tokyo/Narita)

AS geography_distance,

ST_DistanceSphere(

ST_GeometryFromText('SRID=4326;Point(-118.4079 33.9434)'), -- LAX

ST_GeometryFromText('SRID=4326;Point(139.733 35.567)')) -- NRT (Tokyo/Narita)

AS geometry_distance_sphere;

​

-- Résultat geography_distance : 8833954.77277118

-- Résultat geometry_distance_sphere : 8814948.74627491

Geometry - Distances correctes

• ST_DistanceSpheroid

SELECT

ST_Distance(

ST_GeographyFromText('Point(-118.4079 33.9434)'), -- LAX

ST_GeographyFromText('Point(139.733 35.567)')) -- NRT (Tokyo/Narita)

AS geography_distance,

ST_DistanceSpheroid(

ST_GeometryFromText('SRID=4326;Point(-118.4079 33.9434)'), -- LAX

ST_GeometryFromText('SRID=4326;Point(139.733 35.567)'), -- NRT (Tokyo/Narita)

'SPHEROID["WGS 84",6378137,298.257223563]')

AS geometry_distance_spheroid

;

​

-- Résultat geography_distance : 8833954.77277118

-- Résultat geometry_distance_spheroid : 8833954.772771182

Geometry - Distances correctes

• Utiliser le bon SRID (2154 est celui associé la France Métropolitaine)

SELECT

ST_Distance(

ST_GeographyFromText('SRID=4326;POINT(2.3522219 48.856614)'), -- Paris

ST_GeographyFromText('SRID=4326;POINT(0.340375 46.580224)') -- Poitiers

) as distance_geog,

ST_Distance(

ST_Transform(ST_GeometryFromText('SRID=4326;POINT(2.3522219 48.856614)'), 2154),

ST_Transform(ST_GeometryFromText('SRID=4326;POINT(0.340375 46.580224)'), 2154)

) as distance_geom;

​

-- Résultat distance_geog : 294669.87507824

-- Résultat distance_geom : 294475.09209107

Geometry vs Geography

• Geometry
• Représentation dans un plan Cartésien
• Plus efficace
• Geography
• Représentation dans un système de coordonnées sphériques
• Plus précis dans le calcul de distance notamment (prend en compte la courbure de la Terre)
• Moins de fonctions disponibles
• À utiliser si vous avez besoin de traiter très précisément des relations entre des points dispersés sur le globe

Démo !

• Type Geometry
• Type Geography
• Fonctions de base

https://github.com/guillaumesoldera/opendata-api-geo-pg/tree/main/demo-postgis

#DevoxxFR

Fonctions de base

• ST_GeometryFromText / ST_GeomFromText
• ST_GeomFromGeoJSON
• ST_AsGeoJSON
• ST_AsText
• ST_MakePoint

• ST_Distance (warning sur les units)
• ST_Intersects
• ST_Intersection
• ST_Buffer
• ST_Area (warning sur les units)
• && : bounding boxes overlap
• ST_Valid
• ST_MakeValid
• …

Colonne geography

CREATE TABLE airports (

code VARCHAR(3),

geog GEOGRAPHY

);

​

CREATE TABLE airports (

code VARCHAR(3),

geog GEOGRAPHY(Point)

);

​

INSERT INTO airports VALUES ('LAX', 'POINT(-118.4079 33.9434)');

Colonne geometry

CREATE TABLE airports (

code VARCHAR(3),

geom GEOMETRY

);

​

CREATE TABLE airports (

code VARCHAR(3),

geom GEOMETRY(Point, 4326)

);

​

INSERT INTO airports VALUES ('LAX', 'SRID=4326;POINT(-118.4079 33.9434)');

Vue geometry_columns

SELECT *

FROM geometry_columns;

PostGIS - Insertion

INSERT INTO airports

VALUES (

'LAX',

'SRID=4326;POINT(-118.4079 33.9434)'

);

​

INSERT INTO airports

VALUES (

'CDG',

ST_SetSRID(

ST_GeomFromGeoJSON(

'{"type": "Point", "coordinates": [2.5559, 49.0083]}'

),

4326

)

);

Les indexes

Index

• EXPLAIN ANALYZE
• CREATE INDEX my_spatial_idx ON my_table USING GIST(my_spatial_col)
• Structure Real-Tree (ℝ-Tree)
• Organise les objets dans des « boîtes » (les Bounding Box), « des sous-boîtes », etc

Spatial Index - Fonctionnement

• Les indexes « spatiaux » indexent les bounding box des éléments
• Les opérations (intersections, contains…) sont d’abord faites sur les bounding box puis plus précisément sur les éléments retournés par le premier filtre

Spatial Index - Fonctionnement

Quelle(s) ligne(s) intersecte(nt) l’étoile verte ?

Spatial Index - Fonctionnement

Quelle(s) lignes intersectent l’étoile verte ?

Spatial Index - Fonctionnement

Quelle(s) boîte(s) intersectent la boîte verte ?

Spatial Index - Fonctionnement

Quelle(s) lignes intersectent l’étoile verte ?

Performance sans index

B

C

D

E

F

H

G

I

K

L

M

A

B

C

D

E

F

G

H

I

J

K

L

M

Trouver quel objet contient

A

J

Performance avec index

A

B

C

D

E

F

H

G

I

J

K

L

M

A

B

C

D

E

F

G

H

I

J

K

L

M

Trouver quel objet contient

Performance avec index

T

P

N

C

D

E

A

B

C

D

E

F

G

H

I

J

K

L

M

8 “boîtes” à vérifier

Trouver quel objet contient

P

Q

R

S

T

U

Les formats de représentations

Représentations principales

• Well-Known Text (WKT) / Extended Well-Known Text (EWKT)

POINT(2.3522219 48.856614)

SRID=4326;POINT(2.3522219 48.856614)

• GeoJSON

{"type":"Point","coordinates":[2.5559,49.0083]}

Autres représentations

• GML

<gml:Point>

<gml:coordinates>2.3522219,48.856614</gml:coordinates>

</gml:Point>

• SVG

cx="2.5559" cy="-49.0083"

PostGIS - Formats

La problématique

PostgreSQL

PostGIS

Shapefile

(.shp)

WKT ?

WKB ?

GeoJSON ?

GML ?

❌

✅

?

Les outils de transformation

ogr2ogr

• https://gdal.org/programs/ogr2ogr.html
• Conversion entre différents formats
• Changement de système de référence de coordonnées
• Génération du SQL
• Simplification de contours
• …

ogr2ogr \

-t_srs EPSG:4326

-f GeoJSON cities.json \

cities.shp

Format et fichier cibles

Fichier source

Projection cible

Simplification

• “Rognage” des contours
• Diminue la taille des fichiers sources
• Diminue la taille des GeoJSON générés
• Perte de précision

Précision des coordonnées

Source : https://xkcd.com/2170/

Démo !

• Précisions et Distances
• Simplification

https://github.com/guillaumesoldera/opendata-api-geo-pg/tree/main/demo-precision

#DevoxxFR

Simplification

https://mapshaper.org

⚠️ Transformation & Simplification

SELECT ST_AsGeoJSON(ST_Intersection(cc.contour, i.area))

FROM inondation i

INNER JOIN commune_contour cc ON ST_Intersects(cc.contour, i.area)

WHERE cc.typecom = 'COM' AND cc.com = '65275'

lwgeom_intersection_prec: GEOS Error: TopologyException: Input geom 0 is invalid: Self-intersection at 0.20284285714285696 43.048653571428574

Self-intersection

SELECT ST_IsValidReason(contour), ST_AsGeoJSON(contour)

FROM commune_contour

WHERE ST_IsValid(contour) = false

Self-intersection

ST_MakeValid

• Pour corriger : Polygon → MultiPolygon

UPDATE commune_contour

SET contour = ST_MakeValid(contour)

WHERE ST_IsValid(contour) = false

L’insertion des données

Choix

• Une table dédiée pour chaque « entité » du dataset
• Facilité pour les insertions
• Facilité pour les mises à jour futures
• Si besoin, création de vues pour requêter de manière performante des données croisées
• Le nom des colonnes correspond exactement au champ des datasets
• Clés naturelles lorsque cela est possible

Cas d’usage

• Plan de Prévention des Risques Naturels (PPRN)
• Document d’urbanisme réalisé par les services de l’Etat
• Permet d’identifier les risques naturels prévisibles
• Permet de délimiter les zones exposées
• Par type de risque (submersion marine, cavités…)
• Par type de réglementation (interdiction, prescription…)
• Peut porter sur plusieurs communes

​

• Données disponibles sur Géorisques

PPRN - Données

• document_pprn.csv
• Infos « administratives » par identifiant de PPRN (avec notamment les risques impliqués)
• multirisque_pprn.csv
• Correspondance « identifiant » → « risque » en cas de multi-risques
• zone_pprn.shp
• Zonage des PPRN par type de réglementation
• covadis_standard_PPR_v1.pdf
• COmmission de VAlidation des Données pour l’Information Spatialisée (COVADIS)
• Spécifications du standard de données

Approche

PostgreSQL

data.gouv.fr

schémas, indexes,

contraintes

Approche

• Fichier SQL contenant la création des schémas, des indexes et des contraintes
• Téléchargement du dataset avec curl ou wget
• Transformation des Shapefile en GeoJSON pour ne travailler qu’avec du CSV ou GeoJSON avec ogr2ogr
• Scripts générant les requêtes d’insertion SQL (encadrées par les instructions de transaction) avec node
• Import des différents fichiers SQL avec psql
• Script bash pour automatiser et ordonnancer ces différentes étapes

https://github.com/guillaumesoldera/opendata-api-geo-pg/tree/main/pprn/first-impl

Création des schémas

• Tables mappant les données « utiles » des datasets
• Utilisation de colonne typée geometry
• Création des indexes et des différentes contraintes
• PRIMARY KEYS
• NOT NULL
• FOREIGN KEYS
• UNIQUE
• Documentation
• Commentaires sur les colonnes et les tables
• Structures pour gérer les infos sur les sources de données

Documentation des données

• Gestion des sources de données
• Gestion des fournisseurs des données

Documentation des données

• Lien entre la table et la source de données

Documentation des données

INSERT INTO Data_Provider(id, name, url, description)

VALUES ('ministere-de-la-transition-ecologique',

'Ministère de la Transition écologique',

'https://www.ecologie.gouv.fr/',

NULL);

​

INSERT INTO Data_Source(id, name, url, description, publication_date, data_provider_id)

VALUES ('georisques',

'Géorisques',

'https://www.georisques.gouv.fr/donnees/bases-de-donnees',

'Géorisques est le site de référence sur les risques majeurs naturels et technologiques',

'2020-09-01',

'ministere-de-la-transition-ecologique');

​

INSERT INTO Table_Data_Source(table_name, data_source_id) VALUES ('zone_pprn', 'georisques');

Documentation des données

COMMENT ON TABLE zone_pprn IS 'Liste des zones des PPRN';

​

COMMENT ON COLUMN zone_pprn.id_zone IS 'Identifiant de la zone PPRN';

COMMENT ON COLUMN zone_pprn.id_gaspar IS 'Identifiant GASPAR du PPRN';

COMMENT ON COLUMN zone_pprn.nom IS 'Nom de la zone (intitulé exact figurant dans le règlement du PPRN)';

COMMENT ON COLUMN zone_pprn.codezone IS 'Code de la zone (intitulé exact figurant dans le règlement du PPRN)';

COMMENT ON COLUMN zone_pprn.typereg IS 'Type standardisé caractérisant la nature de la réglementation prévalant sur la zone';

COMMENT ON COLUMN zone_pprn.urlfic IS 'URL permettant d''accéder depuis le web au fichier contenant le règlement de la zone';

COMMENT ON COLUMN zone_pprn.area IS 'Zone géographique du PPRN';

• Commentaire des données

Création des schémas

CREATE TABLE zone_pprn

(

id_zone TEXT NOT NULL PRIMARY KEY,

id_gaspar character varying(20) NOT NULL,

nom TEXT,

codezone TEXT,

typereg character varying(2) NOT NULL,

urlfic TEXT,

area geometry NOT NULL

);

​

CREATE INDEX zone_pprn_id_gaspar_idx ON zone_pprn (id_gaspar);

CREATE INDEX zone_pprn_area_idx ON zone_pprn USING gist (area);

Insertion des data

• Script node qui streame le fichier
• Parsing de chaque ligne
• Génération d’une instruction INSERT au bout de plusieurs lignes
• Ecriture de la requête sur la sortie standard

​

​

• Exécution du script node avec redirection de la sortie standard dans un fichier SQL
• Import du fichier SQL

Insertion des data CSV

function buildInsertQuery(rows) {

return `INSERT INTO document_pprn(id_gaspar, nom, …) VALUES ${rows.join(',')};`

}

​

const fs = require('fs');

const csv = require('csv-parser');

fs.createReadStream(document_pprn_file)

.pipe(csv({separator: ';'}))

.on('data', (data) => {

const {id_gaspar,nom,...} = data;

rows.push(`('${id_gaspar}', ${handleStringValue(nom)}, …)`);

if (rows.length === bulkSize) {

console.log(buildInsertQuery(rows));

rows = [];

}

})

…

Insertion des data géographiques

const pipeline = chain([

fs.createReadStream(inputFileName),

Pick.withParser({filter: 'features'}),

streamArray()

]);

pipeline.on('data', data => {

const feature = data.value;

const geom = `ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(feature.geometry)}'), 4326)`

const props = featureMapper(feature.properties).map(property => buildColumnValue(property))

props.push(geom)

const toInsert = `(${props.join(",")})`;

rows.push(toInsert)

if (rows.length === bulkSize) {

console.log(buildInsertQuery(rows, sqlTableTemplate));

rows = [];

}

});

…

Avantages / Inconvénients

• ✅ Code ⇒ souplesse dans la manipulation des données
• ✅ Zone de confort technologique (node, SQL)
• ✅ Fonctionne parfaitement avec des datasets pas trop volumineux

​

• ❌ Pour les gros GeoJSON, nécessité de lancer le script avec pas mal de mémoire
• ❌ Besoin d’espace disque pour stocker les GeoJSON et les SQL
• ❌ Temps d’exécution (très) important pour les datasets volumineux
• ❌ Temps d’upload du fichier SQL final (très) important pour les datasets volumineux sur notre PaaS

Comment réduire le temps d’exécution

• Temps d’upload
• Diminuer la taille des fichiers
• Réduire la précision des GeoJSON ?
• ⇒ Travailler avec le format EWKT qui est moins verbeux

​

• Temps d’exécution
• Créer les contraintes et les index après l’insertion des données
• Utiliser l’instruction COPY
• ⇒ Travailler uniquement avec des fichiers CSV en lieu et place de requêtes INSERT

Commande COPY

• Permet de copier un fichier vers/depuis une table
• Prise en charge du CSV
• Possibilité de spécifier les colonnes si les noms (ou l’ordre) ne correspondent pas dans le fichier source
• ⚠️ COPY FROM s’arrête à la première erreur, les lignes déjà insérées ne sont ni visibles ni accessibles
• la perte d’espace disque peut être récupéré avec la commande VACUUM
• ⚠️ Toutes les colonnes du fichier source sont mappées (pas d’ignore)
• ⚠️ Nécessite que le serveur ait accès au fichier

COPY <nom_table> FROM '<chemin_fichier>' DELIMITER ';' CSV HEADER;

Instruction \copy

• Instruction psql
• Appelle COPY FROM STDIN / COPY TO STDOUT
• Lit/stocke les données dans un fichier accessible au client psql
• L’accès au fichier dépend donc du client (et non du serveur)

Approche n°2

PostgreSQL

data.gouv.fr

schémas

indexes, contraintes

Approche n°2

• Fichier SQL contenant la création des schémas et des contraintes
• Permet de s’assurer de l’intégrité des données dès l’insertion
• Fichier SQL contenant les index des schémas
• Téléchargement du dataset avec curl ou wget
• Transformation des Shapefile en CSV pour ne travailler qu’avec du CSV avec ogr2ogr
• Génération des fichiers CSV “sources” à l’aide de l’outil mlr
• Import du fichier SQL de création des schémas avec psql
• Import des CSV dans la base de données via l’instruction \copy
• Import du fichier SQL de création des index avec psql
• Script bash pour ordonnancer ces différentes commandes

https://github.com/guillaumesoldera/opendata-api-geo-pg/tree/main/pprn/second-impl

Génération des CSV à partir des SHP

ogr2ogr \

-t_srs EPSG:4326 \

-f CSV zone_pprn_17.csv \

zone_pprn_s_017.shp \

-lco SEPARATOR=SEMICOLON \

-lco GEOMETRY=AS_WKT \

-lco GEOMETRY_NAME=geom_latlon -lco CREATE_CSVT=YES

​

Cible

Source

Format d’export de la géométrie

Permet de spécifier le nom de la colonne qui contiendra la géométrie

Exemple de fichier CSV généré

geom_latlon;id_zone;id_gaspar;nom;...

"POLYGON (...)";RNATZR000000000039802;17DDTM19960003;"Zone réglementaire..";...

Manipulation des CSV

• mlr https://miller.readthedocs.io/
• Outil en ligne de commande
• Requête, transforme, convertit les fichiers CSV, JSON

Démo !

• mlr

https://github.com/guillaumesoldera/opendata-api-geo-pg/tree/main/demo-mlr

#DevoxxFR

Transformation des CSV

mlr \

--icsv --ifs ';' \

--ocsv --ofs ';' \

cut -o -f id_zone,id_gaspar,nom,codezone,typereg,urlfic,geom_latlon \

zone_pprn_17.csv \

| mlr \

--icsv --ifs ';' \

--ocsv --ofs ';' put '

$geom_latlon = "SRID=4326; ". $geom_latlon .""

' > zone_pprn_transformed.csv

Transformation des CSV

mlr \

--icsv --ifs ';' --ocsv --ofs ';' put '

if ($coderisque == 9999999) {

$coderisque = "".$num_alea."00000"

} else {

$coderisque = $coderisque

}

' multirisque_pprn_017.csv \

| mlr --icsv --ifs ';' --ocsv --ofs ';' cut -o -f id_gaspar,coderisque \

> multirisque_pprn_light.csv

Insertion des données

export PGPASSWORD=******

export pg_cmd_base="psql -h XXX.XXX.XX.XX -p 5400 -U postgres -d demo_devoxx"

echo "copy multirisque_pprn"

FILE="$(ls input/n_zonages_risque_naturel/multirisque_pprn_light.csv)"

FULL_PATH="$(pwd)/$FILE"

CMD="$pg_cmd_base -c \"\COPY multirisque_pprn FROM '$FULL_PATH' DELIMITER ';' CSV HEADER;\""

eval "$CMD"

Avantages / Inconvénients

• ✅ Temps d’exécution total
• 1^ère approche ≈ 1min.
• 2^ème approche ≈ 11sec.
• ✅ Pas de code à maintenir hormis le script bash
• ✅ Fonctionne parfaitement avec tous les datasets
• ✅ Espace disque et mémoire mieux gérés

​

• ❌ Manipulation à faire via mlr
• ❌ Penser à faire le VACUUM en cas d’échec

​

Les API

Enjeux de nos API

• Exposer nos données de manière standard
• Répondre aux besoins :
• B2C : applications dédiées aux clients finaux
• B2B : partenaires internes (feature teams) et externes (agences immobilières, collectivités)

Data

API

Applications clients finaux

Partenaires

B2C

B2B

Bonnes pratiques

• Toujours beaucoup de questionnements lorsqu’on doit développer une API
• « Tu mettrais quoi comme endpoints pour cette ressource ? »
• « Comment tu gères la pagination ? »
• « Tu retournerais quoi comme statut HTTP quand … ? »
• « Et au fait, tu as pensé au versioning ? »
• Avant de parler code, parlons bonnes pratiques !
• Source d’inspiration : François-Guillaume Ribreau (@FGRibreau)
• « Les 70+ points de contrôle d'une API »
• https://www.getnobullshit.com/
• https://www.youtube.com/watch?v=Mx6RJKa22no
• https://www.youtube.com/watch?v=UKrS_eXZfHw

Points d’attention particuliers

• Nommage & Cohérence
• Design des réponses & Utilisabilité
• Versioning
• Pagination
• Documentation
• Testing
• Sécurité
• Monitoring

Source : @FGRibreau

Nommage et Cohérence

Nommage

« There are only two hard things in Computer Science: cache invalidation and naming things. »

(Phil Karlton)

​

#DevoxxFR

Arborescence de nos ressources

• /risques
• /naturels
• /pprn
• /argiles
• /inondations
• /…
• /technologiques
• /pprt
• /centrales-nucleaires
• /icpe
• /…
• /miniers
• /pprm
• …

• /services
• /commodites
• /connectivite
• /…
• /urbanisme
• /monuments-historiques
• /peb
• …
• /territoires
• /communes
• /departements
• /regions
• /…

Nommage

• Choix d’un format unique pour les attributs des payloads JSON
• snake_case
• _ en préfixe pour des attributs techniques (ex: _path)
• Langues utilisées
• Français pour les notions métier
• Les data ne concerne que la France
• Les consommateurs de l’API sont français à 100%
• Traduire les notions métier n’aurait pas de sens
• Anglais pour le code
• Donc du « Franglais » assumé ;-)

Cohérence des endpoints

• /<ressource>
• Liste de l’ensemble des ressources (avec pagination)
• /<ressource>/<id>
• Détail d’une ressource
• /<ressource>/_metadata
• Méta-données liées à la ressource (fournisseur et source de données notamment)
• /<ressource>/_suggest?q=xxx
• Suggestion de ressources à partir d’un texte
• /<ressource>/_facets
• Facettes : nombre de ressources, avec classement par catégories (~GROUP BY)

Cohérence des endpoints

• /<ressource>?lat=xxx&lon=xxx(&radius=xxx)
• Liste des ressources pour une adresse (latitude + longitude)
• /<ressource>?code_insee=xxx
• Liste des ressources pour une commune (code insee)

​

​

Par défaut, les zones ne sont pas remontées dans les payloads (trop volumineux). Pour récupérer les zones afin de les visualiser (sur une carte par exemple) :

• /<ressource>/zones?lat=xxx&lon=xxx(&radius=xxx)
• /<ressource>/zones?code_insee=xxx

Design des réponses

& Utilisabilité

Utilisabilité

/v1/argiles/zones?code_insee=17300

???

/v1/argiles/_metadata

Utilisabilité

/v2/argiles/zones?code_insee=17300

Design des réponses

• Bonne pratique : wrapper les données dans un noeud dédié
• Le payload peut être enrichi (metadata, documentation)
• Compatibilité ascendante

Design des réponses

• Attribut _path pour le lien vers d’autres ressources
• API navigable à minima, sans se lancer dans du HATEOAS

Versioning

Versioning

• Versioning à penser dès le départ !
• Plusieurs façons de gérer le versioning des API
• Headers Accept / Content-Type
• https://api.example.com + “Accept: application/vnd.example.v1+json”
• URL Path
• https://api.example.com/v1
• Sous-Domaine
• https://api-v1.example.com
• Query params
• https://api.example.com/resource?version=x.y.z

Versioning

• Github utilise les headers :
• https://docs.github.com/en/rest/overview/media-types#request-specific-version
• Twitter utilise le path
• https://developer.twitter.com/en/docs/twitter-ads-api/versioning
• Stripe pousse le versioning très loin
• https://stripe.com/blog/api-versioning
• Nous avons choisi : URL Path
• Testable facilement dans un navigateur pour les routes GET (la majorité dans notre cas)
• Plus facile au niveau des contrôleurs / du routeur, en fonction du framework choisi

PostgREST

• PostgREST pourrait être une piste à étudier pour le versioning
• https://postgrest.org/en/stable/
• PostgREST transforme votre base de données PostgreSQL en une API RESTful.
• Les contraintes structurelles et les autorisations de la base de données déterminent les endpoints de l'API exposée.
• Un schéma public pour chaque version de l’API, contenant uniquement des vues basées sur des tables de schémas privés
• Exploitation au maximum du potentiel de la base de données

API Clients

v1

PostgREST

Versioning & Code Style

• Notre usecase « v1 » ⇒ « v2 » : enrichissement des payloads des réponses
• Des nouvelles routes « v2 » dans les contrôleurs
• v1 : @GetMapping(path = "/api/v1/risques/naturels/argiles")
• v2 : @GetMapping(path = "/api/v2/risques/naturels/argiles")
• Des objets (ou méthodes) spécifiques pour la sérialisation des réponses « v2 »
• Nous avons fait le choix de maîtriser la sérialisation en codant nous même la transformation des objets en JSON (sans utiliser d’annotations)
• Chaque objet sérialisable définit une méthode de transformation, réutilisable dans d’autres sérialisations (composition)
• public JsonNode toJson(ObjectMapper objectMapper) {...}

Versioning & Coding Style

@Override

public JsonNode toJson(ObjectMapper objectMapper) {

ObjectNode node = objectMapper.createObjectNode();

​

node.put("ref", ref);

​

descriptionOpt.ifPresent(description -> node.put("description", description));

​

if (!wcomList.isEmpty()) {

node.set("wcom", JsonUtils.toJsonStringArray(wcomList, objectMapper));

}

​

node.put("externalUrl", "https://www.pop.culture.gouv.fr/notice/merimee/" + ref);

​

areaOpt.ifPresent(area -> {

try {

JsonNode areaNode = objectMapper.readTree(area);

node.set("geometry", areaNode);

} catch (JsonProcessingException e) {

node.set("geometry", NullNode.getInstance());

}

});

​

return node;

}

​

Pagination

Pagination

• On s’est inspiré de ce que fait github :
• https://docs.github.com/en/rest/guides/traversing-with-pagination
• Côté requête
• ?page=xxx
• ?per_page=xxx
• Côté réponse
• Header Link pour indiquer les autres pages disponibles
• next : page suivante
• last : page précédente
• first : première page
• prev : dernière page
• self : page courante
• Header X-Total-Count pour indiquer le nombre total de résultats

self

first

last

prev

next

Pagination

➜ curl http://localhost:8080/api/v1/territoires/communes --include

​

HTTP/1.1 200

Link: </api/v1/territoires/communes?page=2>; rel="next", � </api/v1/territoires/communes?page=1510>; rel="last", � </api/v1/territoires/communes?page=1>; rel="self"

X-Total-Count: 37742

vary: accept-encoding

Content-Type: application/json;charset=UTF-8

Content-Length: 29985

Date: Fri, 11 Mar 2022 13:31:49 GMT

Pagination

➜ curl "http://localhost:8080/api/v1/territoires/communes?page=42" --include

HTTP/1.1 200

Link: </api/v1/territoires/communes?page=43>; rel="next",� </api/v1/territoires/communes?page=1510>; rel="last", � </api/v1/territoires/communes?page=41>; rel="prev", � </api/v1/territoires/communes?page=42>; rel="self", � </api/v1/territoires/communes?page=1>; rel="first"

X-Total-Count: 37742

vary: accept-encoding

Content-Type: application/json;charset=UTF-8

Content-Length: 28307

Date: Fri, 11 Mar 2022 13:45:02 GMT

Pagination

➜ curl "http://localhost:8080/api/v1/territoires/communes?page=42&per_page=200" --include

HTTP/1.1 200

Link: </api/v1/territoires/communes?per_page=200&page=43>; rel="next", � </api/v1/territoires/communes?per_page=200&page=378>; rel="last", � </api/v1/territoires/communes?per_page=200&page=41>; rel="prev", � </api/v1/territoires/communes?per_page=200&page=42>; rel="self", � </api/v1/territoires/communes?per_page=200&page=1>; rel="first"

X-Total-Count: 37742

vary: accept-encoding

Content-Type: application/json;charset=UTF-8

Content-Length: 118156

Date: Fri, 11 Mar 2022 13:46:54 GMT

Documentation

Documentation

• Documentation générale : README.md
• Généralités
• Roadmap
• Liens utiles
• Guide pour les développeurs

Documentation

• Documentation technique : Open API v3 (Swagger)
• Swagger UI pour l’accès à la documentation

Documentation

Quelques problématiques autour d’Open API (Swagger)

• La taille du fichier devient rapidement importante (+ de 10 000 lignes dans notre cas)
• Possibilité de découper le fichier en plusieurs morceaux
• Utilisation des références pour assembler le tout : $ref
• URI + Fragment
• RFC 3986 : https://datatracker.ietf.org/doc/html/rfc3986

responses:

'200':

description: OK - Retourne le détail du fournisseur de données.

content:

application/json:

schema:

$ref: 'schemas.yml#/components/schemas/DataProvider'

Documentation

Quelques problématiques autour d’Open API (Swagger)

• Il n’est pas possible de représenter facilement du nd-json.
• Solution de contournement :
• content-type : application/x-ndjson
• schema : array
• https://github.com/swagger-api/swagger-ui/issues/5342

responses:

'200':

description: OK - Retourne l'ensemble des risques associés au point géographique donné.

content:

application/x-ndjson:

schema:

type: array

items:

$ref: '#/components/schemas/BulkRisquesOutput'

Documentation

La documentation est rendue via un template HTML Spring Boot grâce à la lib JS SwaggerUI

<body>

<div id="swagger-ui"></div>

​

<script src="doc/javascripts/swagger-ui-bundle.js" charset="UTF-8"></script>

<script src="doc/javascripts/swagger-ui-standalone-preset.js" charset="UTF-8"></script>

<script>

window.onload = function () {

const ui = SwaggerUIBundle({

url: "doc/swagger.yml",

dom_id: '#swagger-ui',

deepLinking: true,

presets: [

SwaggerUIBundle.presets.apis,

SwaggerUIStandalonePreset

],

plugins: [

SwaggerUIBundle.plugins.DownloadUrl

],

layout: "StandaloneLayout"

})

​

window.ui = ui

}

</script>

</body>

@Controller

public class DocumentationController {

​

@GetMapping("/doc")

public String doc() {

return "documentation";

}

​

}

Testing

Testing

• Tests unitaires
• Dans un premier temps uniquement pour nos méthodes utilitaires
• Dans un second temps pour le code qui a besoin d’un accès à la base de données, avec TestContainers.
• Ces tests sont exécutés via une Github Action, à chaque push / PR
• Tests « end-to-end »
• Pour tester la chaîne complète d’un appel à l’API.
• Vérification de la non-régression
• Test du bon état de fonctionnement d’un environnement (staging, prod).

TestContainers

• https://www.testcontainers.org/
• Librairie Java permettant de donner accès à tout un tas de choses pouvant être démarrées via un conteneur Docker, en particulier des bases de données telles que PostgreSQL
• Permet (enfin !) d’avoir une solution propre pour écrire et exécuter des tests de notre code nécessitant un accès à une base de données
• Pas de mock
• Pas de « fake DB »
• Aucun problème pour tester avec PostGIS
• Fonctionne correctement avec les Github Actions

TestContainers

@Testcontainers

public class PaginatedQueryTests {

​

@Container

private static final PostgreSQLContainer<?> PG_CONTAINER = new PostgreSQLContainer<>("postgres:12")

.withUsername("xxx_user_test")

.withPassword("xxx_user_test")

.withDatabaseName("xxx_db_test")

.withExposedPorts(5432)

.withInitScript("utils/pagination/paginated-query-tests.sql");

2022-04-11 10:23:15.390 INFO 48343 --- [ main] 🐳 [postgres:12] : Creating container for image: postgres:12

2022-04-11 10:23:15.459 INFO 48343 --- [ main] 🐳 [postgres:12] : Starting container with ID: e91faa1e5ea405fc581331d09b49d0537fba3ba7327a66127221367d623b20a2

2022-04-11 10:23:15.923 INFO 48343 --- [ main] 🐳 [postgres:12] : Container postgres:12 is starting: e91faa1e5ea405fc581331d09b49d0537fba3ba7327a66127221367d623b20a2

2022-04-11 10:23:17.277 INFO 48343 --- [ main] 🐳 [postgres:12] : Container postgres:12 started in PT1.887176S

2022-04-11 10:23:17.280 INFO 48343 --- [ main] org.testcontainers.ext.ScriptUtils : Executing database script from utils/pagination/paginated-query-tests.sql

2022-04-11 10:23:17.360 INFO 48343 --- [ main] org.testcontainers.ext.ScriptUtils : Executed database script from utils/pagination/paginated-query-tests.sql in 80 ms.

Tests End-To-End

• Écrit en node.js, avec Jest et Supertest

test('GET /v1/urbanisme/monuments-historiques/_metadata', async () => {

const response = await request.get('/v1/urbanisme/monuments-historiques/_metadata');

​

expect(response.status).toBe(200);

const expectedData = {

data_sources: [

{

id: "monuments-historiques",

name: "Monuments Historiques",

url: "https://data.culture.gouv.fr/[...]-monuments-historiques/",

description: "Liste et localisation des Immeubles protégés au titre des Monuments Historiques",

publication_date: "2020-12-20",

provider: {

id: "ministere-de-la-culture",

name: "Ministère de la Culture",

url: "https://data.culture.gouv.fr/",

description: "La plate-forme de données ouvertes du ministère de la Culture"

}

}

]

};

expect(response.body).toEqual(expectedData);

});

Les problèmes liés à la précision

• Constat : la précision peut changer d’un environnement à l’autre
• En cause : des versions différentes de PostGIS

PostGIS 2.5

PostGIS 3.x

La sécurité

API Management

• Les API sont exposées au travers d’Otoroshi (Reverse Proxy & API Management)
• https://maif.github.io/otoroshi/

Otoroshi

Data

API

Applications clients/soc.

Partenaires

B2C

B2B

Otoroshi

• Gorgeous React admin UI
• Rest admin API
• Event-Driven
• Live reconfiguration at runtime
• Circuit breakers, retry, canary mode
• Round Robin loadbalancing, healthcheck
• Throttling and quotas per ApiKey

• Metrics (Rest, Datadog, Statd)
• Advanced Clever-Cloud integration
• Multi-datastores support
• Official docker image
• Available as single jar file

Otoroshi

• API Keys, Throttling & Quotas

Otoroshi

• Circuit Breaker

Analytics & Monitoring

Analytics

• Otoroshi peut être couplé à un datastore permettant de stocker les events

Otoroshi

Data

API

Applications clients/soc.

Partenaires

B2B

Events

Dataviz

Analytics

• Otoroshi propose quelques dashboards

Analytics

• Kibana permet de créer des dashboards personnalisés

Metrics Clever Cloud

• Clever Cloud met à disposition un certain nombre de Metrics pour les Apps et Addons

Metrics Clever Cloud

• Clever Cloud expose des Metrics dans Grafana

Monitoring

• Clever Cloud surveille l’état de santé des Applications et les redémarre automatiquement si elles sont « unreachable »

Alerting

• Status Cake nous permet de surveiller l’état de santé de nos applications

Les performances

Performances

Kent Beck (extreme programming) :

1. Make it work
2. Make it right
3. Make it fast

Pourquoi fast ?

• Besoin de récupérer l’ensemble des données de risques pour + de 2 millions d’adresses
• Création des API « résumé » et _bulk
• Passage du code en @Async
• Scalabilité
• Tuning des requêtes, vues et indexes

API « Résumé »

• Besoin : remonter en un seul appel plusieurs données de risques
• Gestion de filtres permettant de choisir les données

GET /api/v1/risques

?lat=48.768837219&lon=1.968329122

&fields=naturels.secheresses,naturels.tempetes

Résumé

Risques

Sécheresses

Inondations

Tempêtes

Centrales Nucléaires

❌

❌

API _bulk

• Besoin : appeler l’API « résumé » en masse.
• Format choisi : nd-json

POST /api/v1/risques/_bulk

&fields=naturels.secheresses,naturels.tempetes

Résumé

Risques

Sécheresses

Inondations

Tempêtes

Centrales Nucléaires

Résumé

Risques

Sécheresses

Inondations

Tempêtes

Centrales Nucléaires

Résumé

Risques

Sécheresses

Inondations

Tempêtes

Centrales Nucléaires

Bulk

Risques

{"id":"1","lat":48.768837,"lon":1.968329}

{"id":"2","lat":48.346621,"lon":0.476500}

{"id":"3","lat":47.711751,"lon":-3.469709}

❌

❌

❌

❌

❌

❌

lat=48.768837

lon=1.968329

lat=48.346621

lon=0.476500

lat=47.711751

lon=-3.469709

API _bulk

curl --location --request POST 'http://localhost:8080/api/v1/risques/_bulk?fields=naturels.secheresses.scores' \

--header 'Content-Type: application/x-ndjson' \

--data-raw '{"id":"6982a745-1284-4e07-a628-e7d567560773","lat":48.7688372194,"lon":1.9683291223,"radius":1000}

{"id":"036aee0d-74b6-40fc-910e-8f5f7687983c","lat":48.3466211545,"lon":0.4765005545,"radius":1000}

{"id":"be63380e-5fbf-4e97-991e-b7f7694e70e0","lat":47.7117511724,"lon":-3.4697094768,"radius":1000}

{"id":"41a01f64-e82c-4941-a493-593d39abd98a","lat":42.6923137272,"lon":2.8918979363,"radius":1000}

{"id":"9299b0d0-6d57-4c36-971a-a5732bee11ad","lat":48.8601300090,"lon":2.3694592772,"radius":1000}'

{"id":"036aee0d-74b6-40fc-910e-8f5f7687983c","status":200,"risques":{"naturels":{"secheresses":{"score":4,"color":"#e9352e","label":"Fort"}}}}

{"id":"6982a745-1284-4e07-a628-e7d567560773","status":200,"risques":{"naturels":{"secheresses":{"score":5,"color":"#68389b","label":"Très fort"}}}}

{"id":"be63380e-5fbf-4e97-991e-b7f7694e70e0","status":200,"risques":{"naturels":{"secheresses":{"score":1,"color":"#a0cd63","label":"A priori nul ou hors zonage"}}}}

{"id":"41a01f64-e82c-4941-a493-593d39abd98a","status":200,"risques":{"naturels":{"secheresses":{"score":4,"color":"#e9352e","label":"Fort"}}}}

{"id":"9299b0d0-6d57-4c36-971a-a5732bee11ad","status":200,"risques":{"naturels":{"secheresses":{"score":1,"color":"#a0cd63","label":"A priori nul ou hors zonage"}}}}

id de corrélation

Hey !

• Pour exécuter nos tests de performance : https://github.com/rakyll/hey

hey -n 500 -c 50 "http://localhost:8080/api/v1/risques/pprn/zones?lat=46.15654&lon=-1.15420"

Summary:

Total: 0.8334 secs

Slowest: 0.3070 secs

Fastest: 0.0070 secs

Average: 0.0727 secs

Requests/sec: 599.9446

​

Response time histogram:

0.007 [1] |

0.037 [174] |■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

0.067 [113] |■■■■■■■■■■■■■■■■■■■■■■■■■■

0.097 [56] |■■■■■■■■■■■■■

0.127 [66] |■■■■■■■■■■■■■■■

0.157 [38] |■■■■■■■■■

0.187 [34] |■■■■■■■■

0.217 [11] |■■■

0.247 [5] |■

0.277 [1] |

0.307 [1] |

Latency distribution:

10% in 0.0141 secs

25% in 0.0274 secs

50% in 0.0563 secs

75% in 0.1128 secs

90% in 0.1602 secs

95% in 0.1812 secs

99% in 0.2224 secs

​

Details (average, fastest, slowest):

DNS+dialup: 0.0005 secs, 0.0070 secs, 0.3070 secs

DNS-lookup: 0.0002 secs, 0.0000 secs, 0.0028 secs

req write: 0.0000 secs, 0.0000 secs, 0.0014 secs

resp wait: 0.0706 secs, 0.0066 secs, 0.3064 secs

resp read: 0.0016 secs, 0.0004 secs, 0.0378 secs

​

Status code distribution:

[200] 500 responses

@Async

Première version : code bloquant

​

​

​

Deuxième version : code non-bloquant (@EnableAsync sur l’Application)

@Async

@GetMapping(path = "/api/v1/data/sources/{id}", produces = "application/json; charset=UTF-8")

@ResponseBody

public CompletableFuture<String> byId(@PathVariable String id, HttpServletResponse response) {

CompletableFuture<Optional<DataSource>> dataSourceOptFuture = dataSourceRepository.findById(id);

return dataSourceOptFuture.thenApply(

dataSourceOpt -> processOptionalJsonable(dataSourceOpt, response)

);

}

@GetMapping(path = "/api/v1/data/source/{id}", produces = "application/json; charset=UTF-8")

@ResponseBody

public String byId(@PathVariable String id, HttpServletResponse response) {

Optional<DataSource> dataSourceOpt = dataSourceRepository.findById(id);

return processOptionalJsonable(dataSourceOpt, response);

}

@Async

• Executor personnalisé

@Bean

public Executor taskExecutor() {

int parallelism = Math.min(

Math.max(

Double.valueOf(Math.ceil(

Runtime.getRuntime().availableProcessors() * parallelismFactor)

).intValue(),

parallelismMin

),

parallelismMax

);

​

return new ForkJoinPool(

parallelism,

ForkJoinPool.defaultForkJoinWorkerThreadFactory,

new Thread.UncaughtExceptionHandler() {...},

true

);

}

executor.parallelism-min=${EXECUTOR_PARALLELISM_MIN:2}

executor.parallelism-max=${EXECUTOR_PARALLELISM_MAX:10}

executor.parallelism-factor=${EXECUTOR_PARALLELISM_FACTOR:2.0}

@Value("${executor.parallelism-min}")

private int parallelismMin;

@Value("${executor.parallelism-max}")

private int parallelismMax;

@Value("${executor.parallelism-factor}")

private double parallelismFactor;

Scalabilité

• Clever Cloud propose de la scalabilité verticale et horizontale

Scalabilité des addons

• Addon PostgreSQL Clever

Scalabilité des addons

• Migration d’un addon PostgreSQL Clever

Scalabilité

Configuration Pool de connexions à la DB

​

​

spring.datasource.hikari.maximum-pool-size=${HIKARI_MAXIMUM_POOL_SIZE:10}

Optimisation des requêtes

• vérifier les plans d’exécution et créer les bons indexes si besoin
• optimiser les requêtes en essayant d’avoir des jointures sur des tables les plus petites possibles
• utiliser des tables temporaires (with…)
• essayer de pré-calculer les requêtes coûteuses
• créer des vues

​

​

Optimisation - Cas d’usage

• Base permanente des équipements (INSEE)
• répertorie les équipements et services pour chaque commune
• services aux particuliers
• commerces
• enseignement
• santé / social
• transports / déplacements
• sports / loisirs / culture
• tourisme
• + de 2,7 millions de lignes

​

• Besoin : calculer un « score » pour chaque domaine d’équipements d’une commune
• Pour des communes de même taille, établir un classement selon le nombre total d’équipements

Optimisation - Cas d’usage

Tranche détaillée d’unité urbaine :

donne une classification des communes

selon leur nombre d’habitants

Optimisation - V0

création d’une vue pour « fusionner » base_permanente_equipement et ref_typeequipement

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Temps d’exécution :

2.8s

Optimisation des requêtes - V0

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), app_geo_tduu AS (

SELECT com, typecom

FROM appartenance_geographique ag

INNER JOIN tduu_city tc ON ag.tduu = tc.tduu

)

SELECT distinct(rte.lib_domaine) as label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, count(*) AS countEquipCom

FROM view_bpe bpe

INNER JOIN app_geo_tduu ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine

) as sub_a

) as sub_b

WHERE com='86281'

) as b on rte.lib_domaine = b.label

Optimisation - V1

Optimisation - V1

CREATE MATERIALIZED VIEW view_grouped_domaine_bpe_tduu

AS

SELECT bpe.com, bpe.lib_domaine, ag.tduu, count(*) as count

FROM view_bpe bpe

INNER JOIN appartenance_geographique ag ON bpe.com = ag.com AND bpe.typecom = ag.typecom

GROUP BY bpe.com, bpe.lib_domaine, ag.tduu;

​

CREATE INDEX view_grouped_domaine_bpe_tduu_typecom_com_idx ON view_grouped_domaine_bpe_tduu (com);

CREATE INDEX view_grouped_domaine_bpe_tduu_lib_domaine_idx ON view_grouped_domaine_bpe_tduu (lib_domaine);

CREATE INDEX view_grouped_domaine_bpe_tduu_tduu_idx ON view_grouped_domaine_bpe_tduu (tduu);

Optimisation - V1

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), bpe_rank_for_city AS (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, bpe.count as countEquipCom

FROM view_grouped_domaine_bpe_tduu bpe

INNER JOIN tduu_city tc ON bpe.tduu = tc.tduu

) AS sub_a

) AS sub_b

WHERE com='86281'

)

SELECT distinct(rte.lib_domaine) AS label, COALESCE(b.score, 0) as score

FROM ref_typeequipement rte

LEFT JOIN bpe_rank_for_city b ON rte.lib_domaine = b.label

Optimisation - V1

WITH

tduu_city AS (

SELECT tduu FROM appartenance_geographique WHERE com='86281'

), bpe_rank_for_city AS (

SELECT lib_domaine as label, (ntile / 10::decimal) AS score

FROM (

SELECT *, ntile(100) OVER (PARTITION BY lib_domaine ORDER BY countEquipCom)

FROM (

SELECT bpe.com, bpe.lib_domaine, bpe.count as countEquipCom

FROM view_grouped_domaine_bpe_tduu bpe

INNER JOIN tduu_city tc ON bpe.tduu = tc.tduu