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);"