⚡ Zones Blanches Bornes VE France

Communes sans borne de recharge · Score = Distance × Population

⬡ REQUÊTE POSTGIS ZONES BLANCHES VE

-- 1. Distance à la borne la plus proche
WITH distances AS (
  SELECT
    c.insee_com,
    c.nom,
    c.departement,
    c.population,
    MIN(
      ST_Distance(
        c.geom::geography,
        b.geom::geography
      ) / 1000
    ) AS dist_borne_km
  FROM communes c
  LEFT JOIN bornes_irve b
    ON ST_DWithin(c.geom::geography, b.geom::geography, 100000)
  GROUP BY c.insee_com, c.nom, c.departement, c.population
),

-- 2. Calcul du score de priorité
scored AS (
  SELECT *,
    ROUND(dist_borne_km * population) AS score_priorite
  FROM distances
  WHERE dist_borne_km > 5
)

-- 3. Export des zones prioritaires
SELECT
  nom, departement, population,
  ROUND(dist_borne_km::numeric, 2) AS dist_borne_km,
  score_priorite,
  ST_Y(geom) AS latitude,
  ST_X(geom) AS longitude
FROM scored
ORDER BY score_priorite DESC;