Dans un cycle de développement de logiciel, il est fréquent de devoir considérer l'interfaçage des données du produit avec des logiciels tiers. Une manière courante de le faire est par exemple de mettre en place une API pour formater et servir des données. Dans cet article, nous allons décrire un processus similaire via la mise en place de vues SQL qui permettent à l'utilisateur de lire de la donnée formatée, sans possibilité d'influer sur le contenu d'une base, tout en se connectant directement à celle-ci.

Contexte du projet

Dans le cadre de l'amélioration de l'affichage des données de Geotrek-admin, nous avons standardisé l'utilisation du SQL.

Historiquement, du code SQL était chargé dans la base de donnée lors des migrations Django pour ajouter des triggers, fonctions et vues SQL. Nous avions utilisé notre propre fonctionnement de templating pour charger les fichiers SQL.

Le projet a dans un premier temps été d'optimiser les vues SQL, et dans ce cadre, nous en avons profité pour standardiser le fonctionnement de l'installation des fichiers SQL via l'utilisation du templating Django.

Pourquoi créer des vues SQL ?

A l'origine de ce projet est né le besoin chez quelques utilisateurs de Geotrek, et plus particulièrement le Conseil Départemental des Pyrénées Orientales (CD66), de pouvoir afficher les données de Geotrek-admin dans un outil externe, comme QGIS. Une première itération a consisté à mettre à disposition des vues SQL pour Geotrek afin de les afficher facilement dans QGIS.

Ces vues contiennent les informations essentielles que nous retrouvons dans Geotrek-admin au niveau de chaque formulaire de module.

L'enjeu de cette première mouture des vues SQL pour Geotrek a été de faciliter l'accès aux données à travers des outils externes, tout en les rendant les plus complètes et génériques possibles.

Pour cela, il a fallu :

• Sélectionner les attributs essentiels à insérer dans chaque vue SQL, à partir de chacun des modules de Geotrek-admin : sentiers, aménagements, signalétiques, itinéraires, etc.
• Afficher l'ensemble des informations contenues dans les vues sous forme de couches géographiques dans QGIS en lecture seule

Les modifications se font directement dans Geotrek-admin pour chaque projet, et sont répercutées instantanément dans les vues SQL.

Ces vues auraient pu rester en l'état mais elles n'auraient été exploitables que par le CD66 et ne présentaient les données qu'en langue française, or la base de données de Geotrek est en anglais.

Django permet facilement de contourner cette problématique.

Standardisation vers le templating Django des vues SQL

Dans Geotrek-admin, nous installons du code SQL hors des migrations Django. Pour cela, les triggers SQL doivent être installés directement en base de données et non pas au niveau applicatif, afin que la création et l'affichage de contenu soit possible hors application. Cela permet l'intégration rapide de contenu depuis la base de données ou depuis n'importe quel outil pouvant se connecter à la base de donnée (QGIS par exemple). 

A l'origine, tous ces fichiers SQL utilisent un templating spécifique à Geotrek-admin une configuration applicative. Par exemple :

• Utilisation des schémas DATABASE_SCHEMAS pour organiser les apps Django
• Fonctions géographiques utilisant un SRID : Spatial Reference Identifier, identifiant unique associé à un système de coordonnées
• Mise en place de valeurs par défaut pour l'intégration de contenu directement au niveau de la base de données (les valeurs par défaut dans Django sont gérées au niveau applicatif)

Afin de mettre à jour la configuration des fonctions SQL, la migration Django est utilisée. Avec la commande migrate, les fichiers SQL contenant les vues, les fonctions et triggers sont tout d'abord désinstallés avant la migration Django.

 

-- 10

DROP FUNCTION IF EXISTS ST_InterpolateAlong(geometry, geometry) CASCADE;
DROP FUNCTION IF EXISTS ST_Smart_Line_Substring(geometry, float, float) CASCADE;
DROP FUNCTION IF EXISTS ST_SmartLineSubstring(geometry, float, float) CASCADE;
DROP FUNCTION IF EXISTS ft_IsBefore(geometry, geometry) CASCADE;
DROP FUNCTION IF EXISTS ft_IsAfter(geometry, geometry) CASCADE;
DROP FUNCTION IF EXISTS ft_Smart_MakeLine(geometry[]) CASCADE;

-- 20

DROP FUNCTION IF EXISTS evenement_latest_updated_d() CASCADE;
DROP FUNCTION IF EXISTS topology_latest_updated_d() CASCADE;

