Get area stats for parcelles and batiments for each parcelle

stats-surface-91114.sql

INSTALL SPATIAL;
LOAD SPATIAL;
SET memory_limit = '16GB';
SET max_temp_directory_size = '125GB';
COPY (WITH batiments_91114 AS (
  SELECT *
  FROM
  read_parquet('https://cadastre.data.gouv.fr/data/etalab-cadastre/2025-09-01/geoparquet/france/cadastre.parquet')
  WHERE type_objet = 'batiments' AND departement = '91' AND commune = '91114' AND ST_GeometryType(geometry) IN ('POLYGON', 'MULTIPOLYGON')
),
parcelles_91114 AS (
  SELECT *
  FROM
  read_parquet('https://cadastre.data.gouv.fr/data/etalab-cadastre/2025-09-01/geoparquet/france/cadastre.parquet')
  WHERE type_objet = 'parcelles' AND departement = '91' AND commune = '91114' AND ST_GeometryType(geometry) IN ('POLYGON', 'MULTIPOLYGON')
),
intersection_parcelles_batiments AS (
  SELECT parcelles_91114.departement, parcelles_91114.commune, parcelles_91114.type_objet, parcelles_91114.id, parcelles_91114.section, parcelles_91114.parcelle, parcelles_91114.numero, parcelles_91114.prefixe, parcelles_91114.code, parcelles_91114.lettre, parcelles_91114.nom, parcelles_91114.created, parcelles_91114.updated, parcelles_91114.qualite, parcelles_91114.modeConfec, parcelles_91114.echelle, parcelles_91114.ancienne, parcelles_91114.type, parcelles_91114.contenance, ST_Area(ST_intersection(batiments_91114.geometry, parcelles_91114.geometry)) As surface_bati, ST_intersection(batiments_91114.geometry, parcelles_91114.geometry) AS geometry
  FROM parcelles_91114, batiments_91114
  WHERE ST_intersects(batiments_91114.geometry, parcelles_91114.geometry) AND ST_GeometryType(ST_intersection(batiments_91114.geometry, parcelles_91114.geometry)) IN ('POLYGON', 'MULTIPOLYGON')
),
grouped_stats_by_parcelles AS (
  SELECT departement, commune, type_objet, id, section, parcelle, numero, prefixe, code, lettre, nom, created, updated, qualite, modeConfec, echelle, ancienne, type, contenance, SUM(surface_bati) AS total_bati
  FROM intersection_parcelles_batiments
  GROUP BY departement, commune, type_objet, id, section, parcelle, numero, prefixe, code, lettre, nom, created, updated, qualite, modeConfec, echelle, ancienne, type, contenance
)
SELECT parcelles_91114.departement, parcelles_91114.commune, parcelles_91114.type_objet, parcelles_91114.id, parcelles_91114.section, parcelles_91114.parcelle, parcelles_91114.numero, parcelles_91114.prefixe, parcelles_91114.code, parcelles_91114.lettre, parcelles_91114.nom, parcelles_91114.created, parcelles_91114.updated, parcelles_91114.qualite, parcelles_91114.modeConfec, parcelles_91114.echelle, parcelles_91114.ancienne, parcelles_91114.type, parcelles_91114.contenance, CASE WHEN total_bati is NULL THEN 0 ELSE total_bati END AS total_bati
FROM parcelles_91114
LEFT JOIN grouped_stats_by_parcelles ON parcelles_91114.id = grouped_stats_by_parcelles.id
)  TO '/tmp/stats_parcelles_batiments_91114.csv';

/*COPY (WITH batiments_91114 AS (
  SELECT *
  FROM
  read_parquet('https://cadastre.data.gouv.fr/data/etalab-cadastre/2025-09-01/geoparquet/france/cadastre.parquet')
  WHERE type_objet = 'batiments' AND departement = '91' AND commune = '91114'
)
SELECT * FROM batiments_91114
)  TO 'batiments_91114.parquet' (FORMAT parquet, COMPRESSION zstd);

COPY (WITH parcelles_91114 AS (
  SELECT *
  FROM
  read_parquet('https://cadastre.data.gouv.fr/data/etalab-cadastre/2025-09-01/geoparquet/france/cadastre.parquet')
  WHERE type_objet = 'parcelles' AND departement = '91' AND commune = '91114'
)
SELECT * FROM parcelles_91114
)  TO 'parcelles_91114.parquet' (FORMAT parquet, COMPRESSION zstd);
*/