202 lines
6.5 KiB
JavaScript
202 lines
6.5 KiB
JavaScript
// Berechnet die Straßenentfernung (km) zwischen der fixen Sternwarte Welzheim
|
|
// und dem Wohnort jedes BEOs und schreibt das Ergebnis in die Spalte beos.km.
|
|
//
|
|
// Der DB-Zugriff läuft über den PHP-Proxy (DB4js_all.php), da die echte
|
|
// Sternwarte-Datenbank nicht direkt erreichbar ist:
|
|
// Lesen: cmd LB_GET_BEO_ADRESSEN
|
|
// Schreiben: cmd LB_UPDATE_BEO_KM { id, km }
|
|
//
|
|
// Geocoding: OpenStreetMap Nominatim | Routing: öffentlicher OSRM-Server.
|
|
// Beide kostenlos, ohne API-Key. Ausführen mit:
|
|
// node --env-file=.env berechne-km.mjs
|
|
//
|
|
// Optionen:
|
|
// --dry-run nichts in die DB schreiben, nur berechnen und anzeigen
|
|
|
|
import { readFile, writeFile } from 'node:fs/promises';
|
|
import { fileURLToPath } from 'node:url';
|
|
import { dirname, join } from 'node:path';
|
|
|
|
const __dirname = dirname(fileURLToPath(import.meta.url));
|
|
const CACHE_FILE = join(__dirname, 'geocode-cache.json');
|
|
const USER_AGENT = 'sternwarte-welzheim-km-berechnen/1.0 (rxf@gmx.de)';
|
|
const DRY_RUN = process.argv.includes('--dry-run');
|
|
|
|
const PHP_DB_URL = process.env.PHP_DB_URL || 'http://localhost:8080/DB4js_all.php';
|
|
|
|
// Fallback-Referenzpunkt: Ortsmitte Welzheim (nur Näherung, per .env überschreibbar)
|
|
const DEFAULT_STERNWARTE = { lat: 48.8756, lon: 9.6377 };
|
|
|
|
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
|
|
|
|
// ---- PHP-Proxy (DB4js) ----
|
|
|
|
async function callProxy(cmd, params = {}) {
|
|
const headers = { 'Content-Type': 'application/json' };
|
|
if (process.env.API_USER && process.env.API_PASS) {
|
|
const token = Buffer.from(`${process.env.API_USER}:${process.env.API_PASS}`).toString('base64');
|
|
headers.Authorization = `Basic ${token}`;
|
|
}
|
|
const res = await fetch(PHP_DB_URL, {
|
|
method: 'POST',
|
|
headers,
|
|
body: JSON.stringify({ cmd, ...params }),
|
|
});
|
|
const text = await res.text();
|
|
let json;
|
|
try {
|
|
json = JSON.parse(text);
|
|
} catch {
|
|
throw new Error(`DB4js ${cmd} HTTP ${res.status}: ${text.slice(0, 200)}`);
|
|
}
|
|
if (json && typeof json === 'object' && !Array.isArray(json) && 'error' in json) {
|
|
throw new Error(`DB4js ${cmd}: ${json.error}`);
|
|
}
|
|
if (!res.ok) throw new Error(`DB4js ${cmd} HTTP ${res.status}`);
|
|
return json;
|
|
}
|
|
|
|
// ---- Geocode-Cache (Adresse -> {lat, lon} | null) ----
|
|
|
|
async function loadCache() {
|
|
try {
|
|
return JSON.parse(await readFile(CACHE_FILE, 'utf8'));
|
|
} catch {
|
|
return {};
|
|
}
|
|
}
|
|
|
|
async function saveCache(cache) {
|
|
await writeFile(CACHE_FILE, JSON.stringify(cache, null, 2), 'utf8');
|
|
}
|
|
|
|
// ---- Nominatim Geocoding (max. 1 Anfrage/Sekunde) ----
|
|
|
|
async function geocode(query, cache) {
|
|
if (Object.prototype.hasOwnProperty.call(cache, query)) {
|
|
return cache[query]; // Treffer (auch gecachte Fehlschläge = null)
|
|
}
|
|
const url =
|
|
'https://nominatim.openstreetmap.org/search?format=json&limit=1&q=' +
|
|
encodeURIComponent(query);
|
|
const res = await fetch(url, { headers: { 'User-Agent': USER_AGENT } });
|
|
if (!res.ok) throw new Error(`Nominatim HTTP ${res.status}`);
|
|
const json = await res.json();
|
|
const hit = Array.isArray(json) && json[0]
|
|
? { lat: parseFloat(json[0].lat), lon: parseFloat(json[0].lon) }
|
|
: null;
|
|
cache[query] = hit;
|
|
await saveCache(cache);
|
|
await sleep(1100); // Rate-Limit einhalten
|
|
return hit;
|
|
}
|
|
|
|
// ---- OSRM Straßenrouting -> Distanz in km ----
|
|
|
|
async function roadKm(from, to) {
|
|
const url =
|
|
`https://router.project-osrm.org/route/v1/driving/` +
|
|
`${from.lon},${from.lat};${to.lon},${to.lat}?overview=false`;
|
|
const res = await fetch(url, { headers: { 'User-Agent': USER_AGENT } });
|
|
if (!res.ok) throw new Error(`OSRM HTTP ${res.status}`);
|
|
const json = await res.json();
|
|
if (json.code !== 'Ok' || !json.routes?.[0]) return null;
|
|
return Math.round((json.routes[0].distance / 1000) * 10) / 10;
|
|
}
|
|
|
|
function buildAddress(row) {
|
|
const parts = [row.Adresse, [row.Plz, row.Ort].filter(Boolean).join(' ').trim()]
|
|
.map((s) => (s || '').trim())
|
|
.filter(Boolean);
|
|
return parts.length ? parts.join(', ') + ', Deutschland' : '';
|
|
}
|
|
|
|
async function resolveSternwarte(cache) {
|
|
const envLat = parseFloat(process.env.STERNWARTE_LAT ?? '');
|
|
const envLon = parseFloat(process.env.STERNWARTE_LON ?? '');
|
|
if (Number.isFinite(envLat) && Number.isFinite(envLon)) {
|
|
return { lat: envLat, lon: envLon, source: '.env' };
|
|
}
|
|
const hit = await geocode('Sternwarte, Welzheim, Deutschland', cache);
|
|
if (hit) return { ...hit, source: 'Nominatim' };
|
|
return { ...DEFAULT_STERNWARTE, source: 'Fallback (Ortsmitte Welzheim)' };
|
|
}
|
|
|
|
async function main() {
|
|
const cache = await loadCache();
|
|
|
|
const ref = await resolveSternwarte(cache);
|
|
console.log(`Proxy: ${PHP_DB_URL}`);
|
|
console.log(
|
|
`Referenzpunkt Sternwarte Welzheim: ${ref.lat}, ${ref.lon} [${ref.source}]\n`
|
|
);
|
|
|
|
const rows = await callProxy('LB_GET_BEO_ADRESSEN');
|
|
|
|
const done = [];
|
|
const skipped = [];
|
|
|
|
for (const row of rows) {
|
|
const address = buildAddress(row);
|
|
if (!address) {
|
|
skipped.push({ row, reason: 'keine Adresse' });
|
|
if (!DRY_RUN) await callProxy('LB_UPDATE_BEO_KM', { id: row.ID, km: null });
|
|
continue;
|
|
}
|
|
|
|
let km = null;
|
|
let reason = '';
|
|
try {
|
|
const geo = await geocode(address, cache);
|
|
if (!geo) {
|
|
reason = 'Geocoding ohne Treffer';
|
|
} else {
|
|
km = await roadKm(ref, geo);
|
|
if (km == null) reason = 'keine Route gefunden';
|
|
}
|
|
} catch (err) {
|
|
reason = err.message;
|
|
}
|
|
|
|
if (!DRY_RUN) await callProxy('LB_UPDATE_BEO_KM', { id: row.ID, km });
|
|
|
|
if (km == null) skipped.push({ row, reason });
|
|
else done.push({ row, km });
|
|
}
|
|
|
|
// ---- Zusammenfassung ----
|
|
console.log('Berechnete Entfernungen:');
|
|
console.log('Kürzel Name Ort km');
|
|
console.log('─'.repeat(66));
|
|
done
|
|
.sort((a, b) => a.km - b.km)
|
|
.forEach(({ row, km }) => {
|
|
console.log(
|
|
(row.Kuerzel || '').padEnd(7) +
|
|
' ' +
|
|
(row.Name || '').padEnd(24) +
|
|
' ' +
|
|
(row.Ort || '').padEnd(24) +
|
|
' ' +
|
|
String(km).padStart(6)
|
|
);
|
|
});
|
|
|
|
if (skipped.length) {
|
|
console.log('\nÜbersprungen / ohne Ergebnis (km = NULL):');
|
|
skipped.forEach(({ row, reason }) =>
|
|
console.log(` ${(row.Kuerzel || '?').padEnd(7)} ${(row.Name || '').padEnd(24)} — ${reason}`)
|
|
);
|
|
}
|
|
|
|
console.log(
|
|
`\nFertig. ${done.length} berechnet, ${skipped.length} übersprungen.` +
|
|
(DRY_RUN ? ' (DRY-RUN: nichts in die DB geschrieben)' : ' In Spalte beos.km gespeichert.')
|
|
);
|
|
}
|
|
|
|
main().catch((err) => {
|
|
console.error('Fehler:', err.message);
|
|
process.exit(1);
|
|
});
|