Introduction
Les filtres cartographiques n'existaient pas dans Superset, malgré la possibilité de faire des cartes à partir de données ponctuelles ou de geojson.
La fonctionnalité de filtrage discutée ici et développée par Synaltic fonctionne grâce à une recherche de texte au sein de la colonne des coordonnées. Il n'y a pas de logique spatiale , intersection ou autre, mais plutot une recherche attributaire.
Nous verrons donc dans ce guide comment réaliser un jeu de données permettant d'utiliser cette fonctionnalité, ainsi que les outils pour y arriver et quelques considérations pour les performances.
Logiciel et accès pré-requis
- Savoir ajouter du GeoJson dans un jeu de données
- Créer une carte dans Superset
- QGIS (ou sinon des connaissances en Postgis)
- Un compte pgAdmin lié à votre base de données alimentant Superset
- Un compte utilisateur de la base de données
- Un compte Superset
Schéma de données et informations nécessaires
Cadrage général
L'objectif est d'obtenir un jeu de données possédant l'ensemble des attributs et des géométries que nous souhaitons représenter dans notre tableau de bord. Cela inclut d'avoir plusieurs colonnes géométries (1 pour chaque type d'entités que nous souhaitons représenter) dans une même table.
Nous prendrons comme exemple le jeu de données des restaurants de la base Décibelle Data, et les départements de la BFC fourni dans la BD CARTO.
1) Jointure spatiale entre les deux jeux de données
Pour obtenir les deux géométries dans la même table (géométrie du département dans la table des points des restaurants), nous allons utiliser le traitement "Joindre les attributs par localisation" de QGIS. On ne joindra que l'attribut "cleabs" de la couche "Département", ce qui nous servira plus tard à ajouter les géométries requises.
Une fois la jointure effectuées, il faut exporter la couche issue de la jointure et la couche des département au format SQL afin de pouvoir les importer en bases de données.
Par défaut, les tables seront incluses dans le schéma public. Si vous souhaitez les inclure dans un autre schéma, il existe une case "SCHEMA" dans la fenêtre d'export qui vous permet de le changer.
2) Import des données dans pgadmin
Connectez vous à pgadmin, puis à la base de données espace de travail.
Ouvrez un nouvel onglet de Query Tool (outil pour executer des requêtes SQL)
Ouvrir avec un éditeur de texte (de préférence Notepad++) un script SQL créer par QGIS.
Coller et executer le script SQL pour importer les données.
Utiliser la requete SQL suivante pour donner accès à Superset à votre jeu de données nouvellement créer :
GRANT SELECT ON TABLE schema.table TO workspace ;
-- workspace est l'utilisateur de superset par défaut dans DataBFC
3) Créer les géométries en Geojson
Copier le script SQL en annexe de cette documentation dans pgadmin et l'executer.
Faire attention au schéma dans lequel la fonction va être créer. Ajouter le votre si nécessaire comme dans l'image ci-dessous (à la place de "public"):
CREATE OR REPLACE FUNCTION "public".add_geojson_column_with_srid_transform(
input_schema TEXT,
input_table TEXT,
id_column TEXT,
source_geom_column TEXT,
new_geom_column TEXT,
geojson_column_name TEXT,
target_srid INT DEFAULT 3857
)
Vérifier que la fonction est bien dans votre schéma grâce à l'interface graphique de pgadmin :
Ce script créer une Fonction Postgresql qui permettra de créer une colonnes de géométries geojson à partir d'une colonne géométries WKT / WKB et d'un SRID.
Voici un exemple pour l'execution de la fonction :
SELECT demo_carto.add_geojson_column_with_srid_transform(
'demo_carto', --schéma
'sql_departements', --table à traiter
'ogc_fid', --colonne id
'wkb_geometry', --colonne de la géométrie
'geom_4326_dep', --colonne de géométrie reprojetée créer automatiquement par la fonction
'geojson_dep', --colonne de geojson créer automatiquement par la fonction
4326 --INTEGER du système de projection à utiliser
)
Fonctionnement de la fonctionnalité dans Superset
Création des graphiques
En suivant les étapes précédentes, nous avons créer un jeu de données avec les géométries et les attributs de l'ensemble des données que nous souhaitons visualiser dans notre tableau de bord.
Dans un premier temps, nous devons l'importer dans Superset. Pour ça, il faut se connecter à Superset, puis cliquer sur le "+" puis sur "Créer un ensemble de données".
Ensuite, choisir la table créer précedemment, puis réaliser plusieurs graphiques et un tableau de bord qui les accueille.
Dans l'exemple, nous avons une colonne géométrie geojson et deux colonnes définissant les coordonnées des points (longitude, latitude). Donc nous pouvons au moins faire deux cartes différentes : une carte de Points et une carte de Polygones. Voir Cartographie dans Superset
ATTENTION : Il est conseiller d'utiliser à chaque fois le même jeu de données pour que les filtres fonctionnent bien.
Utilisation du filtre dans le tableau de données
Une fois les cartes et les graphiques créé et ajouté dans le tableau de bord, il suffit de cliquer sur une entité de la carte pour activer le filtre. Exemple dans les deux prochaines images suivantes :
Avant le filtre :
Après le filtre (clic sur la Côte d'Or) :
Point d'attention sur la performance
Les Geojson sont lourd et l'action de filtrage après le clic peut prendre plusieurs secondes. C'est dû à la recherche texte dans les geojson. Pour améliorer les performances de ces requetes :
- Créer des index sur les colonnes de points et de géométries
--Exemple
CREATE INDEX gmaplatitude_idx ON demo_carto.resto_dep_join(gmaplatitude);
CREATE INDEX gmaplongitude_idx ON demo_carto.resto_dep_join (gmaplongitude);
CREATE INDEX geom_dep_idx ON demo_carto.resto_dep_join(geojson_dep);
- Utiliser des géométries adaptées à l'affichage web (géométries simplifiées avec moins de vertex)
Par exemple, préférer la BD CARTO à la BD TOPO , beaucoup plus précises mais moins pertinentes dans notre cas.
Annexe
-- Script à executer pour créer la fonction
CREATE OR REPLACE FUNCTION "public".add_geojson_column_with_srid_transform(
input_schema TEXT,
input_table TEXT,
id_column TEXT,
source_geom_column TEXT,
new_geom_column TEXT,
geojson_column_name TEXT,
target_srid INT DEFAULT 3857
)
RETURNS void AS $$
DECLARE
geom_type TEXT;
base_geom_type TEXT;
sql TEXT;
BEGIN
-- 1. Récupère le type de géométrie
EXECUTE format(
'SELECT DISTINCT ST_GeometryType(%I) FROM %I.%I WHERE %I IS NOT NULL LIMIT 1',
source_geom_column, input_schema, input_table, source_geom_column
)
INTO geom_type;
IF geom_type IS NULL THEN
RAISE EXCEPTION 'Impossible de déterminer le type de géométrie pour %.%', input_schema, input_table;
END IF;
base_geom_type := replace(geom_type, 'ST_', '');
-- 2. Ajoute la nouvelle colonne géométrique reprojetée
sql := format(
'ALTER TABLE %I.%I ADD COLUMN %I geometry(%s, %s);',
input_schema, input_table, new_geom_column, base_geom_type, target_srid
);
EXECUTE sql;
-- 3. Peupler avec ST_Transform
sql := format(
'UPDATE %I.%I SET %I = ST_Transform(%I, %s) WHERE %I IS NOT NULL;',
input_schema, input_table, new_geom_column, source_geom_column, target_srid, source_geom_column
);
EXECUTE sql;
-- 4. Ajoute la colonne GeoJSON
sql := format(
'ALTER TABLE %I.%I ADD COLUMN %I JSONB;',
input_schema, input_table, geojson_column_name
);
EXECUTE sql;
-- 5. Remplit la colonne GeoJSON enrichie avec ST_ForcePolygonCCW sur géométrie en 4326
sql := format(
'UPDATE %1$I.%2$I AS t SET %3$I = geojson_data.geojson FROM ( ' ||
' SELECT %6$I, jsonb_build_object( ' ||
' ''type'', ''Feature'', ' ||
' ''geometry'', ST_AsGeoJSON(ST_ForcePolygonCCW(ST_Transform(%4$I, 4326)))::jsonb, ' ||
' ''properties'', jsonb_build_object(''id'', %6$I) ' ||
' ) AS geojson ' ||
' FROM %1$I.%2$I ' ||
') AS geojson_data WHERE t.%6$I = geojson_data.%6$I;',
input_schema, -- %1$I
input_table, -- %2$I
geojson_column_name, -- %3$I
new_geom_column, -- %4$I
target_srid, -- %5$I
id_column -- %6$I
);
EXECUTE sql;
RAISE NOTICE 'Colonnes "%" (geometry) et "%" (GeoJSON enrichi) ajoutées et remplies.', new_geom_column, geojson_column_name;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION add_geojson_column_with_srid_transform IS 'input_schema TEXT,
input_table TEXT,
id_column TEXT,
source_geom_column TEXT,
new_geom_column TEXT,
geojson_column_name TEXT,
target_srid INT DEFAULT 3857'