Adminator
1e51ef7def
- Konzept/, didaktik_geografie/, didaktik_simulation/, v2-modules/, v2-platform/ - 12 code-workspace-Files - STATUS-*.md - viele M/D/R-Änderungen an bereits getrackten Files - .gitignore verstärkt: **/.humaninput/, **/secret_keys.txt Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
151 lines
6.1 KiB
PHP
151 lines
6.1 KiB
PHP
<?php
|
|
/**
|
|
* Waypoint-Koordinaten-Audit gegen OpenStreetMap (Nominatim).
|
|
*
|
|
* Prüft alle city/village-Waypoints in geo_waypoints, vergleicht mit
|
|
* OSM-Koordinaten, listet Abweichungen. base/peak/target werden nur
|
|
* informativ angezeigt (keine Nominatim-Abfrage).
|
|
*
|
|
* Aufruf: http://localhost/geograsim/App/php/verify-waypoints.php
|
|
* http://localhost/geograsim/App/php/verify-waypoints.php?format=json
|
|
*/
|
|
require_once __DIR__ . '/config/app.php';
|
|
require_once __DIR__ . '/config/db.php';
|
|
|
|
set_time_limit(300);
|
|
$format = $_GET['format'] ?? 'html';
|
|
|
|
$db = getDB();
|
|
$rows = $db->query("SELECT wp_key, name, lat, lon, region, wp_type
|
|
FROM geo_waypoints
|
|
ORDER BY wp_type, region, name")->fetchAll();
|
|
|
|
function haversine_km($lat1, $lon1, $lat2, $lon2) {
|
|
$R = 6371.0;
|
|
$dLat = deg2rad($lat2 - $lat1);
|
|
$dLon = deg2rad($lon2 - $lon1);
|
|
$a = sin($dLat/2) ** 2 + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin($dLon/2) ** 2;
|
|
return $R * 2 * atan2(sqrt($a), sqrt(1 - $a));
|
|
}
|
|
|
|
function clean_name($name) {
|
|
// Entfernt "C8 ", "(ÖAMTC)" etc. für saubere Geocoding-Queries
|
|
$n = preg_replace('/^C\d+\s+/', '', $name);
|
|
$n = preg_replace('/\s*\(.*?\)\s*/', '', $n);
|
|
return trim($n);
|
|
}
|
|
|
|
function nominatim_lookup($query) {
|
|
$url = 'https://nominatim.openstreetmap.org/search?'
|
|
. http_build_query(['q' => $query, 'format' => 'json', 'countrycodes' => 'at', 'limit' => 1]);
|
|
$ctx = stream_context_create(['http' => [
|
|
'header' => "User-Agent: GeoGraSim-Waypoint-Audit/1.0 (lokal, Bildungsprojekt)\r\n",
|
|
'timeout' => 10,
|
|
]]);
|
|
$resp = @file_get_contents($url, false, $ctx);
|
|
if ($resp === false) return null;
|
|
$j = json_decode($resp, true);
|
|
if (!is_array($j) || count($j) === 0) return null;
|
|
return ['lat' => (float)$j[0]['lat'], 'lon' => (float)$j[0]['lon'], 'display' => $j[0]['display_name']];
|
|
}
|
|
|
|
$results = [];
|
|
foreach ($rows as $w) {
|
|
$row = [
|
|
'wp_key' => $w['wp_key'],
|
|
'name' => $w['name'],
|
|
'region' => $w['region'],
|
|
'wp_type' => $w['wp_type'],
|
|
'db_lat' => (float)$w['lat'],
|
|
'db_lon' => (float)$w['lon'],
|
|
'osm_lat' => null, 'osm_lon' => null, 'distance_km' => null,
|
|
'osm_display' => null, 'status' => 'skipped',
|
|
];
|
|
|
|
if (in_array($w['wp_type'], ['city', 'village'])) {
|
|
$q = clean_name($w['name']) . ', ' . ucfirst($w['region']) . ', Austria';
|
|
$osm = nominatim_lookup($q);
|
|
usleep(1_100_000); // 1.1 s, Nominatim Usage Policy
|
|
|
|
if ($osm) {
|
|
$row['osm_lat'] = $osm['lat'];
|
|
$row['osm_lon'] = $osm['lon'];
|
|
$row['osm_display'] = $osm['display'];
|
|
$row['distance_km'] = round(haversine_km($row['db_lat'], $row['db_lon'], $osm['lat'], $osm['lon']), 3);
|
|
if ($row['distance_km'] < 0.5) $row['status'] = 'ok';
|
|
elseif ($row['distance_km'] < 2.0) $row['status'] = 'review';
|
|
else $row['status'] = 'wrong';
|
|
} else {
|
|
$row['status'] = 'not_found';
|
|
}
|
|
}
|
|
|
|
$results[] = $row;
|
|
}
|
|
|
|
if ($format === 'json') {
|
|
header('Content-Type: application/json; charset=utf-8');
|
|
echo json_encode($results, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
|
|
exit;
|
|
}
|
|
|
|
$counts = array_count_values(array_column($results, 'status'));
|
|
?><!DOCTYPE html>
|
|
<html lang="de">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<title>Waypoint-Audit</title>
|
|
<style>
|
|
body{font-family:system-ui,sans-serif;padding:1rem;background:#f5efe0;color:#1f2a24}
|
|
h1{color:#2d5434;margin-bottom:.5rem}
|
|
.sum{background:#fff;padding:.8rem 1rem;border-radius:8px;margin-bottom:1rem;display:flex;gap:1rem;flex-wrap:wrap}
|
|
.sum span{font-weight:700}
|
|
table{border-collapse:collapse;width:100%;background:#fff;font-size:.82rem}
|
|
th,td{padding:.35rem .55rem;border-bottom:1px solid #eee;text-align:left;vertical-align:top}
|
|
th{background:#2d5434;color:#f5efe0;position:sticky;top:0}
|
|
tr.ok td{background:#f2f8f2}
|
|
tr.review td{background:#fff6d9}
|
|
tr.wrong td{background:#fde0dc}
|
|
tr.not_found td{background:#f0e6f5}
|
|
tr.skipped td{color:#999}
|
|
code{font-family:ui-monospace,monospace;font-size:.78rem}
|
|
.dist{font-weight:700;text-align:right}
|
|
.btn{display:inline-block;padding:.4rem .8rem;background:#2d5434;color:#fff;border-radius:6px;text-decoration:none;margin-right:.5rem}
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<h1>Waypoint-Koordinaten-Audit</h1>
|
|
<p style="color:#666;margin-bottom:1rem">Quelle: OSM/Nominatim. Nur <code>city</code> und <code>village</code> werden geprüft. Grün < 500 m, Gelb < 2 km, Rot > 2 km.</p>
|
|
<div class="sum">
|
|
<div>Gesamt: <span><?= count($results) ?></span></div>
|
|
<div>OK: <span style="color:#3a6b3e"><?= $counts['ok'] ?? 0 ?></span></div>
|
|
<div>Review: <span style="color:#8a7020"><?= $counts['review'] ?? 0 ?></span></div>
|
|
<div>Falsch: <span style="color:#c85c4a"><?= $counts['wrong'] ?? 0 ?></span></div>
|
|
<div>Nicht gefunden: <span style="color:#7a4a8a"><?= $counts['not_found'] ?? 0 ?></span></div>
|
|
<div>Übersprungen: <span style="color:#888"><?= $counts['skipped'] ?? 0 ?></span></div>
|
|
<a class="btn" href="?format=json">JSON-Export</a>
|
|
</div>
|
|
<table>
|
|
<thead><tr>
|
|
<th>Status</th><th>Key</th><th>Name</th><th>Region</th><th>Typ</th>
|
|
<th>DB Lat, Lon</th><th>OSM Lat, Lon</th><th class="dist">Δ km</th><th>OSM-Treffer</th>
|
|
</tr></thead>
|
|
<tbody>
|
|
<?php foreach ($results as $r): ?>
|
|
<tr class="<?= htmlspecialchars($r['status']) ?>">
|
|
<td><?= htmlspecialchars($r['status']) ?></td>
|
|
<td><code><?= htmlspecialchars($r['wp_key']) ?></code></td>
|
|
<td><?= htmlspecialchars($r['name']) ?></td>
|
|
<td><?= htmlspecialchars($r['region']) ?></td>
|
|
<td><?= htmlspecialchars($r['wp_type']) ?></td>
|
|
<td><code><?= number_format($r['db_lat'], 5) ?>, <?= number_format($r['db_lon'], 5) ?></code></td>
|
|
<td><?= $r['osm_lat'] !== null ? '<code>'.number_format($r['osm_lat'],5).', '.number_format($r['osm_lon'],5).'</code>' : '—' ?></td>
|
|
<td class="dist"><?= $r['distance_km'] !== null ? number_format($r['distance_km'], 2) : '—' ?></td>
|
|
<td style="font-size:.72rem;color:#666"><?= htmlspecialchars($r['osm_display'] ?? '') ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</body>
|
|
</html>
|