Carnet des commandes itératives

C’est une excellente initiative. Pour Nexte_Stats, disposer d'un "Playbook" SQL permet de reproduire cette précision chirurgicale sur n'importe quelle autre commune (Sanary, Six-Fours, etc.) en quelques minutes.

Voici la synthèse technique de notre workflow "Bandol", nettoyée des erreurs de syntaxe et optimisée pour ton architecture PostGIS.

📑 Playbook : Extraction & Nettoyage OSM (Référentiel Nexte_Stats)

Ce script part du principe que tes données brutes sont dans le schéma osm_raw (SRID 4326 ou 3857) et que ta commune cible est définie par sa table de parcelles dans le schéma 83009_bandol (SRID 2154).

1. Préparation de l'emprise (La "Découpe")

Avant d'extraire, on définit la forme exacte de la commune pour éviter les "gros carrés" (Bounding Box).

-- On utilise l'Union des parcelles comme emporte-pièce universel
-- On la stocke mentalement comme : (SELECT ST_Union(geom) FROM "83009_bandol".bandol_parcelles)

2. Couche Bâtiments (Buildings)

Extraction des emprises bâties, transformation en Lambert-93 et nettoyage des géométries aberrantes.

DROP TABLE IF EXISTS "83009_bandol".osm_buildings;

CREATE TABLE "83009_bandol".osm_buildings AS
SELECT b.osm_id, b.name, b.building, b.tags,
       ST_Multi(ST_Transform(b.way, 2154)) as geom
FROM osm_raw.buildings b
WHERE ST_Intersects(
    b.way, 
    (SELECT ST_Transform(ST_Union(geom), 3857) FROM "83009_bandol".bandol_parcelles)
);

-- Nettoyage des artefacts et indexation
DELETE FROM "83009_bandol".osm_buildings WHERE ST_Area(geom) > 50000;
CREATE INDEX idx_osm_buildings_geom ON "83009_bandol".osm_buildings USING GIST(geom);

3. Couche Voirie (Roads / Ways)

Découpage (ST_Intersection) pour que les routes s'arrêtent pile à la frontière communale.

DROP TABLE IF EXISTS "83009_bandol".osm_roads;

CREATE TABLE "83009_bandol".osm_roads AS
SELECT r.osm_id, r.name, r.highway, r.ref, r.oneway, r.surface, r.z_order,
       ST_Multi(ST_Intersection(
           ST_Transform(r.way, 2154), 
           (SELECT ST_Union(geom) FROM "83009_bandol".bandol_parcelles)
       )) as geom
FROM osm_raw.roads r
WHERE ST_Intersects(
    r.way, 
    (SELECT ST_Transform(ST_Union(geom), 3857) FROM "83009_bandol".bandol_parcelles)
);

CREATE INDEX idx_osm_roads_geom ON "83009_bandol".osm_roads USING GIST(geom);

4. Couche Occupation du Sol (Landuse)

Gestion spécifique du format Geofabrik (fclass) et conversion Lambert-93.

DROP TABLE IF EXISTS "83009_bandol".osm_landuse;

CREATE TABLE "83009_bandol".osm_landuse AS 
SELECT l.osm_id, l.name, l.fclass as landuse_type, 
       ST_Multi(ST_Intersection(
           ST_Transform(l.geom, 2154), 
           (SELECT ST_Union(geom) FROM "83009_bandol".bandol_parcelles)
       )) as geom
FROM osm_raw.landuse l
WHERE ST_Intersects(
    ST_Transform(l.geom, 2154), 
    (SELECT ST_Union(geom) FROM "83009_bandol".bandol_parcelles)
);

CREATE INDEX idx_osm_landuse_geom ON "83009_bandol".osm_landuse USING GIST(geom);

5. Couche Équipements Publics (Amenities - La "Hiérarchie")

Fusion des points et des centroïdes de bâtiments pour créer un référentiel de services unique.

DROP TABLE IF EXISTS "83009_bandol"."83009_equipements_publics";

-- Étape A : Import des points natifs
CREATE TABLE "83009_bandol"."83009_equipements_publics" AS
SELECT osm_id, name, amenity, tags, ST_Transform(way, 2154) as geom
FROM osm_raw.points
WHERE ST_Intersects(way, (SELECT ST_Transform(ST_Union(geom), 3857) FROM "83009_bandol".bandol_parcelles))
AND (amenity IN ('school','townhall','police','hospital','library','post_office','community_centre'));

-- Étape B : Ajout des centroïdes des bâtiments (Mairie, Gymnases, etc.)
INSERT INTO "83009_bandol"."83009_equipements_publics" (osm_id, name, amenity, geom)
SELECT osm_id, name, COALESCE(amenity, tags->'leisure', tags->'building'), ST_Centroid(geom)
FROM "83009_bandol".osm_buildings
WHERE amenity IN ('school','townhall','hospital')
   OR tags->'leisure' IN ('sports_centre', 'stadium')
   OR tags->'building' IN ('public', 'train_station');

CREATE INDEX idx_equip_pub_geom ON "83009_bandol"."83009_equipements_publics" USING GIST(geom);

💡 Pourquoi ce référentiel est "pro" ?

1. SRID Unique (2154) : Plus de bugs d'affichage ou de calculs en degrés. Tout est en mètres.

2. ST_Intersection vs ST_Intersects : On a fait la distinction entre "ce qui touche" (bâtiments) et "ce qui est coupé" (routes/landuse).

3. Performance : Les index GIST garantissent que tes cartes QGIS s'affichent instantanément, même avec 10 000 bâtiments.

Une question me taraude maintenant que tout est prêt : sur quel type d'analyse veux-tu que l'on se concentre en premier ? Est-ce le "taux de vacance théorique" (Bâtiments sans ventes DVF) ou la "pression foncière sur les vignobles" ?