DB Connected with name only
INFO : Connected to database infocirco on the host None:None
SELECT ST_AsGeojson(wkb_geometry)::json FROM zone_circo WHERE ref= '030-06'
SQL Execution time: 0.0019483566284179688
select st_area(wkb_geometry::geography)/1000000 from zone_circo where ref = '030-06'
SQL Execution time: 0.004388093948364258
select sum(st_area(wkb_geometry::geography)::float)/1000000 from ign_region where insee_reg != '03';
SQL Execution time: 6.943579196929932

        SELECT 
        nomrs, numvoie, typvoie, libvoie, libcom, v.lib_mod AS lib_typequ,
        cantine, internat, ep as education_prioritaire, acces_aire_pratique,
        acces_libre, acces_sanitaire, acces_vestiaire, capacite_d_accueil, pres_douche, pres_sanitaire,
        longitude, latitude
        FROM 
        public.insee_bpe AS b
        LEFT JOIN 
        public.insee_bpe_vars AS v 
        ON v.cod_var = 'TYPEQU'  -- Indique la colonne à expliquer
        AND v.cod_mod = b.typequ  -- Lie la valeur de la colonne à sa définition
        WHERE 
        b.depcom  in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
                        
SQL Execution time: 0.45333123207092285
SELECT sum(p18_pop::float) from insee_pop_2018
SQL Execution time: 0.06098055839538574
SELECT sum(p21_pop::float) from insee_pop_2021
SQL Execution time: 0.07395267486572266

            SELECT 2015 as annee,
            sum(ind*pct) AS ind,
                sum(ind_0_3 * pct) + sum(ind_4_5 * pct) as ind_0_5,
                sum(ind_6_10 * pct) as ind_6_10,
                sum(ind_11_17 * pct) as ind_11_17,
                sum(ind_18_24 * pct) as ind_18_24,
                sum(ind_25_39 * pct) as ind_25_39,
                sum(ind_40_54 * pct) as ind_40_54,
                sum(ind_55_64 * pct) as ind_55_64,
                sum(ind_65_79 * pct) as ind_65_79,
                sum(ind_80p * pct) as ind_80p
            FROM circo_carreaux200m_2015
            NATURAL JOIN insee_population_carroyee_2015
            WHERE ref = '030-06'
            
SQL Execution time: 0.05813789367675781

        SELECT 2021 as annee,
            sum(p21_pop::float * cic.pct_iris_dans_circo) as ind,
            sum((p21_pop0002::float + p21_pop0305::float) * cic.pct_iris_dans_circo) as ind_0_5,
            sum(p21_pop0610::float * cic.pct_iris_dans_circo) as ind_6_10,
            sum(p21_pop1117::float * cic.pct_iris_dans_circo) as ind_11_17,
            sum(p21_pop1824::float * cic.pct_iris_dans_circo) as ind_18_24,
            sum(p21_pop2539::float * cic.pct_iris_dans_circo) as ind_25_39,
            sum(p21_pop4054::float * cic.pct_iris_dans_circo) as ind_40_54,
            sum(p21_pop5564::float * cic.pct_iris_dans_circo) as ind_55_64,
            sum(p21_pop6579::float * cic.pct_iris_dans_circo) as ind_65_79,
            sum(p21_pop80p::float * cic.pct_iris_dans_circo) as ind_80p
        FROM insee_pop_2021 p
        JOIN circo_iris_correspondance cic ON p.iris = cic.code_iris
        WHERE cic.ref_circo = '030-06'
        
SQL Execution time: 0.004312038421630859

        SELECT 2010 as annee,
            sum(p10_pop::float * cic.pct_iris_dans_circo) as ind,
            sum((p10_pop0002::float + p10_pop0305::float) * cic.pct_iris_dans_circo) as ind_0_5,
            sum(p10_pop0610::float * cic.pct_iris_dans_circo) as ind_6_10,
            sum(p10_pop1117::float * cic.pct_iris_dans_circo) as ind_11_17,
            sum(p10_pop1824::float * cic.pct_iris_dans_circo) as ind_18_24,
            sum(p10_pop2539::float * cic.pct_iris_dans_circo) as ind_25_39,
            sum(p10_pop4054::float * cic.pct_iris_dans_circo) as ind_40_54,
            sum(p10_pop5564::float * cic.pct_iris_dans_circo) as ind_55_64,
            sum(p10_pop6579::float * cic.pct_iris_dans_circo) as ind_65_79,
            sum(p10_pop80p::float * cic.pct_iris_dans_circo) as ind_80p
        FROM insee_pop_2010 p
        JOIN circo_iris_correspondance cic ON p.iris = cic.code_iris
        WHERE cic.ref_circo = '030-06'
        
SQL Execution time: 0.008137702941894531

            SELECT "p13_pop"::numeric
            FROM insee_circo_2013
            WHERE "depcir" = '3006'
            
SQL Execution time: 0.0007922649383544922

            SELECT population::numeric
            FROM insee_circo_2019
            WHERE dep = '30' and circo = '6'
            
SQL Execution time: 0.0007379055023193359

        SELECT sum(p21_pop::float * cic.pct_iris_dans_circo) as population
        FROM insee_pop_2021 p
        JOIN circo_iris_correspondance cic ON p.iris = cic.code_iris
        WHERE cic.ref_circo = '030-06'
        
SQL Execution time: 0.005281686782836914

            SELECT sum(ind),
                sum(ind_0_3) + sum(ind_4_5),
                sum(ind_6_10),
                sum(ind_11_17),
                sum(ind_18_24),
                sum(ind_25_39),
                sum(ind_40_54),
                sum(ind_55_64),
                sum(ind_65_79),
                sum(ind_80p)
            FROM insee_population_carroyee_2015
            WHERE depcom LIKE '30%'
            
SQL Execution time: 0.1573336124420166

        SELECT
            sum(p21_pop::float) as ind,
            sum(p21_pop0002::float) + sum(p21_pop0305::float) as ind_0_5,
            sum(p21_pop0610::float) as ind_6_10,
            sum(p21_pop1117::float) as ind_11_17,
            sum(p21_pop1824::float) as ind_18_24,
            sum(p21_pop2539::float) as ind_25_39,
            sum(p21_pop4054::float) as ind_40_54,
            sum(p21_pop5564::float) as ind_55_64,
            sum(p21_pop6579::float) as ind_65_79,
            sum(p21_pop80p::float) as ind_80p
        FROM insee_pop_2021
        WHERE com LIKE '30%'
        
SQL Execution time: 0.044600725173950195

            SELECT sum(ind),
                sum(ind_0_3) + sum(ind_4_5),
                sum(ind_6_10),
                sum(ind_11_17),
                sum(ind_18_24),
                sum(ind_25_39),
                sum(ind_40_54),
                sum(ind_55_64),
                sum(ind_65_79),
                sum(ind_80p)
            FROM insee_population_carroyee_2015
            
SQL Execution time: 4.74601411819458

        SELECT
            sum(p21_pop::float) as ind,
            sum(p21_pop0002::float) + sum(p21_pop0305::float) as ind_0_5,
            sum(p21_pop0610::float) as ind_6_10,
            sum(p21_pop1117::float) as ind_11_17,
            sum(p21_pop1824::float) as ind_18_24,
            sum(p21_pop2539::float) as ind_25_39,
            sum(p21_pop4054::float) as ind_40_54,
            sum(p21_pop5564::float) as ind_55_64,
            sum(p21_pop6579::float) as ind_65_79,
            sum(p21_pop80p::float) as ind_80p
        FROM insee_pop_2021
        
SQL Execution time: 0.17090058326721191

            SELECT 2010 as annee,
                sum(ind * pct),
                sum(ind_0_3 * pct) + sum(ind_4_5 * pct),
                sum(ind_6_10 * pct),
                sum(ind_11_14 * pct) + sum(ind_15_17 * pct),
                sum(ind_18_24 * pct),
                sum(ind_25p * pct) - sum(ind_65p * pct),
                sum(ind_65p * pct)
            FROM circo_carreaux200m_2010
            NATURAL JOIN insee_population_carroyee_2010
            WHERE ref='030-06'
            
SQL Execution time: 1.606513261795044

        SELECT 
            sum(c18_pop15p::float * cic.pct_iris_dans_circo) as pop15p,
            sum(c18_f15p::float * cic.pct_iris_dans_circo) as pop15f,
            sum(c18_pop15p_cs1::float * cic.pct_iris_dans_circo) as pop15p_cs1,
            sum(c18_pop15p_cs2::float * cic.pct_iris_dans_circo) as pop15p_cs2,
            sum(c18_pop15p_cs3::float * cic.pct_iris_dans_circo) as pop15p_cs3,
            sum(c18_pop15p_cs4::float * cic.pct_iris_dans_circo) as pop15p_cs4,
            sum(c18_pop15p_cs5::float * cic.pct_iris_dans_circo) as pop15p_cs5,
            sum(c18_pop15p_cs6::float * cic.pct_iris_dans_circo) as pop15p_cs6,
            sum(c18_pop15p_cs7::float * cic.pct_iris_dans_circo) as pop15p_cs7,
            sum(c18_pop15p_cs8::float * cic.pct_iris_dans_circo) as pop15p_cs8,
            sum(c18_f15p_cs1::float * cic.pct_iris_dans_circo) as f15p_cs1,
            sum(c18_f15p_cs2::float * cic.pct_iris_dans_circo) as f15p_cs2,
            sum(c18_f15p_cs3::float * cic.pct_iris_dans_circo) as f15p_cs3,
            sum(c18_f15p_cs4::float * cic.pct_iris_dans_circo) as f15p_cs4,
            sum(c18_f15p_cs5::float * cic.pct_iris_dans_circo) as f15p_cs5,
            sum(c18_f15p_cs6::float * cic.pct_iris_dans_circo) as f15p_cs6,
            sum(c18_f15p_cs7::float * cic.pct_iris_dans_circo) as f15p_cs7,
            sum(c18_f15p_cs8::float * cic.pct_iris_dans_circo) as f15p_cs8,
            sum(p18_pop_fr::float * cic.pct_iris_dans_circo) as pop_fr,
            sum(p18_pop_etr::float * cic.pct_iris_dans_circo) as pop_etr,
            sum(p18_pop_imm::float * cic.pct_iris_dans_circo) as pop_imm
        FROM insee_pop_2018 p
        JOIN circo_iris_correspondance cic ON p.iris = cic.code_iris
        WHERE cic.ref_circo = '030-06'
        
