Arnaud
5633969d98
Carte Leaflet (annuaire Éducation nationale) + API PHP/MySQL : fiche établissement (effectifs, IPS, population INSEE, DPE ADEME), GED documents/photos, commentaires par groupe, gestion utilisateurs et politique de mots de passe. Imports opendata (effectifs, IPS, population, DPE, IRIS). Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
210 lines
8.1 KiB
PHP
210 lines
8.1 KiB
PHP
<?php
|
|
/**
|
|
* Bibliothèque partagée DPE (ADEME) : création des tables, import du dataset
|
|
* tertiaire et rattachement géographique aux établissements.
|
|
*
|
|
* Utilisée par les scripts CLI (import_dpe.php, match_dpe.php) et par
|
|
* l'endpoint web super-admin (api/import_dpe.php), pour éviter toute
|
|
* divergence de logique.
|
|
*/
|
|
declare(strict_types=1);
|
|
require_once __DIR__ . '/db.php';
|
|
|
|
const DPE_DATASET = 'https://data.ademe.fr/data-fair/api/v1/datasets/dpe01tertiaire/lines';
|
|
const DPE_FIELDS = 'numero_dpe,etiquette_dpe,etiquette_ges,conso_kwhep_m2_an,'
|
|
. 'emission_ges_kg_co2_m2_an,surface_utile,periode_construction,categorie_erp,'
|
|
. 'date_etablissement_dpe,date_fin_validite_dpe,code_insee_ban,code_postal_ban,'
|
|
. 'adresse_ban,nom_commune_ban,_geopoint';
|
|
|
|
/** GET JSON via cURL (suit les redirections). */
|
|
function dpe_fetch_json(string $url): array
|
|
{
|
|
$ch = curl_init($url);
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_FOLLOWLOCATION => true,
|
|
CURLOPT_TIMEOUT => 120,
|
|
CURLOPT_USERAGENT => 'ecoles-import/1.0',
|
|
CURLOPT_HTTPHEADER => ['Accept: application/json'],
|
|
]);
|
|
$body = curl_exec($ch);
|
|
if ($body === false) {
|
|
$err = curl_error($ch);
|
|
curl_close($ch);
|
|
throw new RuntimeException('Requête échouée : ' . $err);
|
|
}
|
|
$code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
|
|
curl_close($ch);
|
|
if ($code >= 400) {
|
|
throw new RuntimeException("HTTP $code sur l'API ADEME.");
|
|
}
|
|
$data = json_decode($body, true);
|
|
if (!is_array($data)) {
|
|
throw new RuntimeException('Réponse JSON ADEME invalide.');
|
|
}
|
|
return $data;
|
|
}
|
|
|
|
/** Crée la table « dpe » si absente. */
|
|
function dpe_create_table(PDO $pdo): void
|
|
{
|
|
$pdo->exec(
|
|
'CREATE TABLE IF NOT EXISTS dpe (
|
|
numero_dpe VARCHAR(20) NOT NULL PRIMARY KEY,
|
|
etiquette_dpe CHAR(1) NULL,
|
|
etiquette_ges CHAR(1) NULL,
|
|
conso DECIMAL(10,1) NULL, -- conso_kwhep_m2_an (énergie primaire kWh/m²/an)
|
|
ges DECIMAL(10,2) NULL, -- emission_ges_kg_co2_m2_an
|
|
surface INT NULL, -- surface_utile (m²)
|
|
periode VARCHAR(40) NULL, -- periode_construction
|
|
cat_erp VARCHAR(60) NULL, -- categorie_erp
|
|
date_dpe DATE NULL, -- date_etablissement_dpe
|
|
date_fin DATE NULL, -- date_fin_validite_dpe
|
|
code_insee VARCHAR(5) NULL, -- code_insee_ban (clé de rattachement commune)
|
|
cp VARCHAR(5) NULL,
|
|
adresse VARCHAR(255) NULL,
|
|
commune VARCHAR(120) NULL,
|
|
lat DECIMAL(9,6) NULL,
|
|
lon DECIMAL(9,6) NULL,
|
|
INDEX idx_insee (code_insee)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Importe les DPE tertiaires de l'ADEME dans la table « dpe ».
|
|
* @param string|null $dep filtre département (code) ou null pour tout
|
|
* @param callable $log fn(string) pour la progression
|
|
* @return int nombre de DPE importés
|
|
*/
|
|
function dpe_import(PDO $pdo, ?string $dep, callable $log): int
|
|
{
|
|
dpe_create_table($pdo);
|
|
|
|
$url = DPE_DATASET . '?size=10000&sort=numero_dpe&select=' . rawurlencode(DPE_FIELDS);
|
|
if ($dep !== null && $dep !== '') {
|
|
$url .= '&qs=' . rawurlencode('code_departement_ban:"' . $dep . '"');
|
|
$log("Filtre département = $dep.");
|
|
}
|
|
|
|
$ins = $pdo->prepare(
|
|
'REPLACE INTO dpe
|
|
(numero_dpe, etiquette_dpe, etiquette_ges, conso, ges, surface, periode,
|
|
cat_erp, date_dpe, date_fin, code_insee, cp, adresse, commune, lat, lon)
|
|
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
|
|
);
|
|
|
|
$letter = static fn($v) => ($v !== null && $v !== '') ? strtoupper(substr((string) $v, 0, 1)) : null;
|
|
$date = static fn($v) => (is_string($v) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $v)) ? $v : null;
|
|
$numOrNull = static fn($v) => ($v === null || $v === '') ? null : (float) $v;
|
|
|
|
$page = 0;
|
|
$n = 0;
|
|
$total = null;
|
|
$pdo->beginTransaction();
|
|
|
|
while ($url) {
|
|
$res = dpe_fetch_json($url);
|
|
$total ??= $res['total'] ?? null;
|
|
$rows = $res['results'] ?? [];
|
|
if (!$rows) {
|
|
break;
|
|
}
|
|
foreach ($rows as $r) {
|
|
$id = $r['numero_dpe'] ?? '';
|
|
if ($id === '') {
|
|
continue;
|
|
}
|
|
$lat = $lon = null;
|
|
if (!empty($r['_geopoint']) && str_contains($r['_geopoint'], ',')) {
|
|
[$lat, $lon] = array_map('floatval', explode(',', $r['_geopoint'], 2));
|
|
}
|
|
$ins->execute([
|
|
substr($id, 0, 20),
|
|
$letter($r['etiquette_dpe'] ?? null),
|
|
$letter($r['etiquette_ges'] ?? null),
|
|
$numOrNull($r['conso_kwhep_m2_an'] ?? null),
|
|
$numOrNull($r['emission_ges_kg_co2_m2_an'] ?? null),
|
|
isset($r['surface_utile']) && $r['surface_utile'] !== '' ? (int) round((float) $r['surface_utile']) : null,
|
|
$r['periode_construction'] ?? null,
|
|
$r['categorie_erp'] ?? null,
|
|
$date($r['date_etablissement_dpe'] ?? null),
|
|
$date($r['date_fin_validite_dpe'] ?? null),
|
|
$r['code_insee_ban'] ?? null,
|
|
$r['code_postal_ban'] ?? null,
|
|
$r['adresse_ban'] ?? null,
|
|
$r['nom_commune_ban'] ?? null,
|
|
$lat, $lon,
|
|
]);
|
|
$n++;
|
|
}
|
|
$pdo->commit();
|
|
$pdo->beginTransaction();
|
|
|
|
$page++;
|
|
$log(sprintf('Page %d — %d DPE importés%s', $page, $n, $total ? " / $total" : ''));
|
|
|
|
// Page suivante (curseur). data-fair renvoie l'URL complète dans « next ».
|
|
$url = $res['next'] ?? null;
|
|
if ($url) {
|
|
usleep(800000); // ménage le quota anonyme de l'API (20 s de traitement / 60 s)
|
|
}
|
|
}
|
|
$pdo->commit();
|
|
|
|
return $n;
|
|
}
|
|
|
|
/** Crée la table de rattachement « etab_dpe » si absente. */
|
|
function dpe_ensure_link_table(PDO $pdo): void
|
|
{
|
|
$pdo->exec(
|
|
'CREATE TABLE IF NOT EXISTS etab_dpe (
|
|
uai VARCHAR(12) NOT NULL,
|
|
numero_dpe VARCHAR(20) NOT NULL,
|
|
distance_m SMALLINT NOT NULL, -- distance école↔bâtiment (m)
|
|
PRIMARY KEY (uai, numero_dpe),
|
|
INDEX idx_uai (uai)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Rattache les DPE aux établissements par proximité (même commune, ≤ $radius m).
|
|
* @return array{pairs:int, ecoles:int}
|
|
*/
|
|
function dpe_match(PDO $pdo, int $radius, callable $log): array
|
|
{
|
|
foreach (['dpe', 'etablissements'] as $t) {
|
|
if (!$pdo->query('SHOW TABLES LIKE ' . $pdo->quote($t))->fetchColumn()) {
|
|
throw new RuntimeException("Table « $t » absente — lancez d'abord l'import.");
|
|
}
|
|
}
|
|
if (!$pdo->query("SHOW COLUMNS FROM etablissements LIKE 'comc'")->fetchColumn()) {
|
|
throw new RuntimeException("Colonne « comc » absente — lancez d'abord import_comc.php.");
|
|
}
|
|
|
|
dpe_ensure_link_table($pdo);
|
|
$log("Rattachement (rayon = {$radius} m)…");
|
|
$pdo->exec('TRUNCATE TABLE etab_dpe');
|
|
|
|
// Jointure restreinte à la commune (index idx_insee / idx_comc) puis filtre
|
|
// par distance sphérique (ST_Distance_Sphere ⇒ MySQL 5.7.6+ / MariaDB 10.2.4+).
|
|
$sql =
|
|
'INSERT INTO etab_dpe (uai, numero_dpe, distance_m)
|
|
SELECT e.id, d.numero_dpe,
|
|
ROUND(ST_Distance_Sphere(POINT(e.lon, e.lat), POINT(d.lon, d.lat)))
|
|
FROM etablissements e
|
|
JOIN dpe d ON d.code_insee = e.comc
|
|
WHERE e.comc IS NOT NULL AND e.lat IS NOT NULL AND e.lon IS NOT NULL
|
|
AND d.lat IS NOT NULL AND d.lon IS NOT NULL
|
|
AND ST_Distance_Sphere(POINT(e.lon, e.lat), POINT(d.lon, d.lat)) <= ?';
|
|
$st = $pdo->prepare($sql);
|
|
$st->execute([$radius]);
|
|
|
|
return [
|
|
'pairs' => (int) $pdo->query('SELECT COUNT(*) FROM etab_dpe')->fetchColumn(),
|
|
'ecoles' => (int) $pdo->query('SELECT COUNT(DISTINCT uai) FROM etab_dpe')->fetchColumn(),
|
|
];
|
|
}
|