Téléchargement des données

Les sources sont des données libres d’accès produites et diffusées par l’IGN :

données millésimées de l’Occupation du Sol à Grande Échelle (OCS GE) : https://geoservices.ign.fr/ocsge
données d’artificialisation issues de l’OCS GE : https://geoservices.ign.fr/artificialisation-ocs-ge

Livrables

Les tables départementales millésimées créées à l’aide de la matrice sont déposées dans la base referentiels, schéma zz_production.

Ces tables doivent ensuite être déplacées dans la base production, schéma scte_conso_ocs_ge.

Chargement des librairies

library(datalibaba)
library(DBI)
library(yaml)

Configuration

On récupère les paramètres suivants via le fichier config.yml :

millésimes par département des livrables OCS GE
base de données de travail
schéma de stockage de l’OCS GE
schéma de travail
préfixe du nom de la table à créer
rôle de connexion

# Charger le fichier YAML
config <- yaml::read_yaml("../config.yml")

# Récupérer la liste annee_dept et les paramètres de connexion à la base
annee_dept <- config$annee_dept
database <- config$database
ocs_ge_schema <- config$ocs_ge_schema
work_schema <- config$work_schema
prefix_table <- config$prefix_table
role <- config$role

Création des tables à partir des variables

Les tables sont créées par millésime et département dans la base referentiels, schéma zz_production.
Les champs suivants sont ajoutés à la création :
- consommation_enaf
- artificialisation
- type_espace
- surf

Pour les départements 44, 49, 53 et 85

NB : Paramétrer les départements et les millésimes concernés dans config.yml au préalable.

Ces tables sont créées à partir des livrables OCS GE de l’IGN :

# Boucle pour créer les tables pour chaque combinaison année/département
for (annee in names(annee_dept)) {
  depts <- annee_dept[[annee]]
  for (dept in depts) {
    # Nom de la table dynamique
    table_name <- paste0(work_schema, ".", prefix_table, annee, "_s_", dept)
    
    # Requête de suppression préalable de la table si elle existe
    drop_table_query <- sprintf("DROP TABLE IF EXISTS %s;", table_name)
    
    # Requête de création de la table avec intégration des nouveaux champs
    create_table_query <- sprintf(
      "CREATE TABLE IF NOT EXISTS %s AS
        SELECT id, code_cs, code_us, millesime,
          NULL::boolean AS consommation_enaf,
          NULL::text AS artificialisation,
          NULL::text AS type_espace,
          NULL::numeric AS surface,
          the_geom
        FROM %s.n_ocsge_occupation_sol_%s_s_d%02d;", 
      table_name, ocs_ge_schema, annee, dept)

    # Connexion à la base de données
    connexion <- datalibaba::connect_to_db(db = database, user = role)
    
    # Exécution de la création et de la modification des tables
    DBI::dbExecute(connexion, drop_table_query)
    DBI::dbExecute(connexion, create_table_query)

    # Déconnexion de la base de données
    DBI::dbDisconnect(connexion)
    
    # Message d'information
    message(sprintf("Table créée : %s", table_name))
  }
}

Pour le département 72

NB : Paramétrer le département et les millésimes concernés dans config.yml au préalable.

Ces tables sont créées à partir des données d’artificialisation issue de l’OCS GE de l’IGN (en raison d’une différence d’identifiants avec les livrables OCS GE pour le 72) :

# Boucle pour créer les tables pour chaque combinaison année/département
for (annee in names(annee_dept)) {
  depts <- annee_dept[[annee]]
  for (dept in depts) {
    # Nom de la table dynamique
    table_name <- paste0(work_schema, ".", prefix_table, annee, "_s_", dept)
    
    # Requête de suppression préalable de la table si elle existe
    drop_table_query <- sprintf("DROP TABLE IF EXISTS %s;", table_name)
    
    # Requête de création de la table avec intégration des nouveaux champs
    create_table_query <- sprintf(
      "CREATE TABLE IF NOT EXISTS %s AS
        SELECT id, code_cs, code_us, millesime,
          NULL::boolean AS consommation_enaf,
          NULL::text AS artificialisation,
          NULL::text AS type_espace,
          NULL::numeric AS surface,
          the_geom
        FROM %s.n_artif_%s_s_d%02d;", 
      table_name, ocs_ge_schema, annee, dept)

    # Connexion à la base de données
    connexion <- datalibaba::connect_to_db(db = database, user = role)
    
    # Exécution de la création et de la modification des tables
    DBI::dbExecute(connexion, drop_table_query)
    DBI::dbExecute(connexion, create_table_query)

    # Déconnexion de la base de données
    DBI::dbDisconnect(connexion)
    
    # Message d'information
    message(sprintf("Table créée : %s", table_name))
  }
}