SQL Execution time: 0.023322582244873047

        SELECT
            sum(c18_pop15p::float) as pop15p,
            sum(c18_f15p::float) as pop15f,
            sum(c18_pop15p_cs1::float) as pop15p_cs1,
            sum(c18_pop15p_cs2::float) as pop15p_cs2,
            sum(c18_pop15p_cs3::float) as pop15p_cs3,
            sum(c18_pop15p_cs4::float) as pop15p_cs4,
            sum(c18_pop15p_cs5::float) as pop15p_cs5,
            sum(c18_pop15p_cs6::float) as pop15p_cs6,
            sum(c18_pop15p_cs7::float) as pop15p_cs7,
            sum(c18_pop15p_cs8::float) as pop15p_cs8,
            sum(c18_f15p_cs1::float) as f15p_cs1,
            sum(c18_f15p_cs2::float) as f15p_cs2,
            sum(c18_f15p_cs3::float) as f15p_cs3,
            sum(c18_f15p_cs4::float) as f15p_cs4,
            sum(c18_f15p_cs5::float) as f15p_cs5,
            sum(c18_f15p_cs6::float) as f15p_cs6,
            sum(c18_f15p_cs7::float) as f15p_cs7,
            sum(c18_f15p_cs8::float) as f15p_cs8,
            sum(p18_pop_fr::float) as pop_fr,
            sum(p18_pop_etr::float) as pop_etr,
            sum(p18_pop_imm::float) as pop_imm
        FROM insee_pop_2018
        WHERE com LIKE '30%'
        
SQL Execution time: 0.02495718002319336

        SELECT
            sum(c18_pop15p::float) as pop15p,
            sum(c18_f15p::float) as pop15f,
            sum(c18_pop15p_cs1::float) as pop15p_cs1,
            sum(c18_pop15p_cs2::float) as pop15p_cs2,
            sum(c18_pop15p_cs3::float) as pop15p_cs3,
            sum(c18_pop15p_cs4::float) as pop15p_cs4,
            sum(c18_pop15p_cs5::float) as pop15p_cs5,
            sum(c18_pop15p_cs6::float) as pop15p_cs6,
            sum(c18_pop15p_cs7::float) as pop15p_cs7,
            sum(c18_pop15p_cs8::float) as pop15p_cs8,
            sum(c18_f15p_cs1::float) as f15p_cs1,
            sum(c18_f15p_cs2::float) as f15p_cs2,
            sum(c18_f15p_cs3::float) as f15p_cs3,
            sum(c18_f15p_cs4::float) as f15p_cs4,
            sum(c18_f15p_cs5::float) as f15p_cs5,
            sum(c18_f15p_cs6::float) as f15p_cs6,
            sum(c18_f15p_cs7::float) as f15p_cs7,
            sum(c18_f15p_cs8::float) as f15p_cs8,
            sum(p18_pop_fr::float) as pop_fr,
            sum(p18_pop_etr::float) as pop_etr,
            sum(p18_pop_imm::float) as pop_imm
        FROM insee_pop_2018
        
SQL Execution time: 0.2546806335449219

        SELECT 
            sum(c20_pop15p::float * cic.pct_iris_dans_circo) as pop15p,
            sum(c20_f15p::float * cic.pct_iris_dans_circo) as pop15f,
            sum(c20_pop15p_cs1::float * cic.pct_iris_dans_circo) as pop15p_cs1,
            sum(c20_pop15p_cs2::float * cic.pct_iris_dans_circo) as pop15p_cs2,
            sum(c20_pop15p_cs3::float * cic.pct_iris_dans_circo) as pop15p_cs3,
            sum(c20_pop15p_cs4::float * cic.pct_iris_dans_circo) as pop15p_cs4,
            sum(c20_pop15p_cs5::float * cic.pct_iris_dans_circo) as pop15p_cs5,
            sum(c20_pop15p_cs6::float * cic.pct_iris_dans_circo) as pop15p_cs6,
            sum(c20_pop15p_cs7::float * cic.pct_iris_dans_circo) as pop15p_cs7,
            sum(c20_pop15p_cs8::float * cic.pct_iris_dans_circo) as pop15p_cs8,
            sum(c20_f15p_cs1::float * cic.pct_iris_dans_circo) as f15p_cs1,
            sum(c20_f15p_cs2::float * cic.pct_iris_dans_circo) as f15p_cs2,
            sum(c20_f15p_cs3::float * cic.pct_iris_dans_circo) as f15p_cs3,
            sum(c20_f15p_cs4::float * cic.pct_iris_dans_circo) as f15p_cs4,
            sum(c20_f15p_cs5::float * cic.pct_iris_dans_circo) as f15p_cs5,
            sum(c20_f15p_cs6::float * cic.pct_iris_dans_circo) as f15p_cs6,
            sum(c20_f15p_cs7::float * cic.pct_iris_dans_circo) as f15p_cs7,
            sum(c20_f15p_cs8::float * cic.pct_iris_dans_circo) as f15p_cs8,
            sum(p20_pop_fr::float * cic.pct_iris_dans_circo) as pop_fr,
            sum(p20_pop_etr::float * cic.pct_iris_dans_circo) as pop_etr,
            sum(p20_pop_imm::float * cic.pct_iris_dans_circo) as pop_imm
        FROM insee_pop_2020 p
        JOIN circo_iris_correspondance cic ON p.iris = cic.code_iris
        WHERE cic.ref_circo = '030-06'
        
SQL Execution time: 0.003213167190551758

        SELECT
            sum(c20_pop15p::float) as pop15p,
            sum(c20_f15p::float) as pop15f,
            sum(c20_pop15p_cs1::float) as pop15p_cs1,
            sum(c20_pop15p_cs2::float) as pop15p_cs2,
            sum(c20_pop15p_cs3::float) as pop15p_cs3,
            sum(c20_pop15p_cs4::float) as pop15p_cs4,
            sum(c20_pop15p_cs5::float) as pop15p_cs5,
            sum(c20_pop15p_cs6::float) as pop15p_cs6,
            sum(c20_pop15p_cs7::float) as pop15p_cs7,
            sum(c20_pop15p_cs8::float) as pop15p_cs8,
            sum(c20_f15p_cs1::float) as f15p_cs1,
            sum(c20_f15p_cs2::float) as f15p_cs2,
            sum(c20_f15p_cs3::float) as f15p_cs3,
            sum(c20_f15p_cs4::float) as f15p_cs4,
            sum(c20_f15p_cs5::float) as f15p_cs5,
            sum(c20_f15p_cs6::float) as f15p_cs6,
            sum(c20_f15p_cs7::float) as f15p_cs7,
            sum(c20_f15p_cs8::float) as f15p_cs8,
            sum(p20_pop_fr::float) as pop_fr,
            sum(p20_pop_etr::float) as pop_etr,
            sum(p20_pop_imm::float) as pop_imm
        FROM insee_pop_2020
        WHERE com LIKE '30%'
        
SQL Execution time: 0.024960041046142578

        SELECT
            sum(c20_pop15p::float) as pop15p,
            sum(c20_f15p::float) as pop15f,
            sum(c20_pop15p_cs1::float) as pop15p_cs1,
            sum(c20_pop15p_cs2::float) as pop15p_cs2,
            sum(c20_pop15p_cs3::float) as pop15p_cs3,
            sum(c20_pop15p_cs4::float) as pop15p_cs4,
            sum(c20_pop15p_cs5::float) as pop15p_cs5,
            sum(c20_pop15p_cs6::float) as pop15p_cs6,
            sum(c20_pop15p_cs7::float) as pop15p_cs7,
            sum(c20_pop15p_cs8::float) as pop15p_cs8,
            sum(c20_f15p_cs1::float) as f15p_cs1,
            sum(c20_f15p_cs2::float) as f15p_cs2,
            sum(c20_f15p_cs3::float) as f15p_cs3,
            sum(c20_f15p_cs4::float) as f15p_cs4,
            sum(c20_f15p_cs5::float) as f15p_cs5,
            sum(c20_f15p_cs6::float) as f15p_cs6,
            sum(c20_f15p_cs7::float) as f15p_cs7,
            sum(c20_f15p_cs8::float) as f15p_cs8,
            sum(p20_pop_fr::float) as pop_fr,
            sum(p20_pop_etr::float) as pop_etr,
            sum(p20_pop_imm::float) as pop_imm
        FROM insee_pop_2020
        
SQL Execution time: 0.2506754398345947

        SELECT 
            sum(c21_pop15p::float * cic.pct_iris_dans_circo) as pop15p,
            sum(c21_f15p::float * cic.pct_iris_dans_circo) as pop15f,
            sum(c21_pop15p_cs1::float * cic.pct_iris_dans_circo) as pop15p_cs1,
            sum(c21_pop15p_cs2::float * cic.pct_iris_dans_circo) as pop15p_cs2,
            sum(c21_pop15p_cs3::float * cic.pct_iris_dans_circo) as pop15p_cs3,
            sum(c21_pop15p_cs4::float * cic.pct_iris_dans_circo) as pop15p_cs4,
            sum(c21_pop15p_cs5::float * cic.pct_iris_dans_circo) as pop15p_cs5,
            sum(c21_pop15p_cs6::float * cic.pct_iris_dans_circo) as pop15p_cs6,
            sum(c21_pop15p_cs7::float * cic.pct_iris_dans_circo) as pop15p_cs7,
            sum(c21_pop15p_cs8::float * cic.pct_iris_dans_circo) as pop15p_cs8,
            sum(c21_f15p_cs1::float * cic.pct_iris_dans_circo) as f15p_cs1,
            sum(c21_f15p_cs2::float * cic.pct_iris_dans_circo) as f15p_cs2,
            sum(c21_f15p_cs3::float * cic.pct_iris_dans_circo) as f15p_cs3,
            sum(c21_f15p_cs4::float * cic.pct_iris_dans_circo) as f15p_cs4,
            sum(c21_f15p_cs5::float * cic.pct_iris_dans_circo) as f15p_cs5,
            sum(c21_f15p_cs6::float * cic.pct_iris_dans_circo) as f15p_cs6,
            sum(c21_f15p_cs7::float * cic.pct_iris_dans_circo) as f15p_cs7,
            sum(c21_f15p_cs8::float * cic.pct_iris_dans_circo) as f15p_cs8,
            sum(p21_pop_fr::float * cic.pct_iris_dans_circo) as pop_fr,
            sum(p21_pop_etr::float * cic.pct_iris_dans_circo) as pop_etr,
            sum(p21_pop_imm::float * cic.pct_iris_dans_circo) as pop_imm
        FROM insee_pop_2021 p
        JOIN circo_iris_correspondance cic ON p.iris = cic.code_iris
        WHERE cic.ref_circo = '030-06'
        
SQL Execution time: 0.008628606796264648

        SELECT
            sum(c21_pop15p::float) as pop15p,
            sum(c21_f15p::float) as pop15f,
            sum(c21_pop15p_cs1::float) as pop15p_cs1,
            sum(c21_pop15p_cs2::float) as pop15p_cs2,
            sum(c21_pop15p_cs3::float) as pop15p_cs3,
            sum(c21_pop15p_cs4::float) as pop15p_cs4,
            sum(c21_pop15p_cs5::float) as pop15p_cs5,
            sum(c21_pop15p_cs6::float) as pop15p_cs6,
            sum(c21_pop15p_cs7::float) as pop15p_cs7,
            sum(c21_pop15p_cs8::float) as pop15p_cs8,
            sum(c21_f15p_cs1::float) as f15p_cs1,
            sum(c21_f15p_cs2::float) as f15p_cs2,
            sum(c21_f15p_cs3::float) as f15p_cs3,
            sum(c21_f15p_cs4::float) as f15p_cs4,
            sum(c21_f15p_cs5::float) as f15p_cs5,
            sum(c21_f15p_cs6::float) as f15p_cs6,
            sum(c21_f15p_cs7::float) as f15p_cs7,
            sum(c21_f15p_cs8::float) as f15p_cs8,
            sum(p21_pop_fr::float) as pop_fr,
            sum(p21_pop_etr::float) as pop_etr,
            sum(p21_pop_imm::float) as pop_imm
        FROM insee_pop_2021
        WHERE com LIKE '30%'
        
SQL Execution time: 0.03498196601867676

        SELECT
            sum(c21_pop15p::float) as pop15p,
            sum(c21_f15p::float) as pop15f,
            sum(c21_pop15p_cs1::float) as pop15p_cs1,
            sum(c21_pop15p_cs2::float) as pop15p_cs2,
            sum(c21_pop15p_cs3::float) as pop15p_cs3,
            sum(c21_pop15p_cs4::float) as pop15p_cs4,
            sum(c21_pop15p_cs5::float) as pop15p_cs5,
            sum(c21_pop15p_cs6::float) as pop15p_cs6,
            sum(c21_pop15p_cs7::float) as pop15p_cs7,
            sum(c21_pop15p_cs8::float) as pop15p_cs8,
            sum(c21_f15p_cs1::float) as f15p_cs1,
            sum(c21_f15p_cs2::float) as f15p_cs2,
            sum(c21_f15p_cs3::float) as f15p_cs3,
            sum(c21_f15p_cs4::float) as f15p_cs4,
            sum(c21_f15p_cs5::float) as f15p_cs5,
            sum(c21_f15p_cs6::float) as f15p_cs6,
            sum(c21_f15p_cs7::float) as f15p_cs7,
            sum(c21_f15p_cs8::float) as f15p_cs8,
            sum(p21_pop_fr::float) as pop_fr,
            sum(p21_pop_etr::float) as pop_etr,
            sum(p21_pop_imm::float) as pop_imm
        FROM insee_pop_2021
        
SQL Execution time: 0.3739292621612549

                WITH population_par_commune AS (
                    SELECT 
                        cic.insee_com,
                        COALESCE(
                            iam.nom,  -- Nom depuis ign_arrondissement_municipal pour les arrondissements
                            cc.nom,   -- Nom depuis circo_commune pour les communes
                            ic.nom,   -- Fallback sur ign_commune
                            'Commune inconnue'
                        ) as nom_commune,
                        SUBSTRING(cic.insee_com FROM 1 FOR 2) as code_departement,
                        SUM(
                            CASE 
                                WHEN ip21.p21_pop ~ '^[0-9.]+$' THEN 
                                    CAST(ip21.p21_pop AS NUMERIC) * cic.pct_iris_dans_circo
                                ELSE 0 
                            END
                        ) as population_ponderee,
                        COUNT(*) as nb_iris,
                        ROUND(AVG(cic.pct_iris_dans_circo)::numeric, 4) as pct_moyen_iris_dans_circo
                    FROM circo_iris_correspondance cic
                    JOIN insee_pop_2021 ip21 ON cic.code_iris = ip21.iris
                    LEFT JOIN circo_commune cc ON cic.insee_com = cc.insee_com AND cic.ref_circo = cc.circo
                    LEFT JOIN ign_arrondissement_municipal iam ON cic.insee_com = iam.insee_arm
                    LEFT JOIN ign_commune ic ON cic.insee_com = ic.insee_com
                    WHERE cic.ref_circo = '030-06'
                    GROUP BY cic.insee_com, iam.nom, cc.nom, ic.nom
                    ORDER BY population_ponderee DESC
                )
                SELECT 
                    insee_com,
                    nom_commune,
                    code_departement,
                    ROUND(population_ponderee::numeric, 0) as population_2021,
                    nb_iris,
                    pct_moyen_iris_dans_circo,
                    CASE 
                        WHEN pct_moyen_iris_dans_circo >= 0.99 THEN 'Entièrement'
                        WHEN pct_moyen_iris_dans_circo >= 0.5 THEN 'Majoritairement'
                        ELSE 'Partiellement'
                    END as inclusion_dans_circo
                FROM population_par_commune
                ORDER BY population_2021 DESC
                
SQL Execution time: 0.012367725372314453

            select coalesce(p.ind,0)::int as pop_2010,
                coalesce(p2.ind,0)::int as pop_2015,
                st_asgeojson((st_dump(st_intersection(coalesce(p.wkb_geometry,p2.wkb_geometry),c.wkb_geometry))).geom)::json as geojson
            from zone_circo c
            left join insee_population_carroyee_2015_1k p on (st_intersects(c.wkb_geometry,p.wkb_geometry))
            left join insee_population_carroyee_2010_1k p2 on (p2.wkb_geometry&& p.wkb_geometry and st_intersects(p.wkb_geometry, st_centroid(p2.wkb_geometry)))
            where ref='030-06'
            
SQL Execution time: 0.7358899116516113

        SELECT coalesce(sum(ind * pct),0)
        FROM circo_carreaux200m_2015
        NATURAL JOIN insee_population_carroyee_2015 p
        where ind/0.04>=0 and ind/0.04<=25 and ref='030-06'
        
SQL Execution time: 0.019585847854614258

        SELECT coalesce(sum(ind * pct),0)
        FROM circo_carreaux200m_2015
        NATURAL JOIN insee_population_carroyee_2015 p
        where ind/0.04>25 and ind/0.04<=300 and ref='030-06'
        
SQL Execution time: 0.01281881332397461

        SELECT coalesce(sum(ind * pct),0)
        FROM circo_carreaux200m_2015
        NATURAL JOIN insee_population_carroyee_2015 p
        where ind/0.04>300 and ind/0.04<=1500 and ref='030-06'
        
SQL Execution time: 0.012295007705688477

        SELECT coalesce(sum(ind * pct),0)
        FROM circo_carreaux200m_2015
        NATURAL JOIN insee_population_carroyee_2015 p
        where ind/0.04>1500 and ref='030-06'
        
SQL Execution time: 0.012777566909790039

            SELECT ind as densite,
                st_asgeojson((st_dump(st_intersection(p.wkb_geometry,c.wkb_geometry))).geom)::json as geojson
            FROM
                insee_population_carroyee_2015_1k p
            JOIN zone_circo c on (st_intersects(c.wkb_geometry,p.wkb_geometry))
            WHERE ref='030-06'
            
SQL Execution time: 1.7062196731567383

        SELECT 
            densite_tres_peu_dense,
            densite_peu_dense,
            densite_intermediaire,
            densite_densement_peuple
        FROM iris_2018_density_stats
        WHERE ref_circo = '030-06'
        
SQL Execution time: 0.04615950584411621

        SELECT
            densite,
            geojson
        FROM iris_2018_circo_density
        WHERE ref_circo = '030-06'
        
SQL Execution time: 0.07780623435974121

        SELECT 
            densite_tres_peu_dense,
            densite_peu_dense,
            densite_intermediaire,
            densite_densement_peuple
        FROM iris_2021_density_stats
        WHERE ref_circo = '030-06'
        
SQL Execution time: 0.046602725982666016

        SELECT
            densite,
            geojson
        FROM iris_2021_circo_density
        WHERE ref_circo = '030-06'
        
SQL Execution time: 0.0730125904083252

    select
        sum(men * pct),
        sum(men_pauv * pct),
        sum(men_1ind * pct),
        sum(men_5ind * pct),
        sum(men_fmp * pct),
        sum(men_prop * pct),
        sum(men_coll * pct),
        sum(men_mais * pct),
        sum(ind_snv * pct)
    FROM circo_carreaux200m_2015 cp
    JOIN insee_population_carroyee_2015 p ON cp.idinspire = p.idinspire
    WHERE cp.ref='030-06'
    
SQL Execution time: 0.06425857543945312

        select
            sum(men * pct),
            sum(men_pauv * pct),
            sum(men_1ind * pct),
            sum(men_5ind * pct),
            sum(men_fmp * pct),
            sum(men_prop * pct),
            sum(men_coll * pct),
            sum(men_mais * pct),
            sum(ind_snv * pct)
        FROM circo_carreaux200m_2015 cp
        JOIN insee_population_carroyee_2015 p ON cp.idinspire = p.idinspire
        WHERE cp.ref LIKE '030%'
        
SQL Execution time: 0.15302276611328125

        select
            sum(men),
            sum(men_pauv),
            sum(men_1ind),
            sum(men_5ind),
            sum(men_fmp),
            sum(men_prop),
            sum(men_coll),
            sum(men_mais),
            sum(ind_snv)
        from insee_population_carroyee_2015 p
        
SQL Execution time: 1.061676263809204

        select
            sum(men * pct),
            sum(men_pauv * pct),
            sum(men_1ind * pct),
            sum(men_5ind * pct),
            sum(men_fmp * pct),
            sum(men_prop * pct),
            sum(men_coll * pct),
            sum(men_mais * pct),
            sum(ind_snv * pct)
        FROM circo_carreaux200m_2019 cp
        JOIN insee_population_carroyee_2019 p ON cp.idinspire = p.idinspire
        WHERE cp.ref='030-06'
        
SQL Execution time: 0.04745888710021973

        select
            sum(men * pct),
            sum(men_pauv * pct),
            sum(men_1ind * pct),
            sum(men_5ind * pct),
            sum(men_fmp * pct),
            sum(men_prop * pct),
            sum(men_coll * pct),
            sum(men_mais * pct),
            sum(ind_snv * pct)
        FROM circo_carreaux200m_2019 cp
        JOIN insee_population_carroyee_2019 p ON cp.idinspire = p.idinspire
        WHERE cp.ref LIKE '030%'
        
SQL Execution time: 0.16250181198120117

        select
            sum(men),
            sum(men_pauv),
            sum(men_1ind),
            sum(men_5ind),
            sum(men_fmp),
            sum(men_prop),
            sum(men_coll),
            sum(men_mais),
            sum(ind_snv)
        from insee_population_carroyee_2019 p
        
SQL Execution time: 1.359426498413086

        select
            sum(men * pct),
            sum(men_pauv * pct),
            sum(men_1ind * pct),
            sum(men_5ind * pct),
            sum(men_prop * pct),
            sum(men_coll * pct),
            sum(ind_snv * pct)
        FROM circo_carreaux200m_2010 cp
        JOIN insee_population_carroyee_2010 p ON cp.idinspire = p.idinspire
        WHERE cp.ref='030-06'
        
SQL Execution time: 0.6580524444580078

        select
            sum(men * pct),
            sum(men_pauv * pct),
            sum(men_1ind * pct),
            sum(men_5ind * pct),
            sum(men_prop * pct),
            sum(men_coll * pct),
            sum(ind_snv * pct)
        FROM circo_carreaux200m_2010 cp
        JOIN insee_population_carroyee_2010 p ON cp.idinspire = p.idinspire
        WHERE cp.ref LIKE '030%'
        
SQL Execution time: 0.8893938064575195

        select
            sum(men),
            sum(men_pauv),
            sum(men_1ind),
            sum(men_5ind),
            sum(men_prop),
            sum(men_coll),
            sum(ind_snv)
        from insee_population_carroyee_2010 p
        
SQL Execution time: 1.1614654064178467

                select d.*, n.femmes_nat, n.total_nat from
                (select elu, ordre,
                    sum(case when code_sexe='F' then 1 else 0 end) as femmes,
                    count(*) as total
                from rne
                where rne.code_du_departement='30' 
                group by 1, 2) as d
                join
                (select elu, ordre,
                    sum(case when code_sexe='F' then 1 else 0 end) as femmes_nat,
                    count(*) as total_nat
                from rne
                group by 1, 2) as n ON (n.elu=d.elu)
                order by d.ordre desc;
                
SQL Execution time: 1.3313186168670654

        select
            case when left(categoriejuridiqueunitelegale,1) in ('4','7') then 'Public'
            else coalesce(categorieentreprise,'autre') end,
            count(*),
            sum(case when trancheeffectifsetablissement NOT IN ('NN','00') then 1 else 0 end) as employeur,
            count(distinct(et.siren))
        from zone_circo c
        join insee_siret et on (st_intersects(c.wkb_geometry, et.geom))
        join insee_siren en on (et.siren=en.siren)
        where
            ref = '030-06'
            and etatadministratifetablissement = 'A'
        GROUP BY 1
        ORDER BY length(case when left(categoriejuridiqueunitelegale,1) in ('4','7') then 'Public'
            else coalesce(categorieentreprise,'autre') end),1
        
SQL Execution time: 27.30900549888611

        select
            case when left(categoriejuridiqueunitelegale,1) in ('4','7') then 'Public'
            else coalesce(categorieentreprise,'autre') end,
            count(*)
        from zone_circo c
        join insee_siret et on (st_intersects(c.wkb_geometry, et.geom))
        join insee_siren en on (et.siren=en.siren and nicsiegeunitelegale=et.nic)
        where
            ref = '030-06'
            and etatadministratifetablissement = 'A'
        GROUP BY 1
        ORDER BY length(case when left(categoriejuridiqueunitelegale,1) in ('4','7') then 'Public'
            else coalesce(categorieentreprise,'autre') end),1
        
SQL Execution time: 6.7640228271484375

        select
            categorieentreprise,
            count(*)
        from zone_circo c
        join insee_siret et on (st_intersects(c.wkb_geometry, et.geom))
        join insee_siren en on (et.siren=en.siren)
        where ref = '030-06'
            and categoriejuridiqueunitelegale='1000'
            and etatadministratifetablissement = 'A'
        group by 1
        order by 1
        
SQL Execution time: 17.88965940475464

                select
                    trancheeffectifsetablissement,
                    count(*)
                from zone_circo c
                join insee_siret et on (st_intersects(c.wkb_geometry, et.geom))
                join insee_siren en on (et.siren=en.siren)
                where
                    ref = '030-06'
                    and etatadministratifetablissement = 'A'
                    AND trancheeffectifsetablissement NOT IN ('NN','00')
                GROUP BY 1
                ORDER BY 1
                
SQL Execution time: 2.242401123046875

        select
            et.siren,
            coalesce(denominationusuelleetablissement, denominationunitelegale, denominationusuelle1unitelegale) as nom,
            trancheeffectifsetablissement,
            case when left(categoriejuridiqueunitelegale,1) in ('4','7') then 'Public' else coalesce(categorieentreprise) end,
            case when coalesce(denominationusuelleetablissement, denominationunitelegale, denominationusuelle1unitelegale) ~ replace(libellecommuneetablissement,'-', ' ')
                then ''
                else '('||libellecommuneetablissement||')' end,
            latitude, longitude
        from zone_circo c
        join insee_siret et on (st_intersects(c.wkb_geometry, et.geom))
        join insee_siren en on (et.siren=en.siren)
        where ref = '030-06'
            and trancheeffectifsetablissement not in ('NN')
            and trancheeffectifsetablissement >= '22'
            and etatadministratifetablissement = 'A'
        order by 3 desc, 2
        limit 50;
        
SQL Execution time: 1.5803043842315674

            select
                left(date_immatriculation,4)::int as annee,
                count(*) as immatriculations
            from rncs_immatriculations_by_circo
            where ref_circo='030-06'
            and left(date_immatriculation,4)::int < date_part('year', CURRENT_DATE)
            group by 1
            order by 1;
            
SQL Execution time: 0.451279878616333

            select
                left(date_radiation,4)::int as annee,
                count(*) as radiations
            from rncs_radiations_by_circo
            where ref_circo='030-06'
            and left(date_radiation,4)::int < date_part('year', CURRENT_DATE)
            group by 1
            order by 1;
            
SQL Execution time: 0.07535362243652344

            select annee, immatriculations
            from rncs_immatriculations_stats_nat
            order by 1;
            
SQL Execution time: 0.04391002655029297

            select annee, radiations
            from rncs_radiations_stats_nat
            order by 1;
            
SQL Execution time: 0.04150962829589844

                select
                    i.annee_mois,
                    i.immatriculations,
                    r.radiations,
                    i.immatriculations - r.radiations as solde
                from (
                    select
                        left(date_radiation,7) as annee_mois,
                        count(*) as radiations
                    from rncs_radiations_by_circo
                    where ref_circo='030-06'
                        and to_date(date_radiation,'YYYY-MM-DD')>date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
                        and to_date(date_radiation,'YYYY-MM-DD')<date_trunc('month', CURRENT_DATE)
                    group by 1
                ) as r,
                (
                    select
                        left(date_immatriculation,7) as annee_mois,
                        count(*) as immatriculations
                    from rncs_immatriculations_by_circo
                    where ref_circo='030-06'
                        and to_date(date_immatriculation,'YYYY-MM-DD')>date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
                        and to_date(date_immatriculation,'YYYY-MM-DD')<date_trunc('month', CURRENT_DATE)
                    group by 1
                ) as i
                where r.annee_mois=i.annee_mois
                order by 1
                
SQL Execution time: 0.0691063404083252

        select
            grand_secteur_d_activite,
            substring(grand_secteur_d_activite,5),
            coalesce(sum(effectifs_salaries_2021::int),0) as effectifs_2021,
            coalesce(sum(effectifs_salaries_2022::int),0) as effectifs_2022,
            coalesce(sum(effectifs_salaries_2023::int),0) as effectifs_2023
        from
            urssaf_salaries_prive
        where
            code_commune in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
        group by 1,2 order by 1,2;
        
SQL Execution time: 0.28551602363586426

        select
            grand_secteur_d_activite,
            secteur_na38,
            substring(grand_secteur_d_activite,5),
            substring(secteur_na38,4),
            coalesce(sum(effectifs_salaries_2006::int),0)  as effectifs_2006,
            coalesce(sum(effectifs_salaries_2021::int),0) as effectifs_2021,
            coalesce(sum(effectifs_salaries_2022::int),0) as effectifs_2022,
            coalesce(sum(effectifs_salaries_2023::int),0) as effectifs_2023
        from
            urssaf_salaries_prive
        where
            code_commune in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
        group by 1,2,3,4 order by 1,2;
        
SQL Execution time: 0.005869626998901367

        select
            st_asgeojson(wkb_geometry),
            nom,
            sum(coalesce(effectifs_salaries_2023,'0')::int - coalesce(effectifs_salaries_2006,'0')::int) as evolution,
            ST_X(St_centroid(wkb_geometry)),
            ST_Y(St_centroid(wkb_geometry))
        from
            urssaf_salaries_prive
        JOIN ign_commune co ON (co.insee_com=code_commune)
        where
            code_commune in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
        GROUP BY 1,2,4,5 ORDER BY 3 DESC
        
SQL Execution time: 1.2753148078918457

                    SELECT
                        st_asgeojson((st_dump(st_intersection(p.wkb_geometry,c.wkb_geometry))).geom)::json as geojson,
                        round(100.0*men_coll/men,1) as pct_logement_collectifs,
                        round(100.0*men_prop/men,1) as pct_proprietaires
                    FROM
                        insee_population_carroyee_2015_1k p
                    JOIN zone_circo c on (st_intersects(c.wkb_geometry,p.wkb_geometry))
                    WHERE ref='030-06'
                    
SQL Execution time: 2.168548583984375
 SELECT log_res::numeric, log_sec::numeric, log_vac::numeric,
                proprio::numeric, locatai::numeric, gratuit::numeric, maison::numeric, ach90::numeric, COALESCE(mfuel::numeric, 0) as mfuel
                FROM insee_indic_stat_circo_2022 WHERE circo in ('030-06', '000-00')
                ORDER BY CASE circo
                WHEN '030-06' THEN 1
                WHEN '000-00' THEN 2
                END;
SQL Execution time: 0.011263608932495117
SELECT 
                    SUM(p21_log::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RP::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RSECOCC::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RPMAISON::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RPAPPART::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RP_PROP::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RP_LOC::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_RP_GRAT::numeric * corr.pct_circo_dans_iris),
                    SUM(p21_LOGVAC::numeric * corr.pct_circo_dans_iris),
                    SUM((p21_RP_ACH19::numeric + p21_RP_ACH45::numeric + p21_RP_ACH70::numeric + p21_RP_ACH90::numeric) * corr.pct_circo_dans_iris)
                FROM insee_logement_2021 AS log 
                JOIN circo_iris_correspondance AS corr ON corr.code_iris = log.iris
                WHERE log.p21_RSECOCC IS NOT NULL AND corr.ref_circo = '030-06';
SQL Execution time: 0.043688297271728516
SELECT 
                    SUM(p21_log::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RP::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RSECOCC::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RPMAISON::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RPAPPART::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RP_PROP::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RP_LOC::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_RP_GRAT::numeric * corr.pct_iris_dans_dept),
                    SUM(p21_LOGVAC::numeric * corr.pct_iris_dans_dept),
                    SUM((p21_RP_ACH19::numeric + p21_RP_ACH45::numeric + p21_RP_ACH70::numeric + p21_RP_ACH90::numeric) * corr.pct_iris_dans_dept)
                FROM insee_logement_2021 AS log 
                JOIN iris_departement_correspondance AS corr ON corr.code_iris = log.iris
                WHERE log.p21_RSECOCC IS NOT NULL AND corr.insee_dep = '30';
SQL Execution time: 0.06361651420593262
SELECT SUM(p21_log::numeric),
                    SUM(p21_RP::numeric),
                    SUM(p21_RSECOCC::numeric),
                    SUM(p21_RPMAISON::numeric),
                    SUM(p21_RPAPPART::numeric),
                    SUM(p21_RP_PROP::numeric),
                    SUM(p21_RP_LOC::numeric),
                    SUM(p21_RP_GRAT::numeric),
                    SUM(p21_LOGVAC::numeric),
                    SUM((p21_RP_ACH19::numeric + p21_RP_ACH45::numeric + p21_RP_ACH70::numeric + p21_RP_ACH90::numeric))
                            FROM insee_logement_2021  AS log 
                            JOIN iris_2021 AS iris ON (iris.dcomiris || iris.iris) = log.iris
                            JOIN ign_departement AS dep ON st_intersects(iris.wkb_geometry, dep.wkb_geometry);
SQL Execution time: 6.508617877960205

            SELECT enum.etiquette_dpe as classe_consommation_energie, coalesce(compte.nb_dpe, 0) as nb
            FROM (
                SELECT DISTINCT etiquette_dpe
                FROM ademe_dpe_logements_2024
                GROUP BY 1) as enum
                NATURAL LEFT JOIN (
                    SELECT d.etiquette_dpe, count(DISTINCT d.ndpe) as nb_dpe
                    FROM ademe_dpe_logements_2024 as d
                    LEFT JOIN zone_circo c
                    ON st_intersects(c.wkb_geometry, d.geom)
                    WHERE c.ref = '030-06' AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
                    AND d.etiquette_dpe between 'A' and 'G'
                    GROUP BY 1
                    ) as compte
                ORDER BY 1;
SQL Execution time: 3.6387979984283447
SELECT enum.etiquette_dpe as classe_consommation_energie, coalesce(compte.nb_dpe, 0) as sum
                FROM (
                    SELECT DISTINCT etiquette_dpe
                    FROM ademe_dpe_logements_2024
                    GROUP BY 1) as enum
                NATURAL LEFT JOIN (
                    SELECT d.etiquette_dpe, count(DISTINCT d.ndpe) as nb_dpe
                    FROM ademe_dpe_logements_2024 as d
                    LEFT JOIN zone_circo c
                    ON st_intersects(c.wkb_geometry, d.geom)
                    WHERE LPAD(RTRIM(RTRIM(CAST(d.n_departement__ban_ AS VARCHAR),'.'),'.0'),3,'0') = '030'
                    AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
                    AND d.etiquette_dpe between 'A' and 'G'
                    GROUP BY d.etiquette_dpe) as compte;
SQL Execution time: 13.27797555923462

                SELECT *
                FROM ademe_dpe_france_2024;
SQL Execution time: 0.0621945858001709

                SELECT classe_consommation_energie, 
                    annee_2013, annee_2014, annee_2015, 
                    annee_2016, annee_2017, annee_2018,
                    annee_2019, annee_2020, annee_2021
                FROM dpe_stats_by_circo
                WHERE circonscription = '030-06'
                ORDER BY classe_consommation_energie;
                
SQL Execution time: 0.05001640319824219
SELECT classe_consommation_energie, coalesce(a.annee_2021, 0) as annee_2021, coalesce(a.annee_2022, 0) as annee_2022, coalesce(a.annee_2023, 0) as annee_2023 FROM (
                select * from crosstab(
                    $$SELECT d.etiquette_dpe, LEFT(d.date_etablissement_dpe,4) as annee_etablissement_DPE, coalesce(count(DISTINCT d.ndpe), 0) as nb_dpe
                    FROM ademe_dpe_logements_2024 as d
                    LEFT JOIN zone_circo c
                    ON st_intersects(c.wkb_geometry, d.geom)
                    WHERE c.ref = '030-06'
                    AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
                    AND d.etiquette_dpe between 'A' and 'G'
                    GROUP BY 1,2 ORDER BY 1,2$$,
                    $$SELECT generate_series(2021,2023)$$
                ) as T (
                    classe_consommation_energie text,
                    "annee_2021" int,
                    "annee_2022" int,
                    "annee_2023" int
                )) as a
                ORDER BY 1;
                
SQL Execution time: 0.20993661880493164

            SELECT enum.etiquette_ges as classe_ges, coalesce(compte.nb_ges, 0) as nb
            FROM (
                SELECT DISTINCT etiquette_ges
                FROM ademe_dpe_logements_2024
                GROUP BY 1) as enum
                NATURAL LEFT JOIN (
                    SELECT d.etiquette_ges, count(DISTINCT d.ndpe) as nb_ges
                    FROM ademe_dpe_logements_2024 as d
                    LEFT JOIN zone_circo c
                    ON st_intersects(c.wkb_geometry, d.geom)
                    WHERE c.ref = '030-06'
                    AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
                    AND d.etiquette_ges IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
                    GROUP BY 1
                    ) as compte
                ORDER BY 1;
SQL Execution time: 2.4027011394500732
SELECT enum.etiquette_ges as classe_ges, coalesce(compte.nb_ges, 0) as sum
                FROM (
                    SELECT DISTINCT etiquette_ges
                    FROM ademe_dpe_logements_2024
                    GROUP BY 1) as enum
                NATURAL LEFT JOIN (
                    SELECT d.etiquette_ges, count(DISTINCT d.ndpe) as nb_ges
                    FROM ademe_dpe_logements_2024 as d
                    LEFT JOIN zone_circo c
                    ON st_intersects(c.wkb_geometry, d.geom)
                    WHERE LPAD(RTRIM(RTRIM(CAST(d.n_departement__ban_ AS VARCHAR),'.'),'.0'),3,'0') = '030'
                    AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
                    AND d.etiquette_ges IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
                    GROUP BY d.etiquette_ges) as compte;
SQL Execution time: 13.487701177597046

                SELECT *
                FROM ademe_ges_france_2024;
SQL Execution time: 0.04156064987182617
SELECT classe_ges, coalesce(a.annee_2021, 0) as annee_2021, coalesce(a.annee_2022, 0) as annee_2022, coalesce(a.annee_2023, 0) as annee_2023 FROM (
                select * from crosstab(
                    $$SELECT d.etiquette_ges, LEFT(d.date_etablissement_dpe,4) as annee_etablissement_GES, coalesce(count(DISTINCT d.ndpe), 0) as nb_dpe
                    FROM ademe_dpe_logements_2024 as d
                    LEFT JOIN zone_circo c
                    ON st_intersects(c.wkb_geometry, d.geom)
                    WHERE c.ref = '030-06'
                    AND CAST(d.date_etablissement_dpe as VARCHAR) NOT LIKE '2024%'
                    AND d.etiquette_ges IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
                    GROUP BY 1,2 ORDER BY 1,2$$,
                    $$SELECT generate_series(2021,2023)$$
                ) as T (
                    classe_ges text,
                    "annee_2021" int,
                    "annee_2022" int,
                    "annee_2023" int
                )) as a
                ORDER BY 1;
                
SQL Execution time: 0.5099215507507324

            SELECT classe_ges,
                coalesce(annee_2013, 0) as annee_2013, coalesce(annee_2014, 0) as annee_2014, coalesce(annee_2015, 0) as annee_2015,
                        coalesce(annee_2016, 0) as annee_2016, coalesce(annee_2017, 0) as annee_2017, coalesce(annee_2018, 0) as annee_2018,
                        coalesce(annee_2019, 0) as annee_2019, coalesce(annee_2020, 0) as annee_2020, coalesce(annee_2021, 0) as annee_2021
            FROM crosstab(
                $$SELECT 
                    classe_estimation_ges,
                    annee,
                    SUM(nb_ges)
                FROM mv_dpe_stats_2013_2021
                WHERE circonscription = '030-06'
                GROUP BY 1,2
                ORDER BY 1,2$$,
                $$SELECT generate_series(2013,2021)$$
            ) AS T (
                                classe_ges text,
                                "annee_2013" int,
                                "annee_2014" int,
                                "annee_2015" int,
                                "annee_2016" int,
                                "annee_2017" int,
                                "annee_2018" int,
                                "annee_2019" int,
                                "annee_2020" int,
                                "annee_2021" int
                            );
                            
SQL Execution time: 0.25041913986206055

                SELECT france 
                FROM population_france
                WHERE annee=2023
                
SQL Execution time: 0.002328634262084961

                SELECT sum(population) FROM (
                    SELECT population FROM ign_commune WHERE insee_com in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337') AND insee_com not in ('13055','69123','75056')
                    UNION
                    SELECT population FROM ign_arrondissement_municipal WHERE insee_arm in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
                ) territoire
                
SQL Execution time: 0.001142740249633789

            SELECT
                categ_niv3_code,
                categ_niv3_lib,
                count(*)
            FROM
                finess
            WHERE etat='ACTUEL'
                and com_code in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
                and categ_niv3_lib != ''
            GROUP BY 1,2
            ORDER BY 1
            
SQL Execution time: 0.0034165382385253906

        select
            eml.libeml,
            sum(case when ci.libeml is not null then 1 else 0 end) as nb_circo
        from (select libeml from finess_eml group by 1) as eml
        left join (
            select eml.libeml
            from finess et
            join finess_eml eml on (eml.nofinesset=finess)
            where com_code in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
                and etat='ACTUEL'
            ) as ci
            on (eml.libeml=ci.libeml)
        group by 1
        
SQL Execution time: 0.004389286041259766

        SELECT circo.*, nb FROM (
            SELECT
                categ_code,
                categ_lib,
                count(*)
            FROM
                finess
            WHERE etat='ACTUEL'
                and com_code in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
                and categ_code >='600' and categ_code <='621'
            GROUP BY 1,2
        ) as circo left join (
            SELECT
                categ_code,
                categ_lib,
                count(*) as nb
            FROM
                finess
            WHERE etat='ACTUEL'
                and categ_code >='600' and categ_code <='621'
            GROUP BY 1,2
        ) as nat on (nat.categ_code = circo.categ_code)
            ORDER BY 1
            
SQL Execution time: 0.3523857593536377

        SELECT
            nat.lib_profession, coalesce(circo.nb,0), nat.nb
        FROM (
            select
                lib_profession, count(*) as nb
            from (
                select
                    nom, prenom, lib_profession
                from laposte_cp cp
                join cnamts_prof_sante_lib ps on (code_postal=cp and ville in (nom_de_la_commune, ligne_5, libelle_d_acheminement))
                where sexe is not null and lib_profession is not null
                UNION
                select
                    nom, prenom, lib_profession
                from laposte_cp cp
                join cnamts_prof_sante_etab_lib ps on (code_postal=cp and ville in (nom_de_la_commune, ligne_5, libelle_d_acheminement))
                where lib_profession is not null
                group by 1,2,3
            ) ps group by 1
        ) as nat
        LEFT JOIN (
            select
                lib_profession, count(*) as nb
            from (
                select
                    nom, prenom, lib_profession
                from laposte_cp cp
                join cnamts_prof_sante_lib ps on (code_postal=cp and ville in (nom_de_la_commune, ligne_5, libelle_d_acheminement))
                where code_commune_insee in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337') and sexe is not null and lib_profession is not null

                UNION

                select
                    nom, prenom, lib_profession
                from laposte_cp cp
                join cnamts_prof_sante_etab_lib ps on (code_postal=cp and ville in (nom_de_la_commune, ligne_5, libelle_d_acheminement))
                where code_commune_insee in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337') and lib_profession is not null
                group by 1,2,3
            ) ps group by 1
        ) as circo
        ON (nat.lib_profession = circo.lib_profession)
        WHERE nat.nb > 500
        ORDER BY 3 DESC;
        
SQL Execution time: 3.390822172164917

        SELECT
            left(typ_equip,4),
            (tps_total+2.5)::int / 5 * 5,
            count(*),
            sum(pop_2017::int)
        FROM drees_acces_urgences_2019 ur
        WHERE depcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
        GROUP BY 1,2
        ORDER BY 2,1 desc;
        
SQL Execution time: 0.06465935707092285

                        SELECT
                                ur.typ_equip,
                                ur.tps_total,
                                st_asgeojson(coalesce(co.wkb_geometry,ar.wkb_geometry)) as geojson
                        FROM drees_acces_urgences_2019 ur
                        LEFT JOIN ign_commune co ON (co.insee_com=ur.depcom)
                        LEFT JOIN ign_arrondissement_municipal ar ON (ar.insee_arm=ur.depcom)
                        WHERE ur.depcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
                        
SQL Execution time: 0.0830526351928711

            SELECT y.depcom, y.nom, y.latitude, y.longitude, y.typ_equip
            FROM (
            SELECT
                    ur.typ_equip,
                    COALESCE(co.nom, ar.nom) as nom,
                    ur.depcom,
                    CAST(coalesce(ur.tps_su_smur, 0) as FLOAT) as temps,
                    st_x(ST_Transform(ST_Centroid(
                        CASE 
                            WHEN ur.depcom LIKE '972%' THEN ST_Transform(coalesce(co.wkb_geometry,ar.wkb_geometry), 5489)  -- Martinique
                            WHEN ur.depcom LIKE '974%' THEN ST_Transform(coalesce(co.wkb_geometry,ar.wkb_geometry), 4627)  -- Reunion
                            WHEN ur.depcom LIKE '976%' THEN ST_Transform(coalesce(co.wkb_geometry,ar.wkb_geometry), 4471)  -- Mayotte
                            ELSE coalesce(co.wkb_geometry,ar.wkb_geometry)  -- Metropole
                        END), 4326)) as longitude,
                    st_y(ST_Transform(ST_Centroid(
                        CASE 
                            WHEN ur.depcom LIKE '972%' THEN ST_Transform(coalesce(co.wkb_geometry,ar.wkb_geometry), 5489)  -- Martinique
                            WHEN ur.depcom LIKE '974%' THEN ST_Transform(coalesce(co.wkb_geometry,ar.wkb_geometry), 4627)  -- Reunion
                            WHEN ur.depcom LIKE '976%' THEN ST_Transform(coalesce(co.wkb_geometry,ar.wkb_geometry), 4471)  -- Mayotte
                            ELSE coalesce(co.wkb_geometry,ar.wkb_geometry)  -- Metropole
                        END), 4326)) as latitude
            FROM drees_acces_urgences_2019 ur
            LEFT JOIN ign_commune co ON (co.insee_com=ur.depcom)
            LEFT JOIN ign_arrondissement_municipal ar ON (ar.insee_arm=ur.depcom)
            WHERE ur.depcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')) as y
            WHERE (y.typ_equip IN ('MCS','HeliSMUR','HeliETAT')) OR (y.typ_equip='SU_SMUR' AND y.temps = 0);
SQL Execution time: 0.049009084701538086

            SELECT DISTINCT ON(x.com_ref) x.com_ref, x.nom, x.latitude_ref, x.longitude_ref, x.type_equip_ref
            FROM (
            SELECT *
            FROM drees_acces_urgences_2019 ur
            LEFT JOIN ign_commune co ON (co.insee_com=ur.depcom)
            LEFT JOIN ign_arrondissement_municipal ar ON (ar.insee_arm=ur.depcom)
            WHERE ur.depcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')) as y 
            INNER JOIN ( 
                SELECT ur1.depcom as com_ref, co1.nom, ur1.typ_equip as type_equip_ref,
                CASE WHEN st_intersects(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry) 
                    THEN st_x(ST_Transform(ST_Centroid(
                        CASE 
                            WHEN ur1.depcom LIKE '972%' THEN ST_Transform(st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry), 5489)  -- Martinique
                            WHEN ur1.depcom LIKE '974%' THEN ST_Transform(st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry), 4627)  -- Reunion
                            WHEN ur1.depcom LIKE '976%' THEN ST_Transform(st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry), 4471)  -- Mayotte
                            ELSE st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry)  -- Metropole
                        END), 4326))
                ELSE st_x(ST_Transform(ST_Centroid(
                        CASE 
                            WHEN ur1.depcom LIKE '972%' THEN ST_Transform(coalesce(co1.wkb_geometry,ar1.wkb_geometry), 5489)  -- Martinique
                            WHEN ur1.depcom LIKE '974%' THEN ST_Transform(coalesce(co1.wkb_geometry,ar1.wkb_geometry), 4627)  -- Reunion
                            WHEN ur1.depcom LIKE '976%' THEN ST_Transform(coalesce(co1.wkb_geometry,ar1.wkb_geometry), 4471)  -- Mayotte
                            ELSE coalesce(co1.wkb_geometry,ar1.wkb_geometry)  -- Metropole
                        END), 4326)) END as longitude_ref,
                CASE WHEN st_intersects(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry) 
                    THEN st_y(ST_Transform(ST_Centroid(
                        CASE 
                            WHEN ur1.depcom LIKE '972%' THEN ST_Transform(st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry), 5489)  -- Martinique
                            WHEN ur1.depcom LIKE '974%' THEN ST_Transform(st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry), 4627)  -- Reunion
                            WHEN ur1.depcom LIKE '976%' THEN ST_Transform(st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry), 4471)  -- Mayotte
                            ELSE st_intersection(coalesce(co1.wkb_geometry,ar1.wkb_geometry), c.wkb_geometry)  -- Metropole
                        END), 4326))
                ELSE st_y(ST_Transform(ST_Centroid(
                        CASE 
                            WHEN ur1.depcom LIKE '972%' THEN ST_Transform(coalesce(co1.wkb_geometry,ar1.wkb_geometry), 5489)  -- Martinique
                            WHEN ur1.depcom LIKE '974%' THEN ST_Transform(coalesce(co1.wkb_geometry,ar1.wkb_geometry), 4627)  -- Reunion
                            WHEN ur1.depcom LIKE '976%' THEN ST_Transform(coalesce(co1.wkb_geometry,ar1.wkb_geometry), 4471)  -- Mayotte
                            ELSE coalesce(co1.wkb_geometry,ar1.wkb_geometry)  -- Metropole
                        END), 4326)) END as latitude_ref
                FROM drees_acces_urgences_2019 ur1
                LEFT JOIN  ign_commune co1 ON (co1.insee_com=ur1.depcom)
                LEFT JOIN ign_arrondissement_municipal ar1 ON (ar1.insee_arm=ur1.depcom)
                CROSS JOIN zone_circo c
                WHERE ur1.depcom not in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337') AND c.ref = '030-06') as x
            ON y.com_equip = x.com_ref;
SQL Execution time: 0.045488834381103516

            SELECT y.depcom, y.nom, y.typ_equip, y.temps, y.temps_heli, y.geojson
            FROM (
            SELECT
                    ur.typ_equip,
                    COALESCE(co.nom, ar.nom) as nom,
                    ur.depcom,
                    CAST(coalesce(ur.tps_su_smur, 0) as FLOAT) as temps,
                    CAST(coalesce(ur.tps_total, coalesce(ur.tps_su_smur_mcs, 0)) as FLOAT) as temps_heli,
                    st_asgeojson(coalesce(co.wkb_geometry,ar.wkb_geometry)) as geojson,
                    ur.com_equip
            FROM drees_acces_urgences_2019 AS ur
            LEFT JOIN ign_commune AS co ON (co.insee_com=ur.depcom)
            LEFT JOIN ign_arrondissement_municipal AS ar ON (ar.insee_arm=ur.depcom)
            WHERE ur.depcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')) as y;
SQL Execution time: 0.07556700706481934

                SELECT t.nom_instruction, t.statut_instruction, t.description, t.nom_commune,
                t.action, t.milieu, st_asgeojson(t.geom)
                FROM sites_pollues AS t
                JOIN zone_circo AS c 
                ON st_intersects(c.wkb_geometry, t.geom)
                WHERE c.ref = '030-06'
                
SQL Execution time: 0.0025148391723632812

                SELECT COUNT(*) FROM sites_pollues AS t
                JOIN zone_circo AS c 
                ON st_intersects(c.wkb_geometry, t.geom)
                WHERE c.ref = '030-06';
                
SQL Execution time: 0.0013747215270996094

                SELECT COUNT(*) FROM sites_pollues AS t
                JOIN ign_departement AS dep 
                ON st_intersects(dep.wkb_geometry, t.geom)
                WHERE dep.insee_dep = '30';
                
SQL Execution time: 0.007361412048339844

                SELECT COUNT(*) FROM sites_pollues;
                
SQL Execution time: 0.004153728485107422

            WITH 
            total_surface_circo AS (
                SELECT SUM(st_area(st_intersection(c.wkb_geometry, tp.wkb_geometry)::geography))/10000 as surface_totale_circo_ha
                FROM sdes_occupation_sols_2012_metro_drom as tp
                LEFT JOIN zone_circo c ON st_intersects(c.wkb_geometry, tp.wkb_geometry)
                WHERE c.ref = '030-06'
            ),
            occupation_surfaces_circo AS (
                SELECT 
                    t.code_12 as code_occupation, 
                    MIN(t.libelle_fr) AS nom_occupation,
                    SUM(st_area(st_intersection(c.wkb_geometry,t.wkb_geometry)::geography))/10000 as aire_oc_ha
                FROM sdes_occupation_sols_2012_metro_drom as t
                LEFT JOIN zone_circo c ON st_intersects(c.wkb_geometry, t.wkb_geometry)
                WHERE c.ref = '030-06'
                GROUP BY 1
            )
            SELECT 
                oc.code_occupation as code_occupation,
                clc.nom_occupation as nom_occupation, 
                tc.surface_totale_circo_ha as surface_circo,
                oc.aire_oc_ha as aire_oc_circo_ha,
                oc.aire_oc_ha / tc.surface_totale_circo_ha as part_superficie_circo,
                agg.surface_totale_france_ha,
                agg.aire_oc_ha_france,
                agg.part_superficie_france
            FROM occupation_surfaces_circo oc
            CROSS JOIN total_surface_circo tc
            JOIN sdes_occupation_sols_2012_metro_drom_aggregated agg ON agg.code_occupation = oc.code_occupation
            JOIN sdes_occupation_sols_2012_nomenclature clc ON clc.code_occupation = agg.code_occupation
            ORDER BY clc.nom_occupation;
                
SQL Execution time: 4.332897663116455
SELECT nb_mixte, nb_routiere, nb_ferroviaire, nb_inconnue, nb_activite, nb_habitat, art_totale
                        FROM (
                            SELECT SUM(art11mix12 + art12mix13 + art13mix14 + art14mix15 + art15mix16 + art16mix17 + art17mix18 + art18mix19 + art19mix20 + art20mix21) as nb_mixte,
                        SUM(art11rou12 + art12rou13 + art13rou14 + art14rou15 + art15rou16 + art16rou17 + art17rou18 + art18rou19 + art19rou20 + art20rou21) as nb_routiere,
                        SUM(art11fer12 + art12fer13 + art13fer14 + art14fer15 + art15fer16 + art16fer17 + art17fer18 + art18fer19 + art19fer20 + art20fer21) as nb_ferroviaire,
                        SUM( art11inc12 + art12inc13 + art13inc14 + art14inc15 + art15inc16 + art16inc17 + art17inc18 + art18inc19 + art19inc20 + art20inc21) as nb_inconnue,
                        SUM(art11act12 + art12act13 + art13act14 + art14act15 + art15act16 + art16act17 + art17act18 + art18act19 + art19act20 + art20act21) as nb_activite,
                        SUM(art11hab12 + art12hab13 + art13hab14 + art14hab15 + art15hab16 + art16hab17 + art17hab18 + art18hab19 + art19hab20 + art20hab21) as nb_habitat,
                        SUM(naf11art12 + naf12art13 + naf13art14 + naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20 + naf20art21) as art_totale
                            FROM cerema_artificalisation_2023
                            WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
                        ) as t; 
SQL Execution time: 0.06694865226745605
SELECT nb_mixte, nb_routiere, nb_ferroviaire, nb_inconnue, nb_activite, nb_habitat, art_totale
                        FROM (
                            SELECT SUM(art11mix12 + art12mix13 + art13mix14 + art14mix15 + art15mix16 + art16mix17 + art17mix18 + art18mix19 + art19mix20 + art20mix21) as nb_mixte,
                        SUM(art11rou12 + art12rou13 + art13rou14 + art14rou15 + art15rou16 + art16rou17 + art17rou18 + art18rou19 + art19rou20 + art20rou21) as nb_routiere,
                        SUM(art11fer12 + art12fer13 + art13fer14 + art14fer15 + art15fer16 + art16fer17 + art17fer18 + art18fer19 + art19fer20 + art20fer21) as nb_ferroviaire,
                        SUM( art11inc12 + art12inc13 + art13inc14 + art14inc15 + art15inc16 + art16inc17 + art17inc18 + art18inc19 + art19inc20 + art20inc21) as nb_inconnue,
                        SUM(art11act12 + art12act13 + art13act14 + art14act15 + art15act16 + art16act17 + art17act18 + art18act19 + art19act20 + art20act21) as nb_activite,
                        SUM(art11hab12 + art12hab13 + art13hab14 + art14hab15 + art15hab16 + art16hab17 + art17hab18 + art18hab19 + art19hab20 + art20hab21) as nb_habitat,
                        SUM(naf11art12 + naf12art13 + naf13art14 + naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20 + naf20art21) as art_totale
                            FROM cerema_artificalisation_2023
                            WHERE iddep = '30'
                        ) as t; 
SQL Execution time: 0.07761907577514648
SELECT nb_mixte, nb_routiere, nb_ferroviaire, nb_inconnue, nb_activite, nb_habitat, art_totale
                        FROM (
                            SELECT SUM(art11mix12 + art12mix13 + art13mix14 + art14mix15 + art15mix16 + art16mix17 + art17mix18 + art18mix19 + art19mix20 + art20mix21) as nb_mixte,
                        SUM(art11rou12 + art12rou13 + art13rou14 + art14rou15 + art15rou16 + art16rou17 + art17rou18 + art18rou19 + art19rou20 + art20rou21) as nb_routiere,
                        SUM(art11fer12 + art12fer13 + art13fer14 + art14fer15 + art15fer16 + art16fer17 + art17fer18 + art18fer19 + art19fer20 + art20fer21) as nb_ferroviaire,
                        SUM( art11inc12 + art12inc13 + art13inc14 + art14inc15 + art15inc16 + art16inc17 + art17inc18 + art18inc19 + art19inc20 + art20inc21) as nb_inconnue,
                        SUM(art11act12 + art12act13 + art13act14 + art14act15 + art15act16 + art16act17 + art17act18 + art18act19 + art19act20 + art20act21) as nb_activite,
                        SUM(art11hab12 + art12hab13 + art13hab14 + art14hab15 + art15hab16 + art16hab17 + art17hab18 + art18hab19 + art19hab20 + art20hab21) as nb_habitat,
                        SUM(naf11art12 + naf12art13 + naf13art14 + naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20 + naf20art21) as art_totale
                            FROM cerema_artificalisation_2023
                        ) as t; 
SQL Execution time: 0.10669922828674316

        SELECT
            t.nb_habitat1420 as nb_habitat1420,
            t.naf14art20 as naf14art20,
            t.pop1420 as pop1420,
            t.men1420 as men1420,
            t.emp1420 as emp1420,
            t.artpop1420 as artpop1420,
            t.menhab1420 as menhab1420,
            t.mepart1420 as mepart1420,
            t.mepart1420_calcule as mepart1420_calcule
        FROM (
            SELECT 
                SUM(art14hab15 + art15hab16 + art16hab17 + art17hab18 + art18hab19 + art19hab20) as nb_habitat1420,
                SUM(naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20) as naf14art20,
                SUM(pop1420) as pop1420,
                SUM(men1420) as men1420,
                SUM(emp1420) as emp1420,
                SUM(artpop1420) as artpop1420,
                SUM(menhab1420) as menhab1420,
                SUM(mepart1420) as mepart1420,
                SUM(
                    (men1420 + emp1420) / 
                    NULLIF(
                        ((naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20)/10000), 0
                    )
                ) as mepart1420_calcule
            FROM cerema_artificalisation_2023
                        
                        WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337')
                    ) as t; 
SQL Execution time: 0.09366798400878906

        SELECT
            t.nb_habitat1420 as nb_habitat1420,
            t.naf14art20 as naf14art20,
            t.pop1420 as pop1420,
            t.men1420 as men1420,
            t.emp1420 as emp1420,
            t.artpop1420 as artpop1420,
            t.menhab1420 as menhab1420,
            t.mepart1420 as mepart1420,
            t.mepart1420_calcule as mepart1420_calcule
        FROM (
            SELECT 
                SUM(art14hab15 + art15hab16 + art16hab17 + art17hab18 + art18hab19 + art19hab20) as nb_habitat1420,
                SUM(naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20) as naf14art20,
                SUM(pop1420) as pop1420,
                SUM(men1420) as men1420,
                SUM(emp1420) as emp1420,
                SUM(artpop1420) as artpop1420,
                SUM(menhab1420) as menhab1420,
                SUM(mepart1420) as mepart1420,
                SUM(
                    (men1420 + emp1420) / 
                    NULLIF(
                        ((naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20)/10000), 0
                    )
                ) as mepart1420_calcule
            FROM cerema_artificalisation_2023
                        
                            WHERE iddep = '30'
                        ) as t; 
SQL Execution time: 0.09276723861694336

        SELECT
            t.nb_habitat1420 as nb_habitat1420,
            t.naf14art20 as naf14art20,
            t.pop1420 as pop1420,
            t.men1420 as men1420,
            t.emp1420 as emp1420,
            t.artpop1420 as artpop1420,
            t.menhab1420 as menhab1420,
            t.mepart1420 as mepart1420,
            t.mepart1420_calcule as mepart1420_calcule
        FROM (
            SELECT 
                SUM(art14hab15 + art15hab16 + art16hab17 + art17hab18 + art18hab19 + art19hab20) as nb_habitat1420,
                SUM(naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20) as naf14art20,
                SUM(pop1420) as pop1420,
                SUM(men1420) as men1420,
                SUM(emp1420) as emp1420,
                SUM(artpop1420) as artpop1420,
                SUM(menhab1420) as menhab1420,
                SUM(mepart1420) as mepart1420,
                SUM(
                    (men1420 + emp1420) / 
                    NULLIF(
                        ((naf14art15 + naf15art16 + naf16art17 + naf17art18 + naf18art19 + naf19art20)/10000), 0
                    )
                ) as mepart1420_calcule
            FROM cerema_artificalisation_2023
                        
                        ) as t; 
SQL Execution time: 0.09440469741821289
SELECT  art10mix11, art11mix12, art12mix13, art13mix14, art14mix15, art15mix16, art16mix17, art17mix18, art18mix19, art19mix20, art20mix21, art21mix22, art22mix23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.09133172035217285
SELECT  art10rou11, art11rou12, art12rou13, art13rou14, art14rou15, art15rou16, art16rou17, art17rou18, art18rou19, art19rou20, art20rou21, art21rou22, art22rou23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.0752253532409668
SELECT  art10fer11, art11fer12, art12fer13, art13fer14, art14fer15, art15fer16, art16fer17, art17fer18, art18fer19, art19fer20, art20fer21, art21fer22, art22fer23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.07631921768188477
SELECT  art10inc11, art11inc12, art12inc13, art13inc14, art14inc15, art15inc16, art16inc17, art17inc18, art18inc19, art19inc20, art20inc21, art21inc22, art22inc23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.10503530502319336
SELECT  art10act11, art11act12, art12act13, art13act14, art14act15, art15act16, art16act17, art17act18, art18act19, art19act20, art20act21, art21act22, art22act23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.11305737495422363
SELECT  art10hab11, art11hab12, art12hab13, art13hab14, art14hab15, art15hab16, art16hab17, art17hab18, art18hab19, art19hab20, art20hab21, art21hab22, art22hab23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.10315728187561035
SELECT  art10mix11, art11mix12, art12mix13, art13mix14, art14mix15, art15mix16, art16mix17, art17mix18, art18mix19, art19mix20, art20mix21, art21mix22, art22mix23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.09427881240844727
SELECT  naf10art11 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.08719396591186523
SELECT  naf10art11, naf11art12 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.06572461128234863
SELECT  naf10art11, naf11art12, naf12art13 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.0846710205078125
SELECT  naf10art11, naf11art12, naf12art13, naf13art14 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.08003401756286621
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.09749770164489746
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.07995343208312988
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.06791257858276367
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17, naf17art18 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.07417011260986328
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17, naf17art18, naf18art19 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.07400941848754883
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17, naf17art18, naf18art19, naf19art20 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.06978011131286621
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17, naf17art18, naf18art19, naf19art20, naf20art21 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.06892967224121094
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17, naf17art18, naf18art19, naf19art20, naf20art21, naf21art22 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.06505012512207031
SELECT  naf10art11, naf11art12, naf12art13, naf13art14, naf14art15, naf15art16, naf16art17, naf17art18, naf18art19, naf19art20, naf20art21, naf21art22, naf22art23 FROM cerema_artificalisation_2023 WHERE idcom in ('30001','30014','30039','30041','30057','30067','30110','30145','30155','30156','30174','30189','30206','30211','30257','30262','30286','30295','30299','30301','30308','30319','30334','30337');
SQL Execution time: 0.08135700225830078
get_climat_mens_avg:
SELECT monthly.mois, avg(monthly.temperature) as temperature_circo, avg(monthly.precipitation) as precipitation_circo
        FROM (select m.annee, m.mois,
                avg(m.temperature) as temperature,
                avg(m.precipitation) as precipitation
            from zone_circo c
            join meteo_era5_circo m on (st_intersects(c.wkb_geometry, m.geom))
                where ref = '030-06'
            group by 1,2) as monthly
            group by 1
            order by 1;
            
SELECT sum(effectifs) from educ_etablissements_details
SQL Execution time: 1.551370620727539
SELECT sum(effectifs) from educ_etablissements_details WHERE secteur_public_prive_libe != 'public'
SQL Execution time: 1.5146973133087158
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe IN ('ECOLE DE NIVEAU ELEMENTAIRE', 'ECOLE MATERNELLE')
SQL Execution time: 1.6942980289459229
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe IN ('ECOLE DE NIVEAU ELEMENTAIRE', 'ECOLE MATERNELLE') and secteur_public_prive_libe != 'public'
SQL Execution time: 1.3411228656768799
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe LIKE 'COLLEGE%'
SQL Execution time: 1.3447949886322021
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe LIKE 'COLLEGE%' and secteur_public_prive_libe != 'public'
SQL Execution time: 1.472111463546753
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe LIKE 'LYCEE%'
SQL Execution time: 1.481734275817871
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe LIKE 'LYCEE%' and secteur_public_prive_libe != 'public'
SQL Execution time: 1.5453951358795166
SELECT sum(effectifs) from educ_etablissements_superieur;
SQL Execution time: 0.10055828094482422
SELECT sum(effectifs) from educ_etablissements_superieur WHERE LOWER(secteur_public_prive_libe) != 'public'
SQL Execution time: 0.07247114181518555
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe NOT IN ('ECOLE DE NIVEAU ELEMENTAIRE', 'ECOLE MATERNELLE') AND nature_uai_libe NOT LIKE 'COLLEGE%' AND nature_uai_libe NOT LIKE 'LYCEE%'
SQL Execution time: 1.4899868965148926
SELECT sum(effectifs) from educ_etablissements_details WHERE nature_uai_libe NOT IN ('ECOLE DE NIVEAU ELEMENTAIRE', 'ECOLE MATERNELLE') AND nature_uai_libe NOT LIKE 'COLLEGE%' AND nature_uai_libe NOT LIKE 'LYCEE%'
             AND lower(secteur_public_prive_libe) != 'public'
SQL Execution time: 1.4828884601593018
SELECT avg(ips) from educ_etablissements_details WHERE lower(secteur_public_prive_libe) = 'public' AND ips > 1
SQL Execution time: 1.7202682495117188
SELECT avg(ips) from educ_etablissements_details WHERE lower(secteur_public_prive_libe) != 'public' AND ips > 1
SQL Execution time: 1.641364336013794
SELECT avg(ips) from educ_etablissements_details WHERE ips > 1
SQL Execution time: 1.8069241046905518
SELECT max(ips) from educ_etablissements_details WHERE ips > 1
SQL Execution time: 1.6395237445831299
SELECT min(ips) from educ_etablissements_details WHERE ips > 1
SQL Execution time: 1.7688302993774414
SELECT max(ips) from educ_etablissements_details WHERE lower(secteur_public_prive_libe) != 'public' AND ips > 1
SQL Execution time: 1.519582986831665
SELECT min(ips) from educ_etablissements_details WHERE lower(secteur_public_prive_libe) != 'public' AND ips > 1
SQL Execution time: 1.3501710891723633
SELECT max(ips) from educ_etablissements_details WHERE lower(secteur_public_prive_libe) = 'public' AND ips > 1
SQL Execution time: 1.5971240997314453
SELECT min(ips) from educ_etablissements_details WHERE lower(secteur_public_prive_libe) = 'public' AND ips > 1
SQL Execution time: 1.618238925933838

                SELECT * FROM educ_etablissements_details WHERE circo='030-06'
                
SQL Execution time: 1.01633620262146

                SELECT * FROM educ_etablissements_superieur WHERE circo='030-06'
                
SQL Execution time: 0.015280485153198242
select count(*), sum(p.i_est_1km::numeric)
            from zone_circo c
                right join insee_population_carroyee_2019 p on (st_intersects(c.wkb_geometry, p.wkb_geometry))
            where c.ref='030-06';
SQL Execution time: 21.876461267471313
select st_asgeojson(
                        st_Transform(
                            CASE 
                                WHEN '030-06' LIKE '972-%' THEN ST_Transform(p.wkb_geometry, 5489)  -- Martinique
                                WHEN '030-06' LIKE '974-%' THEN ST_Transform(p.wkb_geometry, 4627)  -- Reunion
                                WHEN '030-06' LIKE '976-%' THEN ST_Transform(p.wkb_geometry, 4471)  -- Mayotte
                                ELSE p.wkb_geometry  -- Metropole et autres (deja en Lambert-93 ou projection appropriee)
                            END, 4326)
                    ) as geometry,
                round(100*(p.men_pauv::numeric)/p.men::numeric,2)::numeric as pct_men_pauv,
                ((p.ind_snv::numeric)/p.ind::numeric)::numeric as niveau_vie_moyen
                from zone_circo c
                    right join insee_population_carroyee_2019_1k p on (st_intersects(c.wkb_geometry, p.wkb_geometry))
                where c.ref='030-06';
SQL Execution time: 0.9418439865112305
 SELECT tx_pauvrete60_diff::numeric, d1_diff::numeric, nivvie_median_diff::numeric, d9_diff::numeric
                FROM insee_indic_stat_circo_2022 WHERE circo in ('030-06', '000-00')
                ORDER BY CASE circo
                WHEN '030-06' THEN 1
                WHEN '000-00' THEN 2
                END;
SQL Execution time: 0.05226278305053711
SELECT tp6019, d119, med19, d919
                FROM insee_revenu_dep_2019
                WHERE codgeo = '30';
SQL Execution time: 0.04956698417663574
 SELECT pact::numeric, ppen::numeric, ppat::numeric, ppsoc::numeric, pimpot::numeric
                FROM insee_indic_stat_circo_2022 WHERE circo in ('030-06', '000-00')
                ORDER BY CASE circo
                WHEN '030-06' THEN 1
                WHEN '000-00' THEN 2
                END;
SQL Execution time: 0.06678199768066406
SELECT pact19::numeric, ppen19::numeric, ppat19::numeric, ppsoc19::numeric, pimpot19::numeric
                FROM insee_revenu_dep_2019
                WHERE codgeo = '30';
SQL Execution time: 0.05237841606140137
 SELECT tx_pauvrete60_diff_tragerf1::numeric, tx_pauvrete60_diff_tragerf2::numeric, tx_pauvrete60_diff_tragerf3::numeric,
                tx_pauvrete60_diff_tragerf4::numeric, tx_pauvrete60_diff_tragerf5::numeric, tx_pauvrete60_diff_tragerf6::numeric
                FROM insee_indic_stat_circo_2022 WHERE circo in ('030-06', '000-00')
                ORDER BY CASE circo
                WHEN '030-06' THEN 1
                WHEN '000-00' THEN 2
                END;
SQL Execution time: 0.04616832733154297
SELECT tp60age119::numeric, tp60age219::numeric, tp60age319::numeric, tp60age419::numeric, tp60age519::numeric, tp60age619::numeric
                FROM insee_revenu_dep_2019
                WHERE codgeo = '30';
SQL Execution time: 0.062249183654785156
WITH somme_totale AS (
                            SELECT SUM(st_area(st_intersection(iris2.wkb_geometry, circo2.wkb_geometry)::geography)*popi2.p21_pop::numeric) as pond_revenu
                            FROM insee_pop_2021 as popi2
                            JOIN insee_revenu_pauvrete_2021 as reveni2 ON popi2.iris = reveni2.iris
                            JOIN iris_2021 AS iris2 ON (iris2.dcomiris || iris2.iris) = popi2.iris
                            JOIN zone_circo AS circo2 ON st_intersects(iris2.wkb_geometry, circo2.wkb_geometry)
                            WHERE circo2.ref = '030-06'
                            AND popi2.p21_pop IS NOT NULL
                            AND reveni2.DISP_D121 IS NOT NULL
                            AND reveni2.DISP_D121 NOT IN ('ns','nd'))    
            SELECT SUM(cast(t.DISP_D121 as float)*pop*surface/pond_revenu),
            SUM(cast(t.DISP_D221 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_D321 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_D421 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_MED21 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_D621 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_D721 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_D821 as float)*t.pop*t.surface/pond_revenu),
            SUM(cast(t.DISP_D921 as float)*t.pop*t.surface/pond_revenu)
                from somme_totale, (
                    SELECT reveni.*, st_area(st_intersection(iris.wkb_geometry, circo.wkb_geometry)::geography) as surface, popi.p21_pop::numeric as pop
                    FROM insee_pop_2021 as popi
                    JOIN insee_revenu_pauvrete_2021 as reveni ON popi.iris = reveni.iris
                    JOIN iris_2021 AS iris ON (iris.dcomiris || iris.iris) = popi.iris
                    JOIN zone_circo AS circo ON st_intersects(iris.wkb_geometry, circo.wkb_geometry)
                    WHERE circo.ref = '030-06'
                    AND popi.p21_pop IS NOT NULL
                    AND reveni.DISP_D121 IS NOT NULL
                    AND reveni.DISP_D121 NOT IN ('ns','nd')) as t;
SQL Execution time: 3.378185987472534
