Création des tables agrégées

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éée - rôle de connexion

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

# Récupérer la liste annee_dept et le schéma de travail
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

Agrégation des tables à partir des variables

# 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)
    agregate_table_name <- paste0(work_schema, 
                                  ".r_consommation_enaf_ocsge_union_", 
                                  annee, "_s_", dept)
    
    # Suppression préalable de la table agrégée si elle existe
    drop_table_query <- sprintf("DROP TABLE IF EXISTS %s;", agregate_table_name)
    
    # Requête de création de la table
    create_table_query <- sprintf(
      "CREATE TABLE %s (
      id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      consommation_enaf boolean,
      the_geom geometry);", agregate_table_name)
    
    # Requête d'alimentation de la table
    insert_query <- sprintf(
    "INSERT INTO %s (consommation_enaf, the_geom)
    SELECT consommation_enaf, ST_UnaryUnion(unnest(ST_ClusterIntersecting(the_geom)))
    FROM %s GROUP BY consommation_enaf;", agregate_table_name, table_name)
    
    # Requêtes d'ajout des contraintes géomatiques
    geom_constraints <- c(
      sprintf("ALTER TABLE IF EXISTS %s
              ADD CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2);", 
              agregate_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);", 
              agregate_table_name),
      sprintf("ALTER TABLE IF EXISTS %s
              ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 2154);", 
              agregate_table_name)
    )
    
    # Requête de création d'un index spatial
    geom_index <- sprintf("CREATE INDEX IF NOT EXISTS r_consommation_enaf_ocsge_union_%s__s_%s_geom_idx
                          ON agregate_table_name USING gist
                          (the_geom)
                          TABLESPACE pg_default;", 
                          annee, dept, agregate_table_name)

    # Requêtes d'attribution des droits
    grant_select_query <- sprintf("GRANT SELECT ON %s TO reader_referentiels;", 
                                  agregate_table_name)
    grant_all_query <- sprintf("GRANT ALL ON %s TO writer_referentiels;", 
                                  agregate_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 (version agrégée)';",
                                agregate_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';", agregate_table_name),
      sprintf("COMMENT ON COLUMN %s.consommation_enaf IS 'Consommation d''ENAF (TRUE/FALSE)';", agregate_table_name),
      sprintf("COMMENT ON COLUMN %s.the_geom IS 'Géométrie de l''objet surfacique';", agregate_table_name)
    )

    # 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)
    DBI::dbExecute(connexion, insert_query)
    # Exécution de l'ajout des contraintes et de l'index spatial
    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 créée et paramétrée : %s", agregate_table_name))
  }
}