L'extraction OSM-raw à un OSM_territoire d'étude

Commandes itératives menées par l'IA pour raffiner OSM à l'échelle du territoire d'analyse avec une précision importante sur le regroupement des aménités

Carnet des commandes itératives
260407-contenu des tables OSM dans OSM-raw
260407-Extraction OSM réussie sur Bandol

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

SQL

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

SQL

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.

SQL

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.

SQL

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.

SQL

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

SRID Unique (2154) : Plus de bugs d'affichage ou de calculs en degrés. Tout est en mètres.
ST_Intersection vs ST_Intersects : On a fait la distinction entre "ce qui touche" (bâtiments) et "ce qui est coupé" (routes/landuse).
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" ?

260407-contenu des tables OSM dans OSM-raw

debian@vps-02c1f983-vps-ovh-net:~$

260407-Extraction OSM réussie sur Bandol

docker exec -i alteris_postgis psql -U alteris_admin -d alteris_geo -c "
/* 1. NETTOYAGE PRÉALABLE */
DROP TABLE IF EXISTS \"83009_bandol\".armature_urbaine_osm;

/* 2. EXTRACTION MULTI-SOURCES (Points, Bâtiments, Zones) */
CREATE TABLE \"83009_bandol\".armature_urbaine_osm AS
SELECT * FROM (
-- BLOC A : Points d'intérêts (Commerces, Santé, Services)
SELECT
osm_id::bigint, name, amenity, leisure, shop,
'point'::text as osm_type,
ST_Transform(way, 2154) as geom
FROM osm_raw.points
WHERE (name IS NOT NULL)
AND (amenity NOT IN ('bench', 'waste_basket', 'post_box', 'vending_machine', 'parking_entrance', 'hunting_stand', 'parking') OR amenity IS NULL)

UNION ALL

-- BLOC B : Bâtiments structurants (Écoles, Mairie, Hôpitaux)
-- On utilise ST_Centroid pour transformer les surfaces en points localisables
SELECT
osm_id::bigint, name, amenity, leisure, NULL::text as shop,
'building'::text as osm_type,
ST_Transform(ST_Centroid(way), 2154) as geom
FROM osm_raw.buildings
WHERE (name IS NOT NULL)
AND (amenity NOT IN ('parking', 'garages', 'waste_disposal') OR amenity IS NULL)

UNION ALL

-- BLOC C : Zones de loisirs et parcs (Stades, Jardins)
-- Sécurisation du champ 'landuse' via un cast ::text pour éviter les erreurs de type record
SELECT
osm_id::bigint, name, NULL::text as amenity, NULL::text as leisure, NULL::text as shop,
'landuse'::text as osm_type,
ST_Transform(ST_Centroid(geom), 2154) as geom
FROM osm_raw.landuse
WHERE name IS NOT NULL
AND \"landuse\"::text NOT IN ('cemetery', 'residential', 'industrial', 'grass', 'forest', 'farmland')
) sub
WHERE geom IS NOT NULL;

/* 3. FILTRAGE GÉOGRAPHIQUE (Buffer de 5km autour du projet) */
-- On force le SRID 2154 pour la comparaison spatiale
DELETE FROM \"83009_bandol\".armature_urbaine_osm
WHERE NOT ST_DWithin(
geom,
(SELECT ST_SetSRID(ST_Centroid(ST_Extent(geom)), 2154) FROM \"83009_bandol\".bandol_parcelles),
5000
);

/* 4. QUALIFICATION THÉMATIQUE (Tri pour la légende QGIS) */
ALTER TABLE \"83009_bandol\".armature_urbaine_osm ADD COLUMN IF NOT EXISTS categorie text;

UPDATE \"83009_bandol\".armature_urbaine_osm SET categorie =
CASE
WHEN amenity IN ('school', 'kindergarten', 'college', 'university') OR name ILIKE '%école%' OR name ILIKE '%collège%' THEN 'Enseignement'
WHEN amenity IN ('restaurant', 'cafe', 'bar', 'fast_food', 'pub') THEN 'Restauration/Sorties'
WHEN amenity IN ('pharmacy', 'doctors', 'hospital', 'dentist') THEN 'Santé'
WHEN amenity IN ('bank', 'post_office', 'townhall', 'police') THEN 'Services Publics/Banques'
WHEN shop IS NOT NULL THEN 'Commerce'
WHEN leisure IS NOT NULL OR osm_type = 'landuse' THEN 'Loisirs/Espaces Verts'
ELSE 'Autre'
END;

/* 5. OPTIMISATION (Index GIST pour affichage rapide sous QGIS) */
CREATE INDEX idx_armature_geom ON \"83009_bandol\".armature_urbaine_osm USING GIST(geom);"