DROP FUNCTION IF EXISTS update_geometry_of_evenement(integer) CASCADE;
DROP FUNCTION IF EXISTS update_geometry_of_topology(integer) CASCADE;

DROP FUNCTION IF EXISTS update_evenement_geom_when_offset_changes() CASCADE;
DROP FUNCTION IF EXISTS update_topology_geom_when_offset_changes() CASCADE;

DROP FUNCTION IF EXISTS evenement_elevation_iu() CASCADE;
DROP FUNCTION IF EXISTS topology_elevation_iu() CASCADE;

-- 30

DROP FUNCTION IF EXISTS ft_troncon_interpolate(integer, geometry) CASCADE;
DROP FUNCTION IF EXISTS ft_path_interpolate(integer, geometry) CASCADE;

DROP FUNCTION IF EXISTS ft_evenements_troncons_geometry() CASCADE;
DROP FUNCTION IF EXISTS ft_topologies_paths_geometry() CASCADE;

DROP FUNCTION IF EXISTS ft_evenements_troncons_junction_point_iu() CASCADE;
DROP FUNCTION IF EXISTS ft_topologies_paths_junction_point_iu() CASCADE;

-- 40

DROP FUNCTION IF EXISTS check_path_not_overlap(integer, geometry) CASCADE;

DROP FUNCTION IF EXISTS update_evenement_geom_when_troncon_changes() CASCADE;
DROP FUNCTION IF EXISTS update_topology_geom_when_path_changes() CASCADE;

DROP FUNCTION IF EXISTS elevation_troncon_iu() CASCADE;
DROP FUNCTION IF EXISTS elevation_path_iu() CASCADE;

DROP FUNCTION IF EXISTS troncons_related_objects_d() CASCADE;
DROP FUNCTION IF EXISTS paths_related_objects_d() CASCADE;

DROP FUNCTION IF EXISTS troncon_latest_updated_d() CASCADE;
DROP FUNCTION IF EXISTS path_latest_updated_d() CASCADE;

-- 50

DROP FUNCTION IF EXISTS troncons_snap_extremities() CASCADE;
DROP FUNCTION IF EXISTS paths_snap_extremities() CASCADE;

DROP FUNCTION IF EXISTS troncons_evenement_intersect_split() CASCADE;
DROP FUNCTION IF EXISTS paths_topology_intersect_split() CASCADE;

-- 60

DROP VIEW IF EXISTS l_v_sentier CASCADE;
DROP VIEW IF EXISTS v_trails CASCADE;

-- 70

DROP FUNCTION IF EXISTS ft_merge_path(integer,integer) CASCADE;

 

Une fois cette opération effectuée, la base de données est vierge de tout élément provenant des fichiers SQL. La migration gérée par Django est ensuite lancée. Si des opérations de migrations n'ont pas encore été réalisées, Django les installe.

Postgres is up - executing command
Operations to perform:
  Apply all migrations: admin, altimetry, auth, authent, cirkwi, common, contenttypes, core, diving, django_celery_results, easy_thumbnails, feedback, flatpages, infrastructure, land, maintenance, mapentity, outdoor, sensitivity, sessions, signage, tourism, trekking, zoning
Running migrations:
No migrations to apply.

Nous réinstallons ensuite tous les fichiers SQL avec la nouvelle configuration.

