Code officiel géographique 2018 (Bêta // PostgreSQL)

Il s'agit d'un export depuis le SGBD PostgreSQL (v9.4.5 Win 64b) du code officiel géographique 2018 mis à disposition le 26/03/2018 par l'INSEE (encodage UTF8).
Il est nécessaire de créer le schéma ref_insee_cog dans la base d'arrivée avant de restaurer la donnée.

Généalogie :
Les huit tables du COG 2018 au format "TXT" ont leur extension modifiée pour "CSV" ; le fichier est édité (Notepad++), le séparateur est modifié (tabulation point virgule)
Dans le schéma ref_insee_cog, les structures des tables sont créées en SQL (CREATE TABLE) avec ajout en fin de table d'un identifiant interne de type SERIAL pour chaque table ;
ces tables sont commentées (tables et attributs).
Les données sont chargées dans la base PostgreSQL à partir des fichiers CSV depuis l'interface PgAdmin (CSV, encodage Win1252, point virgule avec en-tête) ;
Après import le type SERIAL est abandonné pour le type INTEGER (DROP SEQUENCE)
Les contraintes sont posées (ALTER TABLE ADD CONSTRAINT) : clé primaire, une ou plusieurs contraintes d'unicité sémantique, une clé étrangère pour certaines tables.
Quelques attributs complémentaires utiles sont créés (ALTER TABLE ADD COLUMN) : aurh_com_ide pour le code commune à 5 caractères fixes, aurh_libgeo pour un libellé formaté "Nom propre" d'un seul tenant, puis ces attributs sont calculés à partir des valeurs natives (UPDATE... CASE WHEN)
Les huit tables pays_2018, region_2018, dep_2018, arr_2018, can_2018, comsimp_2018, france_2018 et historiq_2018 de ce schéma ref_insee_cog sont exportées en ligne de commande (pg_dump/pg_restore).

Mise en forme
Hormis la clé primaire numérique aucune mise en forme n’a été appliquée pour pays_2018, reg_2018 et dep_2018.
Il faut particulièrement veiller à ce que le code du département soit formaté sur au moins 2 caractères (pas de transformation en numérique)
Pour l’arrondissement (q=332) un code géographique (VARCHAR 4) a été ajouté ainsi qu’un libellé (VARCHAR 48). Une clé étrangère a été appliquée sur le département.
Pour le canton (q=2054) un code géographique (VARCHAR 5) et libellé géographique (VARCHAR 48) ont été ajoutés. Une clé étrangère a été appliquée sur le département.
NB : le canton ne porte pas l’arrondissement (sic). La valeur du champ « burcentral » a été transformée en aurh_com_ide pour la commune centre. Cependant il n’y a pas d’intégrité avec aurh_com_ide de comsimp_2018 pour 6 « chefs-lieux » :
• "76";"48 ";"3 ";"1";"48049";"0";"";"CHIRAC";"";"Chirac";993;"Chirac";"4803";"48049"
• "28";"50 ";"12";"1";"50173";"1";"";"EQUEURDREVILLE-HAINNEVILLE";"";"Équeurdreville-Hainneville";1036;"Équeurdreville-Hainneville";"5012";"50173"
• "28";"50 ";"14";"1";"50416";"3";"(LA)";"HAGUE";"(La)";"Hague";1038;"La Hague";"5014";"50416"
• "28";"50 ";"24";"1";"50602";"0";"";"TOURLAVILLE";"";"Tourlaville";1048;"Tourlaville";"5024";"50602"
• "84";"74 ";"3 ";"1";"74011";"1";"";"ANNECY-LE-VIEUX";"";"Annecy-le-Vieux";1569;"Annecy-le-Vieux";"7403";"74011"
• "84";"74 ";"16";"1";"74268";"0";"";"SEYNOD";"";"Seynod";1582;"Seynod";"7416";"74268"
comsimp_2018 (q=35357) : il s’agit de la table centrale du COG, les communes existantes au 1er janvier 2018.
Il y a simplement pose et calcul d’identifiant INSEE unique à 5 caractères fixe et calcul du libellé d’une seule colonne. Le libellé respecte les caractères spéciaux et en particulier le caractère « œ».
Des contraintes d’unicité sont posées sur cette table en plus de la clé primaire numérique obligatoire : unicité du code commune, unicité du couple département/commune.
france_2018 (q=39941) communes ayant existé depuis 1943. Une contrainte d’unicité a été trouvée en combinant dep, arr, ct et com (champs natifs). En revanche la valeur du champ calculé aurh_com_ide n’est pas ici unique (communes surdéfinies pour les cantons urbains).
A noter, 10 lignes ne correspondent pas pour les valeurs du champ « pole »
• /
• "4";"";"";"";"84";"1";"132";"";"";"1";"69274";"0";"";"CREPIEUX-LA-PAPE";"";"Crépieux-la-Pape";"";"";134;"01132";""
• "4";"";"";"";"32";"2";"344";"";"";"1";"51664";"0";"";"GERNICOURT";"";"Gernicourt";"";"";807;"02344";""
• "4";"";"";"";"28";"14";"513";"";"";"1";"50649";"0";"";"PONT-FARCY";"";"Pont-Farcy";"";"";5375;"14513";""
• "4";"";"";"";"52";"44";"60";"";"";"1";"49382";"2";"(LE)";"FRESNE-SUR-LOIRE";"(Le)";"Fresne-sur-Loire";"";"";18391;"44060";""
• "4";"";"";"";"52";"49";"144";"";"";"1";"44225";"0";"";"FREIGNE";"";"Freigné";"";"";19967;"49144";""
• "4";"";"";"";"11";"78";"25";"";"";"1";"95025";"1";"";"ARTHIEUL";"";"Arthieul";"";"";34504;"78025";""
• "4";"";"";"";"11";"78";"65";"";"";"1";"95065";"0";"";"BLAMECOURT";"";"Blamécourt";"";"";34544;"78065";""
• "4";"";"";"";"11";"78";"173";"";"";"1";"91173";"0";"";"CONGERVILLE";"";"Congerville";"";"";34652;"78173";""
• "4";"";"";"";"11";"78";"221";"";"";"1";"91221";"1";"";"ESSONNES";"";"Essonnes";"";"";34700;"78221";""
• "4";"";"";"";"11";"78";"259";"";"";"1";"95259";"0";"";"GADANCOURT";"";"Gadancourt";"";"";34738;"78259";""
• /
On ne peut donc pas établir simplement de contrainte d’intégrité entre France_2018 et comsimp_2018.
3812 codes communes de France_2018 ne correspondent pas avec un code commune de comsimp_2018. On y trouve aussi les arrondissements municipaux (Paris Lyon Marseille).
278 communes ont plusieurs cantons ; 1050 cantons « urbains ».
entre 2017 et 2018 :
• il n’y a pas eu de création de nouveau code géographique.
• 59 codes géographiques ont disparu
• 42 communes ont changé de libellé