Paramétrage des tables

Étapes implémentées :

Une clé primaire est ajoutée sur le champs id pour chaque table ainsi que les contraintes géomatique usuelles.
Les droits sont attribués sur les tables aux rôles de groupe reader_referentiels et writer_referentiels.
Les commentaires de la table et des champs sont également ajoutés.

# Boucle pour créer les tables pour chaque combinaison année/département
for (annee in names(annee_dept)) {
  depts <- annee_dept[[annee]]
  for (dept in depts) {
    # Nom de la table dynamique
    table_name <- paste0(work_schema, ".", prefix_table, annee, "_s_", dept)
    
    # Requêtes d'ajout des contraintes
    # Clé primaire"
    primary_key_query <- sprintf("ALTER TABLE IF EXISTS %s 
                                 ADD CONSTRAINT pk_%s%s_d_%s PRIMARY KEY (id);",
                                 table_name, prefix_table, annee, dept)
    # Requêtes de création des contraintes géomatiques
    geom_constraints <- c(
      sprintf("ALTER TABLE IF EXISTS %s
              ADD CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2);", 
              table_name),
      sprintf("ALTER TABLE IF EXISTS %s
              ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text 
              OR geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL);", 
              table_name),
      sprintf("ALTER TABLE IF EXISTS %s
              ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 2154);", 
              table_name)
    )
    
    # Requête de création d'un index spatial
    geom_index <- sprintf("CREATE INDEX IF NOT EXISTS %s%s_s_%s_geom_idx
                          ON %s USING gist
                          (the_geom)
                          TABLESPACE pg_default;", 
                          prefix_table, annee, dept, table_name)

    # Requêtes d'attribution des droits
    grant_select_query <- sprintf("GRANT SELECT ON %s TO reader_referentiels;", 
                                  table_name)
    grant_all_query <- sprintf("GRANT ALL ON %s TO writer_referentiels;", 
                                  table_name)
    # Requêtes des commentaires sur les tables
    # Récupération du commentaire du livrable OCS GE d'origine
    ocs_ge_table_comment <- datalibaba::get_table_comment(
      table = sprintf("n_ocsge_occupation_sol_%s_s_d%02d", annee, dept),
      schema = ocs_ge_schema,
      db = database,
      user = role)
    # Création du commentaire pour le livrable Consommation d'ENAF
    comment_on_table <- sprintf("COMMENT ON TABLE %s IS 'Consommation d''ENAF à partir de l''%s';",
                                table_name, ocs_ge_table_comment$commentaire)
    
    # Requêtes des commentaires sur les noms des champs
    comments_sql <- c(
      sprintf("COMMENT ON COLUMN %s.id IS 'Identifiant de l''objet surfacique';", table_name),
      sprintf("COMMENT ON COLUMN %s.code_cs IS 'Code de couverture du sol';", table_name),
      sprintf("COMMENT ON COLUMN %s.code_us IS 'Code d’usage du sol';", table_name),
      sprintf("COMMENT ON COLUMN %s.millesime IS 'Année de référence de l’objet surfacique';", table_name),
      sprintf("COMMENT ON COLUMN %s.consommation_enaf IS 'Consommation d''ENAF (TRUE/FALSE)';", table_name),
      sprintf("COMMENT ON COLUMN %s.artificialisation IS 'artif ou non artif (sources : IGN, Cerema)';", table_name),      
      sprintf("COMMENT ON COLUMN %s.type_espace IS 'Type d''espace (artificialisé ou non)';", table_name),
      sprintf("COMMENT ON COLUMN %s.surface IS 'Surface de l''objet surfacique (en m²)';", table_name),
      sprintf("COMMENT ON COLUMN %s.the_geom IS 'Géométrie de l''objet surfacique';", table_name)
    )

    # Connexion à la base de données
    connexion <- datalibaba::connect_to_db(db = database, user = role)

    # Exécution de l'ajout des contraintes et de l'index spatial
    DBI::dbExecute(connexion, primary_key_query)
        for (sql in c(geom_constraints)) {
      DBI::dbExecute(connexion, sql)
        }
    DBI::dbExecute(connexion, geom_index)
    # Exécution de l'attribution des droits et commentaires
    DBI::dbExecute(connexion, grant_select_query)
    DBI::dbExecute(connexion, grant_all_query)
    DBI::dbExecute(connexion,comment_on_table)
    for (sql in c(comments_sql)) {
      DBI::dbExecute(connexion, sql)
    }
    # Déconnexion de la base de données
    DBI::dbDisconnect(connexion)
    
    # Message d'information
    message(sprintf("Table paramétrée : %s", table_name))
  }
}

Création des tables par millésime et département

Christine Gallais-Jouadet

21/11/2025