CREATE FUNCTION {# geotrek.core #}.topology_elevation_iu() RETURNS trigger SECURITY DEFINER AS $$
DECLARE
    elevation elevation_infos;
BEGIN
    IF {{ TREKKING_TOPOLOGY_ENABLED }} THEN
        RETURN NEW;
    END IF;
    SELECT * FROM ft_elevation_infos(NEW.geom, {{ ALTIMETRIC_PROFILE_STEP }}) INTO elevation;
    -- Update path geometry
    NEW.geom_3d := elevation.draped;
    NEW."length" := ST_3DLength(elevation.draped);
    NEW.slope := elevation.slope;
    NEW.min_elevation := elevation.min_elevation;
    NEW.max_elevation := elevation.max_elevation;
    NEW.ascent := elevation.positive_gain;
    NEW.descent := elevation.negative_gain;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Les paramètres sont tous chargés avec le template Geotrek-admin et remplacés comme pour le templating Django ({{ TREKKING_TOPOLOGY_ENABLED }}). Pour certains paramètres comme les schémas qui nécessitent une modification avant de générer le rendu du template, nous utilisons d'autres balises : {# geotrek.core #}

En d'autres termes, nous avons surchargé la commande Django migrate pour installer les vues SQL.

Le templating que nous utilisions jusqu'à lors dans Geotrek-admin ne permettait pas d'utiliser de boucles, de conditions ou encore de filtres.

 

Avec Geotrek, nous voulons permettre aux utilisateurs de traduire leur contenu afin d'afficher des randonnées dans plusieurs langues. Quelques champs sont configurables pour choisir les langues qui seront disponibles, nom ou description par exemple. C'est une étape supplémentaire à l'internationalisation avec Django pour l'affichage au grand public et non uniquement à l'utilisation de l'outil.

Afin de générer les champs traduits dans Geotrek-admin, le package modeltranslation est utilisé. Cela crée un champ par langue, par exemple avec le français, l'anglais et l'espagnol. Nous obtenons ainsi trois nouveaux champs :

• name_en
• name_fr
• name_es

Nous gardons également le champ name original.

Afin de récupérer l'ensemble des champs traduits, la configuration de modeltranslation est utilisée. Les vues SQL ont été modifiées en ajoutant un moyen de faire des boucles sur les langues. Le moyen le plus simple a été de repartir d'une base saine en utilisant le templating Django.

 

CREATE FUNCTION {{ schema_geotrek }}.topology_elevation_iu() RETURNS trigger SECURITY DEFINER AS $$
DECLARE
    elevation elevation_infos;
BEGIN
    IF {{ TREKKING_TOPOLOGY_ENABLED }} THEN
        RETURN NEW;
    END IF;
    SELECT * FROM ft_elevation_infos(NEW.geom, {{ ALTIMETRIC_PROFILE_STEP }}) INTO elevation;
    -- Update path geometry
    NEW.geom_3d := elevation.draped;
    NEW."length" := ST_3DLength(elevation.draped);
    NEW.slope := elevation.slope;
    NEW.min_elevation := elevation.min_elevation;
    NEW.max_elevation := elevation.max_elevation;
    NEW.ascent := elevation.positive_gain;
    NEW.descent := elevation.negative_gain;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Le seul changement ici est l'utilisation des balises standard de Django pour les schémas. Nous chargeons les templates avec des informations supplémentaires :

from django.template.loader import get_template
from django.conf import settings

template = get_template(sql_file)
context_settings = settings.__dict__['_wrapped'].__dict__ # Permet de créer un dictionnaire avec comme clé chacun des paramètres Django
context = dict(
                schema_geotrek=schema,
                schema_django=schema_django,
                spatial_reference=spatial_reference()
            )
context.update(context_settings)
rendered_sql = template.render(context)

Des modifications sont faites sur les schémas. La logique, elle, est gérée au niveau du python et non pas directement dans le template.

Il est possible d'ajouter n'importe quelle information pour le rendu du template.

rendered_sql = template.render({"foo": "bar"})

Nous installons ensuite le SQL généré après le rendu.

from django.db import connection
cursor = connection.cursor()
cursor.execute(rendered_sql)

À partir de ce moment, nous sommes capables d'utiliser le moteur de templating Django avec les boucles, conditions, etc.

 

À partir des vues précédemment créées, nous pouvons désormais récupérer les champs traduits

Avant

-- POI

CREATE VIEW v_pois AS WITH v_poi AS
    (SELECT e.geom,
            e.id,
            i.topo_object_id,
-- Il n''est pas possible de savoir quelle langue est disponible pour ces champs
            i.name,  :
            i.description,
            i.published,
-------------------------------------------
            i.type_id,
            i.publication_date,
            i.structure_id,
            i.review,
            i.eid,
            CONCAT (e.min_elevation, ' m') AS elevation,
            e.date_insert,
            e.date_update
     FROM trekking_poi i,
          core_topology e
     WHERE i.topo_object_id = e.id
         AND e.deleted = FALSE)
SELECT a.id,
       c.name AS "Structure",
       f.zoning_city AS "City",
       g.zoning_district AS "District",
       a.name AS "Name", -- Problème de langue
       b.label AS "Type",
       a.description AS "Description", -- Problème de langue
       a.eid AS "External ID",
       a.elevation AS "Elevation",
       a.published IS FALSE THEN 'No'
      CASE
          WHEN a.published IS FALSE THEN 'No'
          WHEN a.published IS TRUE THEN 'Yes'
       END AS "Published",
       a.publication_date AS "Publication date",
       a.date_insert AS "Insertion date",
       a.date_update AS "Update date",
       a.geom
FROM v_poi a
LEFT JOIN trekking_poitype b ON a.type_id = b.id
LEFT JOIN authent_structure c ON a.structure_id = c.id
LEFT JOIN
    (SELECT array_to_string(ARRAY_AGG (b.name), ', ', '') zoning_city,
            a.id
     FROM
         (SELECT e.geom,
                 e.id
          FROM trekking_poi i,
               core_topology e
          WHERE i.topo_object_id = e.id
              AND e.deleted = FALSE) a
     JOIN zoning_city b ON ST_INTERSECTS (a.geom, b.geom)
     GROUP BY a.id) f ON a.id = f.id
LEFT JOIN
    (SELECT array_to_string(ARRAY_AGG (b.name), ', ', '') zoning_district,
            a.id
     FROM
         (SELECT e.geom,
                 e.id
          FROM trekking_poi i,
               core_topology e
          WHERE i.topo_object_id = e.id
              AND e.deleted = FALSE) a
     JOIN zoning_district b ON ST_INTERSECTS (a.geom, b.geom)
     GROUP BY a.id) g ON a.id = g.id;

Après

-- POI

CREATE VIEW {{ schema_geotrek }}.v_pois AS WITH v_poi AS
    (SELECT e.geom,
            e.id,
            i.topo_object_id,
----
-- On boucle ici sur les langues disponibles dans Geotrek-admin
-- On génère ainsi du SQL rendu sous cette forme : 
-- i.name_fr,
-- i.description_fr,
-- i.published_fr,
-- i.name_en,
-- i.description_en,
-- i.published_en,
-- i.name_es,
-- i.description_es,
-- i.published_es,
----
            {% for lang in MODELTRANSLATION_LANGUAGES %} 
                i.name_{{ lang }},
                i.description_{{ lang }},
                i.published_{{ lang }},
            {% endfor %}
            i.type_id,
            i.publication_date,
            i.structure_id,
            i.review,
            i.eid,
            CONCAT (e.min_elevation, ' m') AS elevation,
            e.date_insert,
            e.date_update
     FROM trekking_poi i,
          core_topology e
     WHERE i.topo_object_id = e.id
         AND e.deleted = FALSE)
SELECT a.id,
       c.name AS "Structure",
       f.zoning_city AS "City",
       g.zoning_district AS "District",
       {% for lang in MODELTRANSLATION_LANGUAGES %}
        a.name_{{ lang }} AS "Name {{ lang }}",
       {% endfor %}
       b.label AS "Type",
       {% for lang in MODELTRANSLATION_LANGUAGES %}
        a.description_{{ lang }} AS "Description {{ lang }}",
       {% endfor %}
       a.eid AS "External ID",
       a.elevation AS "Elevation",
----
-- On boucle encore sur les langues
-- Cela génère : 
-- CASE
--    WHEN a.published_en IS FALSE THEN 'No'
--    WHEN a.published_en IS TRUE THEN 'Yes'
-- END AS "Published en",
-- CASE
--    WHEN a.published_fr IS FALSE THEN 'No'
--    WHEN a.published_fr IS TRUE THEN 'Yes'
-- END AS "Published fr",
-- CASE
--    WHEN a.published_es IS FALSE THEN 'No'
--    WHEN a.published_es IS TRUE THEN 'Yes'
-- END AS "Published es",
----
       {% for lang in MODELTRANSLATION_LANGUAGES %}
           CASE
               WHEN a.published_{{ lang }} IS FALSE THEN 'No'
               WHEN a.published_{{ lang }} IS TRUE THEN 'Yes'
           END AS "Published {{ lang }}",
       {% endfor %}
       a.publication_date AS "Publication date",
       a.date_insert AS "Insertion date",
       a.date_update AS "Update date",
       a.geom
FROM v_poi a
LEFT JOIN trekking_poitype b ON a.type_id = b.id
LEFT JOIN authent_structure c ON a.structure_id = c.id
LEFT JOIN
    (SELECT array_to_string(ARRAY_AGG (b.name), ', ', '*') zoning_city,
            a.id
     FROM
         (SELECT e.geom,
                 e.id
          FROM trekking_poi i,
               core_topology e
          WHERE i.topo_object_id = e.id
              AND e.deleted = FALSE) a
     JOIN zoning_city b ON ST_INTERSECTS (a.geom, b.geom)
     GROUP BY a.id) f ON a.id = f.id
LEFT JOIN
    (SELECT array_to_string(ARRAY_AGG (b.name), ', ', '*') zoning_district,
            a.id
     FROM
         (SELECT e.geom,
                 e.id
          FROM trekking_poi i,
               core_topology e
          WHERE i.topo_object_id = e.id
              AND e.deleted = FALSE) a
     JOIN zoning_district b ON ST_INTERSECTS (a.geom, b.geom)
     GROUP BY a.id) g ON a.id = g.id ;

Pour retrouver cette vue dans le code source, vous pouvez vous rendre ici

 

Visualiser les vues SQL dans QGIS

Créer une connexion à la base de données PostgreSQL/PostGIS du projet

1. Ouvrir le logiciel QGIS

2. Créer une nouvelle connexion de base de données PostgreSQL/PostGIS

• Dans l’Explorateur > PostGIS > Nouvelle connexion

• Renseigner les informations suivantes :

  • Nom de la connexion (celle que vous voulez)

  • Hôte (IP ou URL)

  • Port (par défaut 5432)

  • Base de données (nom de la base de données contenant les données à visualiser)

  • SSL mode : permet (négocie la connexion SSL avec le serveur. le mode "Permet" essaye d'abord une connexion non-SSL, puis en cas d’échec, essaye une connexion SSL.

  • Nom d’utilisateur (nom d'utilisateur postgres de la base de données)

  • Mot de passe (mot de passe postgres de la base de données)

• Cliquer sur « Tester la connexion »

• Si la connexion est réussie, cliquer sur le bouton "OK" pour enregistrer la connexion

Capture d'écran exemple pour la création d'une nouvelle connexion PostGIS dans QGIS

/!\ Cette procédure est valable seulement si le serveur PostreSQL écoute les connexions vers l'extérieur. Dans le cas contraire, une connexion SSH peut être créée au serveur PostgreSQL (solution recommandée et sécurisée). Voir cet article : "Accéder à sa base de données PostgreSQL depuis QGIS ou pgAdmin de manière sécurisée" pour connaître les étapes à suivre.

 

Créer un projet QGIS à partir des vues SQL

Capture d'écran du Projet QGIS présentant les vues SQL Geotrek du Conseil Départemental des Pyrénées Orientales (CD66)

Importer une vue SQL sous forme de couche

• Dans l’Explorateur > PostGIS > Ouvrir la connexion précédemment créée > Schéma public

• Ajouter les vues : Clic droit sur l’objet > Ajouter la couche au projet

Afficher un fond de plan OpenStreetMap

• Dans l’Explorateur > XYZ Tiles > OpenStreetMap

Changer le style d’une couche

• Clic droit sur la couche > Propriétés > Symbologie

Dimensionner les colonnes de la table attributaire

Le fait de dimensionner la taille des colonnes dans la table attributaire offre une lisibilité des noms de champs et des informations contenues à l’intérieur :

• Clic droit sur la couche > Ouvrir la Table d’Attributs > clic droit sur une colonne > Taille autom pour toutes les colonnes

Certains champs texte peuvent être très larges, dans ce cas, il est possible d’adapter manuellement la taille de la colonne :

• Clic droit sur la couche > Ouvrir la Table d’Attributs > clic droit sur la colonne > Largeur > Entrer une largeur de colonne (exemple : 200)

Selon le type géométrique de la couche (point, ligne, polygone), il est possible de changer à volonté la couleur de remplissage, la couleur de contour, la taille ou l’épaisseur.

 

Afficher le décompte des entités d’une couche

• Clic droit sur la couche > Afficher le nombre d’entités

Zoomer sur l’emprise d’une couche

• Clic droit sur la couche > Zoomer sur la(les) couches

Cette procédure est extraite de la documentation de Geotrek

 

Conclusion

Nous pouvons affirmer que l'utilisation de vues SQL dans Django est une manière alternative de servir de la donnée formatée sans avoir à passer par d'autres méthodes. Dans le cas de besoins spécifiques pour des interfaçages avec d'autres outils, l'utilisation de ces vues permet de refléter la structure de la base données tout en respectant les standards de Django. Pour des besoins plus spécifiques, comme dans le cas de langues multiples dans l'exemple choisi pour illustrer cet article, Django propose un ensemble d'outils pour itérer et conditionner du code SQL.