# 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).

<div class="code-block ng-tns-c2968650486-2138 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-sql"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c2968650486-2138 ng-star-inserted"><span class="ng-tns-c2968650486-2138">SQL</span><div class="buttons ng-tns-c2968650486-2138 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c2968650486-2138"><div class="animated-opacity ng-tns-c2968650486-2138"></div></div></div>```
-- 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)

```

<div class="code-block ng-tns-c2968650486-2138 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--2"><div class="formatted-code-block-internal-container ng-tns-c2968650486-2138"><div class="animated-opacity ng-tns-c2968650486-2138"></div></div></div>---

### 2. Couche Bâtiments (Buildings)

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

<div class="code-block ng-tns-c2968650486-2139 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-sql-1"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c2968650486-2139 ng-star-inserted"><span class="ng-tns-c2968650486-2139">SQL</span><div class="buttons ng-tns-c2968650486-2139 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c2968650486-2139"><div class="animated-opacity ng-tns-c2968650486-2139"></div></div></div>```
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);

```

<div class="code-block ng-tns-c2968650486-2139 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--6"><div class="formatted-code-block-internal-container ng-tns-c2968650486-2139"><div class="animated-opacity ng-tns-c2968650486-2139"></div></div></div>---

### 3. Couche Voirie (Roads / Ways)

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

<div class="code-block ng-tns-c2968650486-2140 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-sql-2"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c2968650486-2140 ng-star-inserted"><span class="ng-tns-c2968650486-2140">SQL</span><div class="buttons ng-tns-c2968650486-2140 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c2968650486-2140"><div class="animated-opacity ng-tns-c2968650486-2140"></div></div></div>```
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);

```

<div class="code-block ng-tns-c2968650486-2140 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--10"><div class="formatted-code-block-internal-container ng-tns-c2968650486-2140"><div class="animated-opacity ng-tns-c2968650486-2140"></div></div></div>---

### 4. Couche Occupation du Sol (Landuse)

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

<div class="code-block ng-tns-c2968650486-2141 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-sql-3"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c2968650486-2141 ng-star-inserted"><span class="ng-tns-c2968650486-2141">SQL</span><div class="buttons ng-tns-c2968650486-2141 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c2968650486-2141"><div class="animated-opacity ng-tns-c2968650486-2141"></div></div></div>```
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);

```

<div class="code-block ng-tns-c2968650486-2141 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--14"><div class="formatted-code-block-internal-container ng-tns-c2968650486-2141"><div class="animated-opacity ng-tns-c2968650486-2141"></div></div></div>---

### 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.*

<div class="code-block ng-tns-c2968650486-2142 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-sql-4"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c2968650486-2142 ng-star-inserted"><span class="ng-tns-c2968650486-2142">SQL</span><div class="buttons ng-tns-c2968650486-2142 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c2968650486-2142"><div class="animated-opacity ng-tns-c2968650486-2142"></div></div></div>```
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);

```

<div class="code-block ng-tns-c2968650486-2142 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--18"><div class="formatted-code-block-internal-container ng-tns-c2968650486-2142"><div class="animated-opacity ng-tns-c2968650486-2142"></div></div></div>---

### 💡 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" ?