Description

This is an export from the PostgreSQL DBMS (v9.4.5 Win 64b) of the 2018 Official Geographic Code made available on 26/03/2018 by INSEE (UTF8 encoding). It is necessary to create the schema ref_insee_cog in the base of arrival before restoring the data. Genealogy: The eight tables of the COG 2018 in "TXT" format have their extension changed to "CSV"; the file is edited (Notepad++), the separator is modified (point-to-point tabulation) In the ref_insee_cog schema, the table structures are created in SQL (CREATE TABLE) with the addition at the end of the table of an internal identifier of type SERIAL for each table; these tables are commented (tables and attributes). The data is loaded into the PostgreSQL database from the CSV files from the PgAdmin interface (CSV, Win1252 encoding, semicolon with header); After import the type SERIAL is dropped for the type INTEGER (DROP SEQUENCE) The constraints are set (ALTER TABLE ADD CONSTRAINT): primary key, one or more constraints of semantic uniqueness, a foreign key for some tables. Some useful additional attributes have been created (ALTER TABLE ADD COLUMN): aurh_com_ide for the common code with 5 fixed characters, aurh_libgeo for a label formatted "Name proper" in one piece, then these attributes are calculated from native values (UPDATE... CASE WHEN) The eight country_2018, region_2018, dep_2018, arr_2018, can_2018, comsimp_2018, france_2018 and historiq_2018 tables of this schema ref_insee_cog are exported at the command line (pg_dump/pg_restore). Formatting Apart from the digital primary key no formatting was applied for country_2018, reg_2018 and dep_2018. Particular care must be taken to ensure that the department code is formatted on at least 2 characters (no digital transformation) For the district (q=332) a geographical code (VARCHAR 4) has been added as well as a wording (VARCHAR 48). A foreign key was applied to the department. For the canton (q=2054) a geographical code (VARCHAR 5) and geographical description (VARCHAR 48) have been added. A foreign key was applied to the department. NB: the canton does not have the district (sic). The value of the ‘burcentral’ field has been transformed into aurh_com_ide for the central municipality. However, there is no integrity with aurh_com_ide de comsimp_2018 for 6 ‘headquarters’: "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): this is the central table of the COG, the municipalities existing on 1 January 2018. There is simply the installation and calculation of a fixed 5-character unique INSEE identifier and the calculation of the wording of a single column. The wording shall respect the special characters and in particular the character ‘œ’. Uniqueness constraints are placed on this table in addition to the mandatory digital primary key: uniqueness of the common code, uniqueness of the department/commune couple. france_2018 (q=39941) municipalities that have existed since 1943. A uniqueness constraint was found by combining dep, arr, ct and com (native fields). On the other hand, the value of the calculated field aurh_com_ide is not unique here (overdefined municipalities for urban cantons). Note that 10 lines do not match the values of the "pole" field /* "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";"" */ We cannot therefore simply establish an integrity constraint between France_2018 and comsimp_2018. 3812 common codes of France_2018 do not correspond with a common code of comsimp_2018. There are also the municipal districts (Paris Lyon Marseille). 278 municipalities have several cantons; 1050 "urban" cantons. between 2017 and 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é

Resources

Name	Format	Description	Link

Official Geographic Code 2018 (Beta // PostgreSQL)

Description

Resources

Tags

Topics

Categories