| dataset-model |
{"db_type":"pgsql","db_name":"datastore","host":"db","port":"5432","login":"ckan","password":"ckan","sql_query":"WITH src_base AS (\r\n SELECT\r\n CASE\r\n WHEN TRIM(codgeo::text) ~ '^[0-9]+$'\r\n THEN LPAD(TRIM(codgeo::text), 5, '0')\r\n ELSE UPPER(TRIM(codgeo::text))\r\n END AS code_commune,\r\n\r\n CASE\r\n WHEN epci IS NULL THEN NULL\r\n ELSE epci::bigint::text\r\n END AS code_epci_source,\r\n\r\n NULLIF(UPPER(TRIM(departement::text)), '') AS code_departement_source,\r\n NULLIF(UPPER(TRIM(region::text)), '') AS code_region_source,\r\n\r\n p22_pop,\r\n p16_pop,\r\n superf,\r\n nais1621,\r\n dece1621,\r\n p22_men,\r\n naisd24,\r\n decesd24,\r\n p22_pop1564\r\n\r\n FROM \"a69528ba-9dac-43fd-b974-c9c62aec2c41\"\r\n WHERE codgeo IS NOT NULL\r\n AND TRIM(codgeo::text) <> ''\r\n\r\n -- Exclusion des arrondissements municipaux :\r\n -- les communes m\u00e8res 75056, 13055 et 69123 existent d\u00e9j\u00e0 dans la source.\r\n AND NOT (\r\n codgeo BETWEEN '75101' AND '75120'\r\n OR codgeo BETWEEN '13201' AND '13216'\r\n OR codgeo BETWEEN '69381' AND '69389'\r\n )\r\n\r\n -- Exclusion des lignes r\u00e9siduelles totalement vides.\r\n AND NOT (\r\n p22_pop IS NULL\r\n AND p16_pop IS NULL\r\n AND superf IS NULL\r\n AND nais1621 IS NULL\r\n AND dece1621 IS NULL\r\n AND p22_men IS NULL\r\n AND naisd24 IS NULL\r\n AND decesd24 IS NULL\r\n AND departement IS NULL\r\n AND region IS NULL\r\n AND epci IS NULL\r\n )\r\n),\r\n\r\ncommunes_ref AS (\r\n SELECT\r\n code_officiel,\r\n nom_officiel,\r\n parent_code,\r\n parent_level\r\n FROM administrative_divisions\r\n WHERE level = 'commune'\r\n),\r\n\r\nepci_ref AS (\r\n SELECT\r\n code_officiel,\r\n nom_officiel,\r\n parent_code,\r\n parent_level\r\n FROM administrative_divisions\r\n WHERE level = 'epci'\r\n),\r\n\r\ndepartements_ref AS (\r\n SELECT\r\n code_officiel,\r\n nom_officiel,\r\n parent_code,\r\n parent_level\r\n FROM administrative_divisions\r\n WHERE level = 'departement'\r\n),\r\n\r\nregions_ref AS (\r\n SELECT\r\n code_officiel,\r\n nom_officiel\r\n FROM administrative_divisions\r\n WHERE level = 'region'\r\n),\r\n\r\nhierarchie AS (\r\n SELECT\r\n 'commune'::text AS niveau_geo,\r\n\r\n s.code_commune,\r\n c.nom_officiel AS libelle_commune,\r\n\r\n CASE\r\n WHEN s.code_epci_source IS NULL\r\n THEN 'HORS_EPCI_' || s.code_commune\r\n ELSE s.code_epci_source\r\n END AS code_epci,\r\n\r\n CASE\r\n WHEN s.code_epci_source IS NULL\r\n THEN 'Hors EPCI - ' || c.nom_officiel\r\n ELSE COALESCE(e.nom_officiel, 'EPCI non r\u00e9f\u00e9renc\u00e9 - ' || s.code_epci_source)\r\n END AS libelle_epci,\r\n\r\n COALESCE(\r\n s.code_departement_source,\r\n CASE\r\n WHEN c.parent_level = 'departement' THEN c.parent_code\r\n WHEN e.parent_level = 'departement' THEN e.parent_code\r\n WHEN s.code_commune LIKE '97%' OR s.code_commune LIKE '98%' THEN LEFT(s.code_commune, 3)\r\n WHEN s.code_commune LIKE '2A%' OR s.code_commune LIKE '2B%' THEN LEFT(s.code_commune, 2)\r\n ELSE LEFT(s.code_commune, 2)\r\n END\r\n ) AS code_departement,\r\n\r\n COALESCE(\r\n s.code_region_source,\r\n CASE\r\n WHEN d.parent_level = 'region' THEN d.parent_code\r\n ELSE NULL\r\n END\r\n ) AS code_region,\r\n\r\n CASE\r\n WHEN s.code_epci_source IS NULL THEN true\r\n ELSE false\r\n END AS epci_pseudo,\r\n\r\n CASE\r\n WHEN s.code_epci_source IS NULL\r\n THEN 'Commune sans EPCI dans la source ; pseudo-EPCI technique g\u00e9n\u00e9r\u00e9'\r\n ELSE 'Rattachement EPCI r\u00e9el depuis la source, libell\u00e9 enrichi par administrative_divisions'\r\n END AS statut_rattachement_epci,\r\n\r\n s.p22_pop,\r\n s.p16_pop,\r\n s.superf,\r\n s.nais1621,\r\n s.dece1621,\r\n s.p22_men,\r\n s.naisd24,\r\n s.decesd24,\r\n s.p22_pop1564\r\n\r\n FROM src_base s\r\n INNER JOIN communes_ref c\r\n ON c.code_officiel = s.code_commune\r\n\r\n LEFT JOIN epci_ref e\r\n ON e.code_officiel = s.code_epci_source\r\n\r\n LEFT JOIN departements_ref d\r\n ON d.code_officiel = COALESCE(\r\n s.code_departement_source,\r\n CASE\r\n WHEN c.parent_level = 'departement' THEN c.parent_code\r\n WHEN e.parent_level = 'departement' THEN e.parent_code\r\n WHEN s.code_commune LIKE '97%' OR s.code_commune LIKE '98%' THEN LEFT(s.code_commune, 3)\r\n WHEN s.code_commune LIKE '2A%' OR s.code_commune LIKE '2B%' THEN LEFT(s.code_commune, 2)\r\n ELSE LEFT(s.code_commune, 2)\r\n END\r\n )\r\n),\r\n\r\nhierarchie_enrichie AS (\r\n SELECT\r\n h.*,\r\n d.nom_officiel AS libelle_departement,\r\n r.nom_officiel AS libelle_region\r\n FROM hierarchie h\r\n LEFT JOIN departements_ref d\r\n ON d.code_officiel = h.code_departement\r\n LEFT JOIN regions_ref r\r\n ON r.code_officiel = h.code_region\r\n)\r\n\r\nSELECT\r\n h.niveau_geo,\r\n h.code_commune,\r\n h.libelle_commune,\r\n h.code_epci,\r\n h.libelle_epci,\r\n h.code_departement,\r\n h.libelle_departement,\r\n h.code_region,\r\n h.libelle_region,\r\n h.epci_pseudo,\r\n h.statut_rattachement_epci,\r\n\r\n -- Mill\u00e9simes respectant la source\r\n 2022::integer AS pop_annee,\r\n h.p22_pop AS population_totale,\r\n\r\n 2016::integer AS pop_reference_annee,\r\n h.p16_pop AS population_reference,\r\n\r\n h.p22_pop - h.p16_pop AS variation_population_2016_2022,\r\n\r\n (h.p22_pop - h.p16_pop) \/ NULLIF(h.p16_pop, 0) AS taux_evolution_population_2016_2022,\r\n h.p22_pop - h.p16_pop AS taux_evolution_population_2016_2022_num,\r\n h.p16_pop AS taux_evolution_population_2016_2022_den,\r\n\r\n h.p22_pop1564 AS population_15_64_ans,\r\n h.p22_pop1564 \/ NULLIF(h.p22_pop, 0) AS part_population_15_64_ans,\r\n h.p22_pop1564 AS part_population_15_64_ans_num,\r\n h.p22_pop AS part_population_15_64_ans_den,\r\n\r\n h.superf AS superficie_km2,\r\n h.p22_pop \/ NULLIF(h.superf, 0) AS densite_population,\r\n h.p22_pop AS densite_population_num,\r\n h.superf AS densite_population_den,\r\n\r\n 2022::integer AS menages_annee,\r\n h.p22_men AS nb_menages,\r\n\r\n h.p22_pop \/ NULLIF(h.p22_men, 0) AS taille_moyenne_menage,\r\n h.p22_pop AS taille_moyenne_menage_num,\r\n h.p22_men AS taille_moyenne_menage_den,\r\n\r\n '2016-2021'::text AS etat_civil_periode,\r\n 2016::integer AS etat_civil_periode_debut,\r\n 2021::integer AS etat_civil_periode_fin,\r\n h.nais1621 AS naissances_2016_2021,\r\n h.dece1621 AS deces_2016_2021,\r\n h.nais1621 - h.dece1621 AS solde_naturel_2016_2021,\r\n\r\n 2024::integer AS etat_civil_annee,\r\n h.naisd24 AS naissances_domiciliees,\r\n h.decesd24 AS deces_domicilies,\r\n h.naisd24 - h.decesd24 AS solde_naturel,\r\n\r\n 'base_du_comparateur_de_territoires'::text AS sources_datasets,\r\n 'a69528ba-9dac-43fd-b974-c9c62aec2c41'::text AS sources_resource_ids\r\n\r\nFROM hierarchie_enrichie h\r\nORDER BY h.code_commune;"} |
| tooltip |
{"resources":[{"type":"standard","resource_id":"f6d01267-04c9-47d0-9ff9-d8ba265679ab","value":{"title":"","fields":"_id,niveau_geo,code_commune,libelle_commune,code_epci,libelle_epci,code_departement,libelle_departement,code_region,libelle_region,epci_pseudo,statut_rattachement_epci,pop_annee,population_totale,pop_reference_annee,population_reference,variation_population_2016_2022,taux_evolution_population_2016_2022,taux_evolution_population_2016_2022_num,taux_evolution_population_2016_2022_den,population_15_64_ans,part_population_15_64_ans,part_population_15_64_ans_num,part_population_15_64_ans_den,superficie_km2,densite_population,densite_population_num,densite_population_den,menages_annee,nb_menages,taille_moyenne_menage,taille_moyenne_menage_num,taille_moyenne_menage_den,etat_civil_periode,etat_civil_periode_debut,etat_civil_periode_fin,naissances_2016_2021,deces_2016_2021,solde_naturel_2016_2021,etat_civil_annee,naissances_domiciliees,deces_domicilies,solde_naturel,sources_datasets,sources_resource_ids"}}]} |