Nous vous conseillons de décompresser tous les fichiers sql (.sql) et batch (.bat) dans un répertoire de votre choix, en sachant que pour leur utilisation vous devrez changer le répertoire utilisé dans les scripts par le nom de votre répertoire. Pour la symbologie, comme vous verrez plus loin vous aurez le choix de créer soit un répertoire par défaut :C:/nautical ((recommandé), soit un répertoire de votre choix.
Si vous vous lancez à travailler avec des fichiers S57 dans un SIG, il y a quelques éléments que vous devez connaître pour naviguer sans écueils.
Tout d’abord, la structure des fichiers S57 ne répond pas aux structures adoptées dans les SIG.
Dans un SIG vous avez un objet géographique qui est représenté par une table avec deux types d’informations : la géométrie des entités de l’objet et les attributs de ces entités.
Si vous avez d’autres objets qui ont des géométries identiques, l’information géométrique est dupliquée, une fois dans chaque table.
Pour le format S57 l’objectif principal est d’optimiser le stockage des informations et par conséquent de ne pas dupliquer les informations. Si un objet a une entité ponctuelle, un point sera créé. Du moment ou d’autres objets ont des entités situées sur ce point, on utilisera la référence du point qui a déjà été créé. De cette manière un point n’est décrit qu’une seule fois dans le fichier. Il en va de même pour les polylignes et les surfaces. Un fichier S57 aura donc une série de tables contenant l’information géométrique, dénommées « primitives » :
IsolatedNode (points)
ConnectedNode (points)
Edge (polylignes)
Face (polygones)
La table attributaire des différents objets S57 contient seulement les attributs des objets.
Les « couches » S57 sont des classes d’objets. Par exemple, les différentes zones terrestres sont codées dans la classe d’objets LNDARE.
La définition de cette classe d’objets est :
Geo object: Land area (LNDARE) (P,L,A) Attributes: CONDTN OBJNAM NOBJNM STATUS INFORM NINFOM
Si tous les objets LNDARE possèdent les mêmes attributs, il n’en est pas de même du type de géométrie. L’information (P, L, A) indique que, dans cette classe d’objets on peut trouver des points, des lignes et des polygones. Contrairement aux standards SIG, les trois types de géométrie coexistent au sein de la même classe d’objets.
Pour passer du format S57 a une base Postgresql/Postgis nous utiliserons la bibliothèque GDAL, incluse avec QGis.
Les opérations avec GDAL seront effectuées par ligne de commande dans une fenêtre OSGeo4W.
La syntaxe de base pour traiter un fichier S57 (extension .000) et l’importer dans une base de données Postgtresql/Postgis est la suivante :
La première permet de continuer le traitement même si des erreurs sont détectées. Les deux suivantes permettent de ne pas écraser les tables postgresql en sortie si elles existent mais de les mettre à jour en ajoutant les données du fichier S57 en entrée.
Quel qu’il soit le format choisi pour l’intégrer dans QGis, nous aurons à créer une couche par type de géométrie. Si on ne le fait pas, GDAL va créer le type de couche en fonction de la première entité trouvée lors de la conversion. Si elle est de type point, la couche créée sera de type point et les entités lignes et polygones seront ignorées. De même, si la première est de type ligne, ce seront les points et les polygones qui seront ignorés.
Il faut aussi noter que le format S57 n’a aucune contrainte sur les classes d’objets : si dans la zone couverte par le fichier S57 il n’y a pas une classe d’objet, il n’y aura rien la concernant dans le fichier (pas de couche vide). De même, s’il n’y a qu’un type de géométrie présente, alors que les trois types sont possibles, il n’y aura pas de trace des autres types de géométrie.
Il faudra alors décomposer le traitement d’un fichier S57 avec ogr2ogr en trois étapes, une pour chaque type de géométrie. Les options suivantes permettent de traiter chaque classe d’objet S57 en ne sélectionnant qu’un type de géométrie :
-where « OGR_GEOMETRY=’POINT’ or OGR_GEOMETRY=’MULTIPOINT’ »
-where « OGR_GEOMETRY=’LINESTRING’ or OGR_GEOMETRY=’MULTILINESTRING’ »
-where « OGR_GEOMETRY=’POLYGON’ or OGR_GEOMETRY=’MULTIPOLYGON’ »
GDAL permet pour certains types de drivers de créer des préfixes dans les tables en sortie. Dans ce cas on pourrait créer toutes les tables (points, lignes, polygones) dans un seul schéma en les préfixant par exemple avec pt_, li_ et pl_. Le problème est que le driver S57 de GDAL ne permet pas cette option. On doit alors créer trois schémas distincts, dans lesquels les tables seront créées selon le type de géométrie. Dans chaque schéma il y aura alors une table de même nom mais de géométrie différente.
Nous allons utiliser ici trois schémas d’import pour les commandes ogr2ogr dans lesquels nous allons importer les tables d’un fichier S57. Nous les appellerons pointsenc, linesenc et polysenc. D’autre part nous allons créer un schéma appelé ENC pour la base de données complète. Pour chaque carte ENC nous allons importer son contenu dans les trois schémas d’import avec ogr2ogr puis nous allons exécuter des requêtes SQL pour mettre à jour la base de données du schéma ENC avec les nouvelles tables importées. Que vous ayez un seul fichier S57 à charger ou bien un lot de fichiers S57 à charger simultanément, le processus est le suivant :
Chargement des classes d’objets Point dans le schéma pointsENC avec ogr2ogr
Chargement des classes d’objets Lignes dans le schéma LinesENC avec ogr2ogr
Chargement des classes d’objets Polygone dans le schéma PolysENC avec ogr2ogr
Suppression des tables vides des trois schémas
Mise à jour des tables existantes dans le schéma ENC à partir des trois schémas d’import
Clonage des tables des trois schémas d’import qui n’étaient pas présentes dans le schéma ENC,
Les sondes bathymétriques posent plusieurs problèmes lors de la conversion de format. Le premier est que la valeur de profondeur n’est pas une contenue dans un attribut, mais en tant que Z dans la géométrie (XYZ). Le deuxième est que les sondes ne sont pas du type Point mais Multipoint. Pour avoir directement la valeur des sondes dans un champ attributaire, il faut donc ajouter deux paramètres à la ligne de commande ogr2ogr:
-oo SPLIT_MULTIPOINT=ON -oo ADD_SOUNDG_DEPTH=ON
Le premier converti les entités multipoint en points individuels, et le deuxième ajoute un champ attributaire « DEPTH » à la table en sortie avec la valeur Z de la géométrie.
Certains objets peuvent être regroupés par un objet non géographique parent. Par exemple, les secteurs de feu sont codés avec un secteur par enregistrement de la table « LIGHTS ». Les différents secteurs d’un même feu n’ont aucun attribut particulier qui puisse indiquer qu’ils correspondant à la même entité. Cette information se trouve dans un enregistrement « parent ». Pour récupérer cet identifiant en tant qu’attribut de la table LIGTHS, il faut ajouter une option à la ligne de commande :
-oo RETURN_LINKAGES=O
Cette option crée un attribut name_rcid qui est commun à tous les secteurs d’un même feu, mais créé aussi une série de champs (name_rcnm,ORNT,USAG,MASK).
Le format S57 utilise, en plus des types de champ classiques (entier, réel, texte) un type particulier : les listes de chaînes de caractères ou d’entiers. Si on retrouve ces types de champ dans PostgreSQL, il n’en va pas de même dans les shapefiles et le geopackage. Il n’est donc pas nécessaire de transformer les listes S57 en chaînes de caractères comme pour ces formats. Il est même fortement déconseillé car cela compliquerait le traitements des attributs de type liste.
Quand vous chargez plusieurs cartes ENC dans une même base de données vous pouvez vous confronter à une duplication des informations quand l’emprise de deux ou plusieurs cartes se superposent.
Ces doublons peuvent être de deux types différents :
Des « vrais » doublons où tous les attributs sont les mêmes. Ils sont rares car ils proviennent de la superposition de deux emprises de cartes marines avec une échelle très proche. Par contre ils peuvent aussi résulter d’une erreur de rechargement en double d’un fichier S57. Ces doublons peuvent être supprimés.
Des « faux » doublons où l’information de la couche se retrouve en double sans que les identifiants des enregistrements soient forcément identiques. Ils apparaissent quand des zones cartographiées à différentes échelles sont chargées dans la même base de données. Aussi, selon l’échelle une même information (par exemple une obstruction) peut être de géométrie différente (point sur carte, surface sur l’autre). Ce type de doublon ne doit pas être supprimé, mais géré.
Pour pouvoir gérer ces doublons, mais aussi l’affichage correct des informations, il est nécessaire d’ajouter des attributs de la carte aux couches. En effet, une fois intégrées dans une couche les entités de différents fichiers n’ont aucun attribut qui permet de remonter à leur source.
Nous avons inclus dans la procédure d’import SQL le code nécessaire qui permet d’ajouter à toutes les tables du schéma le nom du fichier, l’échelle de compilation des données, et la finalité de la carte marine.
Pour ce qui est du nom, il peut être utile dans la maintenance ultérieure de la base de données de pouvoir sélectionner les entités correspondantes à un fichier source S57.
Si le nom de la carte est d’une utilité relative, il n’en va pas de même de l’échelle, car c’est un critère indispensable dans la recherche de doublons. En effet, lors du mélange des entités en provenance de plusieurs fichiers S57 il y aura une coexistence plus ou moins heureuse de données issues de différentes échelles.
Outre l’échelle, il y a une information qui peut s’avérer très utile, la finalité (purpose) de la carte. C’est une valeur comprise entre 1 et 6 et qui correspond à l’objectif principal de la carte :
1 : Vue d’ensemble
2 : Généralités
3 : Côtière
4 : Approche
5 : Port
6 : Accostage
Dans la base de données du projet avec PostgreSQL/Postgis, nous avons importé 4500 fichiers S57. Les valeurs min et max de l’échelle de toutes les cartes pour l’objectif 5 sont 3000 et 60000. Les valeurs min et max de l’échelle pour l’objectif 6 sont 2500 et 15000. On voit bien que les valeurs d’échelle des cartes les plus détaillées se retrouvent à l’intérieur des cartes de type 5.
Voici le résultat pour l’ensemble des finalités :
Tableau des finalités et des échelles correspondantes
Purpose
min_scale
max_scale
1
325000
10000000
2
100000
1534076
3
50000
600000
4
12500
150000
5
3000
60000
6
2500
15000
La table DSID des fichiers S57 contient deux attributs avec le nom du fichier S57 et l’échelle de compilation des données, ainsi que l’attribut ‘finalité’. Cet attribut se retrouve dans la même table DSID utilisée pour récupérer l’échelle avec le nom de DSID_INTU.
Comme les commandes ogr2ogr ne chargent, par défaut, que les tables spatiales, nous devrons exécuter une commande spéciale en demandant le chargement de la table DSID:
Un fichier S57 a donc une série de tables contenant l’information géométrique, dénommées « primitives » :
IsolatedNode (points)
ConnectedNode (points)
Edge (polylignes)
Face (polygones)
La table attributaire des différents objets S57 contient seulement les attributs des objets.
Ce qui complique la tâche c’est qu’il y a deux attributs qui se réfèrent aux géométries : posacc (l’estimation de la précision de la position, valeur quantitative) et quapos (qualité de la position, variable qualitative).
Ces deux attributs se trouvent dans les tables des primitives.
Pour passer de la structure S57 à une structure SIG (shapefile, geopackage, postgis) nous utilisons la bibliothèque GDAL et sa commande ogr2ogr.
Cette commande va créer des tables SIG à partir de la structure S57, en créant une table par objet S57, en affectant à chaque entité les géométries correspondantes tirées des tables primitives et en ajoutant à chaque entité les attributs de l’objet S57. La trace des primitives utilisées pour la géométrie de chaque entité se trouve dans le champ NAME_RCID des tables SIG, à condition d’avoir ajouté les options -oo « RETURN_LINKAGES=ON » –oo « LNAM_REFS=ON » à la ligne de commande ogr2ogr.
La figure suivante montre une couche de type point. La valeur indiquée dans le champ NAME_RCID est celle RCID du point utilisé de la table IsolatedNode.
La figure suivante montre un exemple de couche de type linéaire. Les valeurs indiquées dans le champ NAME_RCID sont celles des RCID des polylignes utilisées de la table Edge.
La figure suivante montre un exemple de couche de type polygone. Les valeurs indiquées dans le champ NAME_RCID sont celles des polylignes utilisées de la table Edge.
Pour pouvoir récupérer les attributs QUAPOS et POSACC de chaque entité des tables de type point nous devons récupérer les valeurs du point IsolatedNode pour les affecter aux tables des différents objets ENC.
Si les identifiants étaient directement les RCID dans les tables ENC, on pourrait faire une jointure entre chaque table (soundg, obstrn,…) et IsolatedNode. Mais comme vous pouvez le voir dans les images précédentes, l’attribut NAME_RCID est de type stringlist ce qui bloque cette solution. Nous avons donc développé une requête SQL qui fait ce travail lors du chargement des données des schémas d’import vers le schéma ENC.
Pour créer les tables d’import dans un schéma spécifique il faut utiliser l’option active_schema d’ogr2ogr. Avec cette option, PostgreSQL permet de créer des tables avec le même nom dans plusieurs schémas différents. Il n’y a qu’une seule exception, le schéma Public. Si un nom de table est utilisé dans ce schéma, l’option active_schema est ignorée. Il faut donc faire attention à ne pas créer des tables S57 dans le schéma Public. Si cela arrive, il faudra les effacer une à une manuellement.
La requête suivante crée les quatre schémas nécessaires :
createschemas.sql
CREATE SCHEMA IF NOT EXISTS encm AUTHORIZATION pg_database_owner; GRANT ALL ON SCHEMA encm TO pg_database_owner; CREATE SCHEMA IF NOT EXISTS pointsenc AUTHORIZATION pg_database_owner; GRANT ALL ON SCHEMA pointsenc TO pg_database_owner; CREATE SCHEMA IF NOT EXISTS linesenc AUTHORIZATION pg_database_owner; GRANT ALL ON SCHEMA linesenc TO pg_database_owner; CREATE SCHEMA IF NOT EXISTS polysenc AUTHORIZATION pg_database_owner; GRANT ALL ON SCHEMA polysenc TO pg_database_owner;
Pour créer les schémas, ouvrez pgAdmin4, et sur Schemas ouvrez le menu contextuel->Create-Schema
Entrez le nom souhaité pour le schéma:
Répétez cette opération pour créer les quatre schémas nécessaires pour notre base de données ENC
ATTENTION! Les deux méthodes de création ne donnent pas le même résultat. Quand vous utilisez pgAdmin, le nom des schémas comporte des majuscules et minuscules et sera encadré par des guillemets (« PointsENC », par exemple). Quand vous utilisez le SQL, les noms seront toujours en minuscules et il n’y aura pas de guillemets (pointsenc par exemple).Pour éviter ceci, n’utilisez pas de majuscules dans pgAdmin.
Dans la suite de cet article nous utiliserons les noms issus de la requête SQL. Si vous optez par l’utilisation de majuscules, il faudra modifier les noms des schémas dans les codes fournis.
La phase d’import des tables avec ogr2ogr ne se limite pas à la simple exécution de la commande de transcodage S57->Postgresql.
Une fois le schéma alimenté par les tables des classes du fichier S57, nous allons ajouter la table DSID qui doit permettre de récupérer le nom du fichier S57, l’échelle de compilation des données et la finalité de la carte.
Au préalable, il faut ajouter ces attributs à toutes les tables non vides résultant de la commande ogr2ogr, en profitant déjà à créer aussi les attributs QUAPOS et POSACC qui seront remplis ultérieurement.
Le principe de cette étape est donc :
Dans le fichier batch (.bat) on exécute la commande de chargement d’un type de géométrie.
Une fois cette ligne de code exécutée, on charge dans le même schéma la table DSID
Un trigger de type AFTER INSERT sur la table dsid du schéma commence par supprimer les tables vides, puis crée les attributs enc_chart, scale, purpose, posacc et quapos dans toutes les tables. Il ajoute dans tous les enregistrements des tables les valeurs de enc_chart,scale et purpose.
Cette procédure est faite pour chaque type de géométrie : point, ligne et polygone.
Pour que cette procédure fonctionne nous avons quelques éléments à mettre en place dans la base Postgresql.
Étant donné qu’on utilisera un trigger sur ces tables, il faut les créer au préalable du premier chargement. On va créer donc, dans chaque schéma d’import une table
Pointsdsid dans le schéma pointsenc
Linesdsid dans le schéma linesenc
Polysdsid dans le schéma polysenc
create_DSID_tables.sql
-- Création de la séquence pointsdsid_ogc_fid_seq CREATE SEQUENCE IF NOT EXISTS pointsenc.pointsdsid_ogc_fid_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- Création de la séquence polysdsid_ogc_fid_seq
CREATE SEQUENCE IF NOT EXISTS polysenc.polysdsid_ogc_fid_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- Création de la séquence linesdsid_ogc_fid_seq
CREATE SEQUENCE IF NOT EXISTS linesenc.linesdsid_ogc_fid_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- Table: pointsenc.pointsdsid
-- DROP TABLE IF EXISTS pointsenc.pointsdsid;
CREATE TABLE IF NOT EXISTS pointsenc.pointsdsid ( ogc_fid integer NOT NULL DEFAULT nextval('pointsenc.pointsdsid_ogc_fid_seq'::regclass), dsid_expp numeric(3,0), dsid_intu numeric(3,0), dsid_dsnm character varying COLLATE pg_catalog."default", dsid_edtn character varying COLLATE pg_catalog."default", dsid_updn character varying COLLATE pg_catalog."default", dsid_uadt character varying(8) COLLATE pg_catalog."default", dsid_isdt character varying(8) COLLATE pg_catalog."default", dsid_sted numeric(11,6), dsid_prsp numeric(3,0), dsid_psdn character varying COLLATE pg_catalog."default", dsid_pred character varying COLLATE pg_catalog."default", dsid_prof numeric(3,0), dsid_agen numeric(5,0), dsid_comt character varying COLLATE pg_catalog."default", dssi_dstr numeric(3,0), dssi_aall numeric(3,0), dssi_nall numeric(3,0), dssi_nomr numeric(10,0), dssi_nocr numeric(10,0), dssi_nogr numeric(10,0), dssi_nolr numeric(10,0), dssi_noin numeric(10,0), dssi_nocn numeric(10,0), dssi_noed numeric(10,0), dssi_nofa numeric(10,0), dspm_hdat numeric(3,0), dspm_vdat numeric(3,0), dspm_sdat numeric(3,0), dspm_cscl numeric(10,0), dspm_duni numeric(3,0), dspm_huni numeric(3,0), dspm_puni numeric(3,0), dspm_coun numeric(3,0), dspm_comf numeric(10,0), dspm_somf numeric(10,0), dspm_comt character varying COLLATE pg_catalog."default", CONSTRAINT pointsdsid_pkey PRIMARY KEY (ogc_fid) )
TABLESPACE pg_default;
ALTER TABLE IF EXISTS pointsenc.pointsdsid OWNER to postgres; -- Table: polysenc.polysdsid
-- DROP TABLE IF EXISTS polysenc.polysdsid;
CREATE TABLE IF NOT EXISTS polysenc.polysdsid ( ogc_fid integer NOT NULL DEFAULT nextval('polysenc.polysdsid_ogc_fid_seq'::regclass), dsid_expp numeric(3,0), dsid_intu numeric(3,0), dsid_dsnm character varying COLLATE pg_catalog."default", dsid_edtn character varying COLLATE pg_catalog."default", dsid_updn character varying COLLATE pg_catalog."default", dsid_uadt character varying(8) COLLATE pg_catalog."default", dsid_isdt character varying(8) COLLATE pg_catalog."default", dsid_sted numeric(11,6), dsid_prsp numeric(3,0), dsid_psdn character varying COLLATE pg_catalog."default", dsid_pred character varying COLLATE pg_catalog."default", dsid_prof numeric(3,0), dsid_agen numeric(5,0), dsid_comt character varying COLLATE pg_catalog."default", dssi_dstr numeric(3,0), dssi_aall numeric(3,0), dssi_nall numeric(3,0), dssi_nomr numeric(10,0), dssi_nocr numeric(10,0), dssi_nogr numeric(10,0), dssi_nolr numeric(10,0), dssi_noin numeric(10,0), dssi_nocn numeric(10,0), dssi_noed numeric(10,0), dssi_nofa numeric(10,0), dspm_hdat numeric(3,0), dspm_vdat numeric(3,0), dspm_sdat numeric(3,0), dspm_cscl numeric(10,0), dspm_duni numeric(3,0), dspm_huni numeric(3,0), dspm_puni numeric(3,0), dspm_coun numeric(3,0), dspm_comf numeric(10,0), dspm_somf numeric(10,0), dspm_comt character varying COLLATE pg_catalog."default", CONSTRAINT polysdsid_pkey PRIMARY KEY (ogc_fid) )
TABLESPACE pg_default;
ALTER TABLE IF EXISTS polysenc.polysdsid OWNER to postgres; -- Table: linesenc.linesdsid
-- DROP TABLE IF EXISTS linesenc.linesdsid;
CREATE TABLE IF NOT EXISTS linesenc.linesdsid ( ogc_fid integer NOT NULL DEFAULT nextval('linesenc.linesdsid_ogc_fid_seq'::regclass), dsid_expp numeric(3,0), dsid_intu numeric(3,0), dsid_dsnm character varying COLLATE pg_catalog."default", dsid_edtn character varying COLLATE pg_catalog."default", dsid_updn character varying COLLATE pg_catalog."default", dsid_uadt character varying(8) COLLATE pg_catalog."default", dsid_isdt character varying(8) COLLATE pg_catalog."default", dsid_sted numeric(11,6), dsid_prsp numeric(3,0), dsid_psdn character varying COLLATE pg_catalog."default", dsid_pred character varying COLLATE pg_catalog."default", dsid_prof numeric(3,0), dsid_agen numeric(5,0), dsid_comt character varying COLLATE pg_catalog."default", dssi_dstr numeric(3,0), dssi_aall numeric(3,0), dssi_nall numeric(3,0), dssi_nomr numeric(10,0), dssi_nocr numeric(10,0), dssi_nogr numeric(10,0), dssi_nolr numeric(10,0), dssi_noin numeric(10,0), dssi_nocn numeric(10,0), dssi_noed numeric(10,0), dssi_nofa numeric(10,0), dspm_hdat numeric(3,0), dspm_vdat numeric(3,0), dspm_sdat numeric(3,0), dspm_cscl numeric(10,0), dspm_duni numeric(3,0), dspm_huni numeric(3,0), dspm_puni numeric(3,0), dspm_coun numeric(3,0), dspm_comf numeric(10,0), dspm_somf numeric(10,0), dspm_comt character varying COLLATE pg_catalog."default", CONSTRAINT linesdsid_pkey PRIMARY KEY (ogc_fid) )
TABLESPACE pg_default;
ALTER TABLE IF EXISTS linesenc.linesdsid OWNER to postgres;
Ces triggers vont, après l’insertion d’une ligne dans la table DSID du schéma,
Effacer les tables vides présentes dans le schéma,
vérifier si les champs supplémentaires existent déjà, si ce n’est pas le cas créer les 5 attributs ‘(enc_chart, scale, purpose,posacc et quapos),
ajouter les valeurs de l’enregistrement DSID aux attributs enc_chart, scale et purpose,
et finalement effacer l’enregistrement de la table DSID.
Il y a une fonction par type de géométrie,
create_fields_and_update_values_pointsenc()
create_fields_and_update_values_linesenc()
create_fields_and_update_values_polysenc()
puis une requête SQL de mise en place des triggers sur chacune des tables DSID (pointsDSID,linesDSID,polysDSID)
create_fields_and_update_values_pointsenc()
CREATE OR REPLACE FUNCTION create_fields_and_update_values_pointsenc()
RETURNS TRIGGER AS $$ DECLARE table_record RECORD; tables_import RECORD; enc_chart_value TEXT; scale_value NUMERIC; purpose_value NUMERIC; empty_tables int; BEGIN --Le code suivant permet de supprimer les tables vides d'un schéma d'import: FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'pointsenc' AND table_name != 'pointsdsid' LOOP -- Composer une requête dynamique pour vérifier si la table est vide EXECUTE format('SELECT COUNT(*) FROM pointsenc.%I', table_record.table_name) INTO empty_tables;
-- Si le nombre de lignes est égal à zéro, supprimer la table
IF empty_tables = 0 THEN
EXECUTE format('DROP TABLE IF EXISTS pointsenc.%I CASCADE', table_record.table_name);
RAISE NOTICE 'Table pointsenc.%I supprimée car elle est vide.', table_record.table_name;
END IF;
END LOOP;
-- Parcours de toutes les tables du schéma pointsenc
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'pointsenc' AND table_name != 'pointsdsid' LOOP
-- Vérifie si les champs enc_chart et scale n'existent pas dans la table actuelle
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'pointsenc' AND table_name = table_record.table_name AND column_name IN ('enc_chart', 'scale','purpose')
) THEN
-- Crée le champ enc_chart de type texte
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN enc_chart TEXT', table_record.table_name);
-- Crée le champ scale de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN scale NUMERIC', table_record.table_name);
-- Crée le champ purpose de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN purpose NUMERIC', table_record.table_name);
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'pointsenc' AND table_name = table_record.table_name AND column_name IN ('posacc','quapos')
) THEN
-- Crée le champ POSACC de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN posacc NUMERIC(10,0)', table_record.table_name);
-- Crée le champ QUAPOS de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN quapos INTEGER', table_record.table_name);
END IF;
RAISE NOTICE 'Champs enc_chart, scale , purpose, POSACC et QUIAPOS créés dans la table %', table_record.table_name;
END IF;
-- Obtient la valeur de enc_chart à partir de la table DSID pour DSID_DSNM
SELECT DSID_DSNM INTO enc_chart_value FROM pointsdsid LIMIT 1;
-- Obtient la valeur de scale à partir de la table DSID pour DSPM_CSCL
SELECT DSPM_CSCL INTO scale_value FROM pointsdsid LIMIT 1;
-- Obtient la valeur de purpose à partir de la table DSID pour DSID_INTU
SELECT DSID_INTU INTO purpose_value FROM pointsdsid LIMIT 1;
-- Met à jour les enregistrements avec les valeurs trouvées dans la table DSID
EXECUTE format('UPDATE pointsenc.%I SET enc_chart = $1 WHERE enc_chart IS NULL', table_record.table_name) USING enc_chart_value;
EXECUTE format('UPDATE pointsenc.%I SET scale = $1 WHERE scale IS NULL', table_record.table_name) USING scale_value;
EXECUTE format('UPDATE pointsenc.%I SET purpose = $1 WHERE purpose IS NULL', table_record.table_name) USING purpose_value;
END LOOP;
-- Efface l'enregistrement de la table DSID
DELETE FROM pointsenc.pointsdsid;
RETURN NULL;
END; $$ LANGUAGE plpgsql;
create_fields_and_update_values_linesenc()
CREATE OR REPLACE FUNCTION create_fields_and_update_values_linesenc() RETURNS TRIGGER AS $$ DECLARE table_record RECORD; tables_import RECORD; enc_chart_value TEXT; scale_value NUMERIC; purpose_value NUMERIC; empty_tables int; BEGIN --Le code suivant permet de supprimer les tables vides d'un schéma d'import: FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'linesenc' AND table_name != 'linesdsid' LOOP -- Composer une requête dynamique pour vérifier si la table est vide EXECUTE format('SELECT COUNT(*) FROM linesenc.%I', table_record.table_name) INTO empty_tables;
-- Si le nombre de lignes est égal à zéro, supprimer la table
IF empty_tables = 0 THEN
EXECUTE format('DROP TABLE IF EXISTS linesenc.%I CASCADE', table_record.table_name);
RAISE NOTICE 'Table linesenc.%I supprimée car elle est vide.', table_record.table_name;
END IF;
END LOOP;
-- Parcours de toutes les tables du schéma linesenc
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'linesenc' AND table_name != 'linesdsid' LOOP
-- Vérifie si les champs enc_chart et scale n'existent pas dans la table actuelle
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'linesenc' AND table_name = table_record.table_name AND column_name IN ('enc_chart', 'scale','purpose')
) THEN
-- Crée le champ enc_chart de type texte
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN enc_chart TEXT', table_record.table_name);
-- Crée le champ scale de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN scale NUMERIC', table_record.table_name);
-- Crée le champ purpose de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN purpose NUMERIC', table_record.table_name);
RAISE NOTICE 'Champs enc_chart, scale et purpose créés dans la table %', table_record.table_name;
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'linesenc' AND table_name = table_record.table_name AND column_name IN ('posacc','quapos')
) THEN
-- Crée le champ POSACC de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN POSACC NUMERIC(10,0)', table_record.table_name);
-- Crée le champ QUAPOS de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN QUAPOS INTEGER', table_record.table_name);
END IF;
RAISE NOTICE 'Champs enc_chart, scale et purpose créés dans la table %', table_record.table_name;
END IF;
-- Obtient la valeur de enc_chart à partir de la table DSID pour DSID_DSNM
SELECT DSID_DSNM INTO enc_chart_value FROM linesenc.linesdsid LIMIT 1;
-- Obtient la valeur de scale à partir de la table DSID pour DSPM_CSCL
SELECT DSPM_CSCL INTO scale_value FROM linesenc.linesdsid LIMIT 1;
-- Obtient la valeur de purpose à partir de la table DSID pour DSID_INTU
SELECT DSID_INTU INTO purpose_value FROM linesenc.linesdsid LIMIT 1;
-- Met à jour les enregistrements avec les valeurs trouvées dans la table DSID
EXECUTE format('UPDATE linesenc.%I SET enc_chart = $1 WHERE enc_chart IS NULL', table_record.table_name) USING enc_chart_value;
EXECUTE format('UPDATE linesenc.%I SET scale = $1 WHERE scale IS NULL', table_record.table_name) USING scale_value;
EXECUTE format('UPDATE linesenc.%I SET purpose = $1 WHERE purpose IS NULL', table_record.table_name) USING purpose_value;
END LOOP;
-- Efface l'enregistrement de la table DSID
DELETE FROM linesenc.linesdsid;
RETURN NULL;
END; $$ LANGUAGE plpgsql;
create_fields_and_update_values_polysenc()
CREATE OR REPLACE FUNCTION create_fields_and_update_values_polysenc() RETURNS TRIGGER AS $$ DECLARE table_record RECORD; tables_import RECORD; enc_chart_value TEXT; scale_value NUMERIC; purpose_value NUMERIC; empty_tables int; BEGIN --Le code suivant permet de supprimer les tables vides d'un schéma d'import: FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'polysenc' AND table_name != 'polysdsid' LOOP -- Composer une requête dynamique pour vérifier si la table est vide EXECUTE format('SELECT COUNT(*) FROM polysenc.%I', table_record.table_name) INTO empty_tables;
-- Si le nombre de lignes est égal à zéro, supprimer la table
IF empty_tables = 0 THEN
EXECUTE format('DROP TABLE IF EXISTS polysenc.%I CASCADE', table_record.table_name);
RAISE NOTICE 'Table polysenc.%I supprimée car elle est vide.', table_record.table_name;
END IF;
END LOOP;
-- Parcours de toutes les tables du schéma polysenc
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'polysenc' AND table_name != 'polysdsid' LOOP
-- Vérifie si les champs enc_chart et scale n'existent pas dans la table actuelle
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'polysenc' AND table_name = table_record.table_name AND column_name IN ('enc_chart', 'scale','purpose')
) THEN
-- Crée le champ enc_chart de type texte
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN enc_chart TEXT', table_record.table_name);
-- Crée le champ scale de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN scale NUMERIC', table_record.table_name);
-- Crée le champ purpose de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN purpose NUMERIC', table_record.table_name);
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'polysenc' AND table_name = table_record.table_name AND column_name IN ('posacc','quapos')
) THEN
-- Crée le champ POSACC de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN POSACC NUMERIC(10,0)', table_record.table_name);
-- Crée le champ QUAPOS de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN QUAPOS INTEGER', table_record.table_name);
END IF;
RAISE NOTICE 'Champs enc_chart, scale et purpose créés dans la table %', table_record.table_name;
END IF;
-- Obtient la valeur de enc_chart à partir de la table DSID pour DSID_DSNM
SELECT DSID_DSNM INTO enc_chart_value FROM polysenc.polysdsid LIMIT 1;
-- Obtient la valeur de scale à partir de la table DSID pour DSPM_CSCL
SELECT DSPM_CSCL INTO scale_value FROM polysenc.polysdsid LIMIT 1;
-- Obtient la valeur de purpose à partir de la table DSID pour DSID_INTU
SELECT DSID_INTU INTO purpose_value FROM polysenc.polysdsid LIMIT 1;
-- Met à jour les enregistrements avec les valeurs trouvées dans la table DSID
EXECUTE format('UPDATE polysenc.%I SET enc_chart = $1 WHERE enc_chart IS NULL', table_record.table_name) USING enc_chart_value;
EXECUTE format('UPDATE polysenc.%I SET scale = $1 WHERE scale IS NULL', table_record.table_name) USING scale_value;
EXECUTE format('UPDATE polysenc.%I SET purpose = $1 WHERE purpose IS NULL', table_record.table_name) USING purpose_value;
END LOOP;
-- Efface l'enregistrement de la table DSID
DELETE FROM polysenc.polysdsid;
RETURN NULL;
END; $$ LANGUAGE plpgsql;
createtriggersfields()
CREATE OR REPLACE TRIGGER check_update AFTER INSERT ON linesenc.linesdsid FOR EACH ROW EXECUTE PROCEDURE create_fields_and_update_values_linesenc();
CREATE OR REPLACE TRIGGER check_update AFTER INSERT ON polysenc.polysdsid FOR EACH ROW EXECUTE PROCEDURE create_fields_and_update_values_polysenc();
CREATE OR REPLACE TRIGGER check_update AFTER INSERT ON pointsenc.pointsdsid FOR EACH ROW EXECUTE PROCEDURE create_fields_and_update_values_pointsenc();
Pour exécuter ces lignes de commande il suffit d’ouvrir la fenêtre de shell d’OSGeo4W:
La fenêtre de Shell s’ouvre
Rentrez alors la ligne de commande suivante :
.\Path/ mass_load_s57_postgis.bat repertoire_enc
Repertoire_enc est le répertoire contenant les fichiers .000 des cartes enc. Tous les fichiers .000 de ce répertoire seront chargés dans les schémas d’import.
On aura alors comme résultat une série de tables créées dans chaque schéma d’import. Par contre il y aura des tables complètement vides. En effet, si le type de géométrie est possible pour une classe d’objet, la table sera créée mais s’il n’y a aucune occurrence dans le fichier S57 traité, la table n’aura aucun enregistrement.
La fonction clone_tables_with_prefix charge la base de données ENC avec les tables des trois schémas d’import.
Tout d’abord elle efface de chaque schéma d’import les tables vides, créées par ogr2ogr dans le cas où un type de géométrie prévu n’a pas été utilisé dans le fichier .000.
Deuxièmement, pour chaque schéma d’import elle vérifie si la table du schéma d’import existe déjà dans la base de données ENC. Si elle n’existe pas, elle créée la table avec le préfixe correspondant au type de géométrie (pt_, li_, pl_).
La fonction ajoute les enregistrements de la table d’import dans la table ENC.
Pour les tables de type point, elle met à jour les attributs posacc et quapos de la table à partir des valeurs de la table IsolatedNode.
Pour les tables de type ligne et polygone, elle met à jour les attributs posacc et quapos de la table à partir des valeurs de la table Edge.
Finalement la fonction vide toutes les tables des schémas d’importt, les laissant alors prêts pour un nouveau chargement avec mes commandes ogr2ogr.
Au préalable d’utiliser cette fonction il faut installer la fonction delete_all_records_in_schema :
Copiez la requête dans une fenêtre SQL de pgAdmin et exécutez-la.
-- DROP FUNCTION IF EXISTS public.delete_all_records_in_schema(text);
CREATE OR REPLACE FUNCTION public.delete_all_records_in_schema( schema_name text) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE table_record RECORD; BEGIN -- Récupérer toutes les tables du schéma spécifié FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = schema_name AND table_type = 'BASE TABLE' LOOP -- Construction de la requête DELETE pour chaque table EXECUTE format('DELETE FROM %I.%I', schema_name, table_record.table_name); END LOOP; END; $BODY$;
ALTER FUNCTION public.delete_all_records_in_schema(text) OWNER TO postgres;
La mise à jour de la base ENC se fait en appelant la commande
SELECT clone_tables_with_prefix()
N’oubliez pas de remplacer le nom du schéma (ci après enc2) par le nom de votre schéma enc.
clone_table_with_prefix
CREATE OR REPLACE FUNCTION clone_tables_with_prefix() RETURNS void AS $$ DECLARE table_nom text; BEGIN -- Clonage des tables dans le schéma ENC et mise à jour des tables existantes
-- Boucle sur les tables du schéma pointsENC
FOR table_nom IN (SELECT table_name as table_nom FROM information_schema.tables WHERE table_schema = 'pointsenc' AND table_name NOT IN ('pointsdsid','isolatednode','connectednode'))
LOOP
-- Construction de la requête dynamique pour créer ou mettre à jour la table dans ENC
EXECUTE format('CREATE TABLE IF NOT EXISTS enc2.pt_%I AS SELECT * FROM pointsenc.%I', table_nom, table_nom);
EXECUTE format('INSERT INTO enc2.pt_%I SELECT * FROM pointsenc.%I ON CONFLICT DO NOTHING', table_nom, table_nom);
EXECUTE format('UPDATE enc2.pt_%I SET posacc = isolatednode.posacc, quapos = isolatednode.quapos FROM pointsenc.IsolatedNode isolatednode WHERE enc2.pt_%I.NAME_RCID[1] = isolatednode.RCID AND enc2.pt_%I.enc_chart = isolatednode.enc_chart;', table_nom, table_nom,table_nom);
END LOOP;
-- Boucle sur les tables du schéma LinesENC
FOR table_nom IN (SELECT table_name as table_nom FROM information_schema.tables WHERE table_schema = 'linesenc' AND table_name NOT IN ('linesdsid','edge'))
LOOP
-- Construction de la requête dynamique pour créer ou mettre à jour la table dans ENC
EXECUTE format('CREATE TABLE IF NOT EXISTS enc2.li_%I AS SELECT * FROM linesenc.%I', table_nom, table_nom);
EXECUTE format('INSERT INTO enc2.li_%I SELECT * FROM linesenc.%I ON CONFLICT DO NOTHING', table_nom, table_nom);
EXECUTE format('UPDATE enc2.li_%I SET posacc = edge.posacc, quapos = edge.quapos FROM linesenc.edge edge WHERE enc2.li_%I.NAME_RCID[1] = edge.RCID AND enc2.li_%I.enc_chart = edge.enc_chart;', table_nom, table_nom,table_nom);
END LOOP;
-- Boucle sur les tables du schéma PolysENC
FOR table_nom IN (SELECT table_name as table_nom FROM information_schema.tables WHERE table_schema = 'polysenc' AND table_name NOT IN ( 'polysdsid','m_qual','m_srel'))
LOOP
-- Construction de la requête dynamique pour créer ou mettre à jour la table dans ENC
EXECUTE format('CREATE TABLE IF NOT EXISTS enc2.pl_%I AS SELECT * FROM polysenc.%I', table_nom, table_nom);
EXECUTE format('INSERT INTO enc2.pl_%I SELECT * FROM polysenc.%I ON CONFLICT DO NOTHING', table_nom, table_nom);
EXECUTE format('UPDATE enc2.pl_%I SET posacc = edge.posacc, quapos = edge.quapos FROM linesenc.edge edge WHERE enc2.pl_%I.NAME_RCID[1] = edge.RCID AND enc2.pl_%I.enc_chart = edge.enc_chart;', table_nom, table_nom,table_nom);
END LOOP;
EXECUTE (SELECT delete_all_records_in_schema('pointsenc'));
EXECUTE (SELECT delete_all_records_in_schema('linesenc'));
EXECUTE (SELECT delete_all_records_in_schema('polysenc'));
EXECUTE (SELECT update_sbdare()); END; $$ LANGUAGE plpgsql;
Après la première exécution du script précédent vous devez, si les fichiers chargés avaient des valeurs pour les natures du fond, avoir dans le schéma ENC une table pt_sbdare. C’est la seule table qui nécessite un traitement particulier pour la symbologie par défaut que nous fournissons. En effet le traitement des valeurs des deux attributs nécessaires à créer l’étiquette de la nature du fond (NATSUR et NATQUA) est très complexe de par la nature des attributs (des StringLists) et des innombrables combinaisons possibles.
La solution appliquée ici est la suivante :
Une table natsurf liste les combinaisons de valeurs possibles des tuples natqua et natsur avec l’étiquette correspondante.
Une fonction update_sbdare traite les deux stringlists pour composer les tuples de valeurs et concatène de manière appropriée les étiquettes issues de la table natsurf.
La fonction met à jour les enregistrements de la table pt_sbdare dont la valeur de ‘label’ est nulle.
Au premier chargement des schémas d’import la table pt_sbdare n’existe pas. Une fois créée par un import, on peut définir un trigger qui exécutera la fonction automatiquement par la suite.
Vous devez donc :
Importer la table natsurf dans le schéma ENC
Créer la fonction update_sbdare
Exécuter la fonction manuellement pour la première fois
Chargez la requête suivante dans une fenêtre SQL de pgAdmin et exécutez-la. On considère que vous avez créé un schéma ‘enc’ pour votre base de données. Si ce n’est pas le cas, éditez la table en remplaçant ‘enc.natsurf’ par ‘votre_schéma.natsurf’.
Chargez la requête suivante dans une fenêtrte SQL de pgAdmin et exécutez-la. On considère que vous avez créé un schéma ‘enc’ pour votre base de données. Si ce n’est pas le cas, éditez la table en remplaçant ‘enc.’ par ‘votre_schéma’.
update_sbdare.sql
CREATE OR REPLACE FUNCTION update_sbdare() RETURNS VOID AS $$ DECLARE rec_row RECORD; natsurt VARCHAR[4]; natquat VARCHAR[4]; S VARCHAR[3]; flag INT := 0; etiq VARCHAR(25); etiquet VARCHAR(25); BEGIN -- Vérifier si le champ 'Label' existe déjà IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = 'enc2' AND table_name = 'pt_sbdare' AND column_name = 'label' ) THEN
-- Parcourir les lignes de la table enc.pt_sbdare
FOR rec_row IN SELECT * FROM enc2.pt_sbdare WHERE label IS NULL LOOP
-- Réinitialiser les variables à chaque itération de la boucle
natsurt := ARRAY['','','',''];
S := ARRAY[',',',',','];
flag :=0;
-- Extraire les parties de natsur
FOR i IN 1..4 LOOP
--sortie anticipée
IF i=4 AND flag=1 THEN
EXIT;
END IF;
-- Vérifier si rec_row.natsur[i] est NULL
IF rec_row.natsur[i] IS NULL THEN
IF flag=0 THEN
natsurt[i] := '0';
ELSE
natsurt[i+1] := '0';
END IF;
-- Vérifier si rec_row.natsur[i] contient un '/'
ELSIF strpos(rec_row.natsur[i], '/') = 0 THEN
IF flag=0 THEN
natsurt[i] := rec_row.natsur[i];
IF rec_row.natqua[i] IS NOT NULL THEN
natquat[i] := rec_row.natqua[i];
ELSE
natquat[i] := '0';
END IF;
ELSE
natsurt[i+1] := rec_row.natsur[i];
IF rec_row.natqua[i] IS NOT NULL THEN
natquat[i+1] := rec_row.natqua[i];
ELSE
natquat[i] := '0';
END IF;
END IF;
ELSE
-- Extraire les parties avant et après le '/'
IF i < 5 THEN
natsurt[i] := split_part(rec_row.natsur[i], '/', 1);
natsurt[i+1] := split_part(rec_row.natsur[i], '/', 2);
IF rec_row.natqua[i] IS NOT NULL THEN
natquat[i] := rec_row.natqua[i];
ELSE
natquat[i] := '0';
END IF;
S[i] := '/';
flag :=1; -- nous avons trouvé une valeur avec un '/'
ELSE
natsurt[i] := split_part(rec_row.natsur[i], '/', 1);
END IF;
END IF;
END LOOP;
etiquet :='';
etiq :='';
FOR i IN 1..4 LOOP
IF natsurt[i] <> '0' THEN
-- Exécutez la requête SQL pour récupérer le label en fonction de natsurt[i] et natquat[i]
EXECUTE 'SELECT etiq FROM enc2.natsurf WHERE NATSURT = $1 AND NATQUAT = $2' INTO etiq USING natsurt[i], natquat[i];
IF i =1 THEN
etiquet := etiq ;
ELSE
etiquet := etiquet || S[i-1] || etiq;
END IF;
END IF;
END LOOP;
-- Mettre à jour la ligne avec les valeurs extraites
UPDATE enc2.pt_sbdare
SET
label = etiquet
WHERE pt_sbdare.ogc_fid = rec_row.ogc_fid;
END LOOP;
Dans une fenêtre SQL de pgAdmin rentrez la requête
ALTER TABLE enc.pt_sbdare ADD COLUMN label VARCHAR(25);
SELECT update_sbdare()
Vous aurez comme résultat l’attribut ‘label’ renseigné dans la table pt_sbdare. Par la suite, lors de l’exécution de clone_tables_with_prefix() la mise à jour se fera automatiquement.
Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !
