-- ****************************************************************************
-- assouplissement d'une contrainte de clé étrangère
-- ****************************************************************************

-- En échec : DELETE FROM emploi WHERE id = 6; -- ERREUR:  UPDATE ou DELETE sur la table « emploi » viole la contrainte de clé étrangère « fk_poste_emploi » de la table « poste »
SELECT * FROM poste WHERE id_emploi = 6;

ALTER TABLE poste DROP CONSTRAINT IF EXISTS fk_poste_emploi;
ALTER TABLE poste ADD CONSTRAINT fk_poste_emploi FOREIGN KEY (id_emploi) REFERENCES emploi (id) ON DELETE SET NULL;

SELECT * FROM poste WHERE id_emploi = 6;
DELETE FROM emploi WHERE id = 6;
SELECT * FROM poste WHERE id_emploi = 6;

-- suppression d'une contrainte de clé étrangère
ALTER TABLE poste DROP CONSTRAINT IF EXISTS fk_poste_emploi;

DELETE FROM emploi WHERE id = 7;
SELECT * FROM poste WHERE id_emploi = 7;

-- recherche des incohérences de référence (syntaxe en NOT IN)
SELECT *
FROM poste p
WHERE id_emploi IS NULL
OR id_emploi NOT IN (SELECT id FROM emploi);

-- recherche des incohérences de référence (syntaxe en LEFT JOIN)
SELECT p.*
FROM poste p
LEFT JOIN emploi e ON p.id_emploi = e.id
WHERE e IS NULL;

-- ****************************************************************************
-- cohérence logique
-- ****************************************************************************

-- recherche des agents dont le genre est non-défini ou différent de F et M (syntaxe en NOT IN)
SELECT *
FROM agent
WHERE genre IS NULL
OR genre NOT IN ('F', 'M');

-- recherche des agents dont le genre est non-défini ou différent de F et M (syntaxe en <> ALL)
SELECT *
FROM agent
WHERE genre IS NULL
OR genre <> ALL (ARRAY['F', 'M']);

-- recheche des agents dont le format d'adresse e-mail est invalide
SELECT *
FROM agent
WHERE email !~ '(?:[a-z0-9!#$%&\''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])';
-- ' <-- pour contourner le problème de coloration syntaxique sur Notepad++

-- calcul de taux de non-conformité des adresses e-mail (syntaxe en SELECT SELECT)
-- taux de non conformité = nb invalides / nb total
SELECT
    (
        SELECT count(*)
        FROM agent
        WHERE email !~ '(?:[a-z0-9!#$%&\''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'
        -- ' <-- pour contourner le problème de coloration syntaxique sur Notepad++
    ) AS nb_invalide,
    (
        SELECT count(*)
        FROM agent
    ) AS nb_total,
    (
        SELECT count(*)::double precision
        FROM agent
        WHERE email !~ '(?:[a-z0-9!#$%&\''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'
    -- ' <-- pour contourner le problème de coloration syntaxique sur Notepad++
    )
    /
    (
        SELECT count(*)::double precision
        FROM agent
    ) AS taux_non_conformite;
    
-- calcul de taux de non-conformité des adresses e-mail (syntaxe en SELECT FROM)
-- taux de conformité = nb valides / nb total
SELECT valide.nb, total.nb, valide.nb::double precision / total.nb AS taux_conformite
FROM
    (
        SELECT count(*) AS nb
        FROM agent
        WHERE email ~ '(?:[a-z0-9!#$%&\''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'
        -- ' <-- pour contourner le problème de coloration syntaxique sur Notepad++
    ) AS valide,
    (
        SELECT count(*) AS nb
        FROM agent
    ) AS total;
    
-- calcul pour le genre des agents du pourcentage de conformité, non-conformité et non-défini (syntaxe avec CTE et GROUP BY)
WITH ventilation AS (
    SELECT genre IN ('M', 'F') AS saisie_valide, count(*) AS nb
    FROM agent
    GROUP BY 1
),
total AS (
    SELECT count(*) AS nb
    FROM agent
)
SELECT v.saisie_valide, v.nb, t.nb AS total, round(v.nb::numeric * 100 / t.nb, 2) AS pourcentage
FROM ventilation v, total t
ORDER BY saisie_valide NULLS LAST;

-- ****************************************************************************
-- statistiques basiques
-- ****************************************************************************

-- calcul de l'age moyen des agents
SELECT avg(age(now(), date_naissance))
FROM agent;

-- calcul de l'age moyen des agents en nombre d'années uniquement
SELECT avg(extract(YEAR FROM age(now(), date_naissance)))
FROM agent;

-- calcul de la variance des ages des agents (population entière)
SELECT var_pop(extract(YEAR FROM age(now(), date_naissance)))
FROM agent;

-- calcul de l'écart-type des ages des agents (population entière)
SELECT stddev_pop(extract(YEAR FROM age(now(), date_naissance)))
FROM agent;

-- calcul de la variance des ages des agents (échantillon de la population)
WITH echantillon AS (
    SELECT *
    FROM agent
    LIMIT 100
)
SELECT var_samp(extract(YEAR FROM age(now(), date_naissance)))
FROM echantillon;

-- calcul de l'écart-type des ages des agents (échantillon de la population)
WITH echantillon AS (
    SELECT *
    FROM agent
    LIMIT 100
)
SELECT stddev_samp(extract(YEAR FROM age(now(), date_naissance)))
FROM echantillon;

-- calcul de l'étendue des ages des agents
SELECT
    max(age(now(), date_naissance)) AS max,
    min(age(now(), date_naissance)) AS min,
    max(age(now(), date_naissance)) - min(age(now(), date_naissance)) AS etendue
FROM agent;

-- identification des agents à plus de 2 fois l'écart type
WITH moyenne AS (
    SELECT avg(extract(YEAR FROM age(now(), date_naissance))) AS valeur
    FROM agent
),
ecarttype AS (
    SELECT stddev_pop(extract(YEAR FROM age(now(), date_naissance))) AS valeur
    FROM agent
)
SELECT a.email, a.date_naissance, age(now(), date_naissance) AS age
FROM agent a
CROSS JOIN moyenne m
CROSS JOIN ecarttype et
WHERE extract(YEAR FROM age(now(), date_naissance)) NOT BETWEEN m.valeur - et.valeur * 2 AND m.valeur + et.valeur * 2
ORDER BY age;

-- calcul du jour du mois moyen de naissance des agents
SELECT avg(extract(DAY FROM date_naissance))
FROM agent;

-- calcul du jour du mois médian de naissance des agents
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY extract(DAY FROM date_naissance)) AS jour_median
FROM agent;

-- calcul des déciles de jour du mois de naissance des agents
SELECT unnest(PERCENTILE_CONT(ARRAY[0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9]) WITHIN GROUP(ORDER BY extract(DAY FROM date_naissance)))
FROM agent;

-- association de chaque agent à son numéro de décile
SELECT
    email,
    extract(DAY FROM date_naissance) as jour_de_naissance,
    ntile(10) over(ORDER BY extract(DAY FROM date_naissance)) AS decile
FROM agent
ORDER BY decile DESC, jour_de_naissance ASC;

-- calcul du nombre d'agents par jour du mois de naissance
SELECT extract(DAY FROM date_naissance) AS jour_naissance, count(*)
FROM agent
GROUP BY 1
ORDER BY 1;

-- ****************************************************************************
-- exhaustivité
-- ****************************************************************************

-- recherche des excédents vis-à-vis du référentiel
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30183'
)
SELECT jeu.*
FROM jeu_a_controler jeu
LEFT JOIN ref_adr_moulezan referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune
-- les adresses du jeu qui n'ont pas de correspondance dans le référentiel (LEFT JOIN / IS NULL) sont en excédent
WHERE referentiel IS NULL;

-- recherche des omissions vis-à-vis du référentiel
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30183'
)
SELECT referentiel.*
FROM jeu_a_controler jeu
RIGHT JOIN ref_adr_moulezan referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune
-- les adresses du référentiel qui n'ont pas de correspondance dans le jeu (RIGHT JOIN / IS NULL) sont en omission
WHERE jeu IS NULL;

-- calcul du taux d'exhaustivité = 1 - (nb_excédents + nb_ommissions) / nb_référentiel
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30183'
),
comparaison AS (
    SELECT
        CASE
            WHEN referentiel IS NULL THEN 'excédent' -- les adresses du jeu qui n'ont pas de correspondance dans le référentiel sont en excédent
            WHEN jeu IS NULL THEN 'ommission' -- les adresses du référentiel qui n'ont pas de correspondance dans le jeu sont en omission
            ELSE 'ok' -- sinon ok
        END AS statut,
        *
    FROM jeu_a_controler jeu
    FULL JOIN ref_adr_moulezan referentiel
    -- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
    ON referentiel.numero = jeu.numero
    AND referentiel.repetition = jeu.repetition
    AND referentiel.voie = jeu.voie
    AND referentiel.cog_commune = jeu.cog_commune
)
SELECT
    nb_excédents.valeur AS "Nombre d'excédents",
    nb_ommissions.valeur AS "Nombre d'ommissions",
    nb_référentiel.valeur AS "Taille du référentiel",
    1 - (nb_excédents.valeur + nb_ommissions.valeur)::numeric / nb_référentiel.valeur AS "Taux d'exhaustivité"
FROM (SELECT count(*) AS valeur FROM ref_adr_moulezan) nb_référentiel
CROSS JOIN (SELECT count(*) AS valeur FROM comparaison WHERE statut = 'excédent') AS nb_excédents
CROSS JOIN (SELECT count(*) AS valeur FROM comparaison WHERE statut = 'ommission') AS nb_ommissions;

-- ****************************************************************************
-- précision de position (relative et absolue)
-- ****************************************************************************

-- calcul de la moyenne et de l'écart type des distances entre le jeu de données et le référentiel (commune Montagnac)
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30354'
)
SELECT
    avg(ST_Distance(referentiel.geom, jeu.geom)) AS moyenne_dist,
    stddev_samp(ST_Distance(referentiel.geom, jeu.geom)) AS ecarttype_dist
FROM jeu_a_controler jeu
INNER JOIN ref_adr_montagnac referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune;

-- création des vecteurs jeu de données --> référentiel (commune Montagnac)
DROP TABLE IF EXISTS vecteur_montagnac;
CREATE TABLE vecteur_montagnac AS
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30354'
)
SELECT
    ST_MakeLine(jeu.geom, referentiel.geom) AS geom 
FROM jeu_a_controler jeu
INNER JOIN ref_adr_montagnac referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune;

-- calcul des statistiques des distances algébriques sur X et Y entre le jeu de données et le référentiel (commune Montagnac)
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30354'
)
SELECT
    avg(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS moyenne_dist_algebrique_X,
    stddev_samp(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS ecarttype_dist_algebrique_X,
    max(ST_X(referentiel.geom) - ST_X(jeu.geom)) - min(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS etendue_dist_algebrique_X,
    
    avg(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS moyenne_dist_algebrique_Y,
    stddev_samp(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS ecarttype_dist_algebrique_Y,
    max(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) - min(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS etendue_dist_algebrique_Y
    
FROM jeu_a_controler jeu
INNER JOIN ref_adr_montagnac referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune;

-- calcul des statistiques des distances algébriques sur X et Y entre le jeu de données et le référentiel (commune Mauressargues)
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30163'
)
SELECT
    avg(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS moyenne_dist_algebrique_X,
    stddev_samp(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS ecarttype_dist_algebrique_X,
    max(ST_X(referentiel.geom) - ST_X(jeu.geom)) - min(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS etendue_dist_algebrique_X,
    
    avg(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS moyenne_dist_algebrique_Y,
    stddev_samp(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS ecarttype_dist_algebrique_Y,
    max(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) - min(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS etendue_dist_algebrique_Y
    
FROM jeu_a_controler jeu
INNER JOIN ref_adr_mauressargues referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune;

-- création des vecteurs jeu de données --> référentiel (commune Mauressargues)
DROP TABLE IF EXISTS vecteur_mauressargues;
CREATE TABLE vecteur_mauressargues AS
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30163'
)
SELECT
    ST_MakeLine(jeu.geom, referentiel.geom) AS geom 
FROM jeu_a_controler jeu
INNER JOIN ref_adr_mauressargues referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune;

-- calcul de la moyenne et de l'écart-type des distances et des azimuts entre le jeu de données et le référentiel (commune Mauressargues)
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30163'
)
SELECT
    avg(ST_Distance(referentiel.geom, jeu.geom)) AS moyenne_dist,
    stddev_samp(ST_Distance(referentiel.geom, jeu.geom)) AS ecarttype_dist,
    
    avg(degrees(ST_Azimuth(referentiel.geom, jeu.geom))) AS moyenne_azimut,
    stddev_samp(degrees(ST_Azimuth(referentiel.geom, jeu.geom))) AS ecarttype_azimut
    
FROM jeu_a_controler jeu
INNER JOIN ref_adr_mauressargues referentiel
-- jointure sur les attributs qui constituent l'adresse (numéro + répétition + voie + code INSEE de la commune)
ON referentiel.numero = jeu.numero
AND referentiel.repetition = jeu.repetition
AND referentiel.voie = jeu.voie
AND referentiel.cog_commune = jeu.cog_commune;

-- recalage sur les moyennes des distances algébriques en X et Y
WITH jeu_a_controler AS (
    SELECT *
    FROM adresse
    WHERE cog_commune = '30163'
),
moyennes_algebrique AS (
    SELECT
        avg(ST_X(referentiel.geom) - ST_X(jeu.geom)) AS X,
        avg(ST_Y(referentiel.geom) - ST_Y(jeu.geom)) AS Y
    FROM jeu_a_controler jeu
    INNER JOIN ref_adr_mauressargues referentiel
    ON referentiel.numero = jeu.numero
    AND referentiel.repetition = jeu.repetition
    AND referentiel.voie = jeu.voie
    AND referentiel.cog_commune = jeu.cog_commune
)
UPDATE adresse
SET geom = ST_Translate(geom, X, Y)
FROM moyennes_algebrique
WHERE cog_commune = '30163';

-- ****************************************************************************
-- échantillonnage
-- ****************************************************************************

-- création d'un échantillon simple aléatoire de 100 adresses
DROP TABLE IF EXISTS echantillon_1_1_simple_aleatoire;
CREATE TABLE echantillon_1_1_simple_aleatoire AS
SELECT *
FROM adresse
ORDER BY random()
LIMIT 100;

-- création d'un échantillon simple aléatoire de 1% des adresses
DROP TABLE IF EXISTS echantillon_1_2_simple_aleatoire;
CREATE TABLE echantillon_1_2_simple_aleatoire AS
SELECT *
FROM adresse TABLESAMPLE BERNOULLI (1);

-- création d'un échantillon orienté objet des adresses associé à une place
DROP TABLE IF EXISTS echantillon_2_oriente_objet;
CREATE TABLE echantillon_2_oriente_objet AS
SELECT *
FROM adresse
WHERE voie ILIKE 'place %';

-- création d'un échantillon orienté surface des adresses sur la commune de Saint-Dionisy
DROP TABLE IF EXISTS echantillon_3_oriente_surface;
CREATE TABLE echantillon_3_oriente_surface AS
SELECT a.*
FROM adresse a
INNER JOIN commune c
ON c.nom = 'Saint-Dionisy' and ST_Intersects(a.Geom, c.Geom);

-- création d'un échantillon aléatoire stratifié de 5 adresses par commune
DROP TABLE IF EXISTS echantillon_4_aleatoire_stratifie;
CREATE TABLE echantillon_4_aleatoire_stratifie AS
SELECT a.*
FROM commune c
CROSS JOIN LATERAL (
    SELECT *
    FROM adresse
    WHERE cog_commune = c.cog
    ORDER BY random()
    LIMIT 5
) a;

-- création d'un échantillon semi-aléatoire sur la base d'une grille rectangulaire
DROP TABLE IF EXISTS echantillon_5_semi_aleatoire_rectangle;
CREATE TABLE echantillon_5_semi_aleatoire_rectangle AS
WITH etendue AS (
    SELECT ST_Collect(geom) AS geom
    FROM commune
    WHERE cog_departement = '30'
),
grille AS (
    SELECT g.*
    FROM etendue e
    CROSS JOIN ST_SquareGrid(1000, geom) g
),
infos_cellules AS (
    SELECT min(i) AS i_min, max(i) AS i_max, min(j) j_min, max(j) AS j_max
    FROM grille
),
selection_cellule AS (
    SELECT g.*
    FROM grille g, infos_cellules
    WHERE (
        (j - j_min)           -- numéro_ligne
        * (i_max - i_min + 1) -- * taille_ligne
        + (i - i_min)         -- + numéro_colonne
    ) % 11 = 0                -- module 11 = 0
)
SELECT a.*
FROM adresse a
INNER JOIN selection_cellule sc ON ST_Intersects(a.geom, sc.geom);

-- grille rectangle d'échantillonage
DROP TABLE IF EXISTS selection_grille_rectangle;
CREATE TABLE selection_grille_rectangle AS
WITH etendue AS (
    SELECT ST_Collect(geom) AS geom
    FROM commune
    WHERE cog_departement = '30'
),
grille AS (
    SELECT g.*
    FROM etendue e
    CROSS JOIN ST_SquareGrid(1000, geom) g
),
infos_cellules AS (
    SELECT min(i) AS i_min, max(i) AS i_max, min(j) j_min, max(j) AS j_max
    FROM grille
)
SELECT g.*
FROM grille g, infos_cellules
WHERE (
    (j - j_min)           -- numéro_ligne
    * (i_max - i_min + 1) -- * taille_ligne
    + (i - i_min)         -- + numéro_colonne
) % 11 = 0;               -- module 11 = 0

-- création d'un échantillon semi-aléatoire sur la base d'une grille hexagonale
DROP TABLE IF EXISTS echantillon_6_semi_aleatoire_hexagone;
CREATE TABLE echantillon_6_semi_aleatoire_hexagone AS
WITH etendue AS (
    SELECT ST_Collect(geom) AS geom
    FROM commune
    WHERE cog_departement = '30'
),
grille AS (
    SELECT g.*
    FROM etendue e
    CROSS JOIN ST_HexagonGrid(1000, geom) g
),
infos_cellules AS (
    SELECT min(i) AS i_min, max(i) AS i_max, min(j) j_min, max(j) AS j_max
    FROM grille
),
selection_cellule AS (
    SELECT g.*
    FROM grille g, infos_cellules
    WHERE (
        (j - j_min)           -- numéro_ligne
        * (i_max - i_min + 1) -- * taille_ligne
        + (i - i_min)         -- + numéro_colonne
    ) % 11 = 0                -- module 11 = 0
)
SELECT a.*
FROM adresse a
INNER JOIN selection_cellule sc ON ST_Intersects(a.geom, sc.geom);

-- grille hexagonale d'échantillonage
DROP TABLE IF EXISTS selection_grille_hexagone;
CREATE TABLE selection_grille_hexagone AS
WITH etendue AS (
    SELECT ST_Collect(geom) AS geom
    FROM commune
    WHERE cog_departement = '30'
),
grille AS (
    SELECT g.*
    FROM etendue e
    CROSS JOIN ST_HexagonGrid(1000, geom) g
),
infos_cellules AS (
    SELECT min(i) AS i_min, max(i) AS i_max, min(j) j_min, max(j) AS j_max
    FROM grille
)
SELECT g.*
FROM grille g, infos_cellules
WHERE (
    (j - j_min)           -- numéro_ligne
    * (i_max - i_min + 1) -- * taille_ligne
    + (i - i_min)         -- + numéro_colonne
) % 11 = 0;               -- module 11 = 0