| dataset-model |
{"db_type":"pgsql","db_name":"datastore","host":"db","port":"5432","login":"ckan","password":"ckan","sql_query":"WITH ips_consolide AS (\r\n SELECT\r\n 'ecole' AS type_etablissement,\r\n rentree_scolaire,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n uai,\r\n nom_de_l_etablissement AS nom_etablissement,\r\n secteur,\r\n CASE\r\n WHEN REPLACE(ips, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(ips, ',', '.')::numeric\r\n ELSE NULL\r\n END AS ips\r\n FROM \"f53e280a-bf05-4660-92f8-7cc071b740c4\"\r\n\r\n UNION ALL\r\n\r\n SELECT\r\n 'college' AS type_etablissement,\r\n rentree_scolaire,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n uai,\r\n nom_de_l_etablissment AS nom_etablissement,\r\n secteur,\r\n ips\r\n FROM \"d33a2cc8-58c0-4a2d-b5a2-3f54ebbbb1db\"\r\n\r\n UNION ALL\r\n\r\n SELECT\r\n 'lycee' AS type_etablissement,\r\n rentree_scolaire,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n uai,\r\n nom_de_l_etablissment AS nom_etablissement,\r\n secteur,\r\n \"ips_ensemble_gt-pro\" AS ips\r\n FROM \"824d0e74-fb7b-4975-a2e3-eff9be88d32b\"\r\n)\r\n\r\nSELECT\r\n ep.uai,\r\n ep.nom_etablissement,\r\n ep.type_etablissement,\r\n ep.statut_public_prive,\r\n ep.ep_2022_2023 AS statut_education_prioritaire,\r\n ep.qp_a_proximite_o_n,\r\n ep.qp_a_proximite,\r\n ep.nom_du_qp,\r\n ep.nombre_d_eleves,\r\n ep.code_commune,\r\n ep.nom_commune AS commune,\r\n ep.code_departement,\r\n ep.libelle_departement AS departement,\r\n ep.code_region,\r\n ep.libelle_region AS region,\r\n ep.latitude,\r\n ep.longitude,\r\n\r\n CASE\r\n WHEN ep.latitude IS NOT NULL AND ep.longitude IS NOT NULL\r\n THEN ep.latitude::text || ',' || ep.longitude::text\r\n ELSE NULL\r\n END AS geo_point_2d,\r\n c.epci,\r\n ips.rentree_scolaire,\r\n ips.ips\r\nFROM \"816a0970-e5ae-4b9c-8c9e-f6c637bfb691\" ep\r\nJOIN \"a69528ba-9dac-43fd-b974-c9c62aec2c41\" c\r\n ON TRIM(ep.code_commune) = TRIM(c.codgeo)\r\nLEFT JOIN ips_consolide ips\r\n ON ep.uai = ips.uai\r\nORDER BY ep.nom_commune, ep.nom_etablissement;"} |
| spatial |
{"type":"Polygon","coordinates":[[[-61.798305091244,-21.386912898623],[55.825493124272,-21.386912898623],[55.825493124272,51.033901387675],[-61.798305091244,51.033901387675],[-61.798305091244,-21.386912898623]]]} |