skyview.astronomiemuseum.de/scripts/export/export_stars_and_constellations.php

<?php
declare(strict_types=1);

ini_set('memory_limit', '512M');

$rootDir = dirname(__DIR__, 2);
$dbConfigPath = $rootDir . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'database.php';

if (!is_file($dbConfigPath)) {
    fwrite(STDERR, "Konfigurationsdatei nicht gefunden: {$dbConfigPath}\n");
    exit(1);
}

$dbConfig = require $dbConfigPath;

foreach (['host', 'dbname', 'user', 'pass'] as $requiredKey) {
    if (!isset($dbConfig[$requiredKey]) || !is_string($dbConfig[$requiredKey]) || $dbConfig[$requiredKey] === '') {
        fwrite(STDERR, "Fehlende oder ungültige DB-Konfiguration: {$requiredKey}\n");
        exit(1);
    }
}

$dsn = sprintf(
    'mysql:host=%s;dbname=%s;charset=%s',
    $dbConfig['host'],
    $dbConfig['dbname'],
    $dbConfig['charset'] ?? 'utf8mb4'
);

try {
    $pdo = new PDO($dsn, $dbConfig['user'], $dbConfig['pass'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (Throwable $e) {
    fwrite(STDERR, "Datenbankverbindung fehlgeschlagen: {$e->getMessage()}\n");
    exit(1);
}

$outputDir = $rootDir . DIRECTORY_SEPARATOR . 'public' . DIRECTORY_SEPARATOR . 'data';
if (!is_dir($outputDir) && !mkdir($outputDir, 0777, true) && !is_dir($outputDir)) {
    fwrite(STDERR, "Ausgabeverzeichnis konnte nicht erstellt werden: {$outputDir}\n");
    exit(1);
}

$starMagnitudeLimit = 6.0;

$stars = [];
$starStmt = $pdo->query(
    "SELECT hip, proper, bayer, bf, con, spect, mag, ci, `ra`, `dec`, rarad, decrad
     FROM star_hipparcos
     WHERE mag IS NOT NULL
       AND mag <= " . $starMagnitudeLimit . "
       AND rarad IS NOT NULL
       AND decrad IS NOT NULL
     ORDER BY mag ASC, hip ASC"
);

while ($row = $starStmt->fetch()) {
    $stars[] = [
        'hip' => $row['hip'] !== null ? (int) $row['hip'] : null,
        'proper' => trim((string) ($row['proper'] ?? '')),
        'bayer' => trim((string) ($row['bayer'] ?? '')),
        'bf' => trim((string) ($row['bf'] ?? '')),
        'con' => trim((string) ($row['con'] ?? '')),
        'spect' => trim((string) ($row['spect'] ?? '')),
        'ra_hours' => $row['ra'] !== null ? (float) $row['ra'] : null,
        'dec_degrees' => $row['dec'] !== null ? (float) $row['dec'] : null,
        'ra_radians' => (float) $row['rarad'],
        'dec_radians' => (float) $row['decrad'],
        'mag' => (float) $row['mag'],
        'ci' => $row['ci'] !== null ? (float) $row['ci'] : null,
    ];
}

$constellations = [];
$ensureConstellation = static function (string $id) use (&$constellations): void {
    if (!isset($constellations[$id])) {
        $constellations[$id] = [
            'id' => $id,
            'common_name' => [
                'german' => null,
                'native' => null,
                'byname' => null,
            ],
            'label_positions' => [],
            'lines' => [],
        ];
    }
};

$constellationStmt = $pdo->query("SELECT id FROM constellations ORDER BY id");
foreach ($constellationStmt as $row) {
    $ensureConstellation((string) $row['id']);
}

$nameStmt = $pdo->query(
    "SELECT constellation_id, german_name, native_name, byname
     FROM constellation_names
     ORDER BY constellation_id"
);
foreach ($nameStmt as $row) {
    $id = (string) $row['constellation_id'];
    $ensureConstellation($id);
    $constellations[$id]['common_name'] = [
        'german' => $row['german_name'] !== null ? (string) $row['german_name'] : null,
        'native' => $row['native_name'] !== null ? (string) $row['native_name'] : null,
        'byname' => $row['byname'] !== null ? (string) $row['byname'] : null,
    ];
}

$labelStmt = $pdo->query(
    "SELECT constellation_id, position_index, ra_hours, dec_degrees
     FROM constellation_label_positions
     ORDER BY constellation_id, position_index"
);
foreach ($labelStmt as $row) {
    $id = (string) $row['constellation_id'];
    $ensureConstellation($id);
    $constellations[$id]['label_positions'][] = [
        'index' => (int) $row['position_index'],
        'ra_hours' => (float) $row['ra_hours'],
        'dec_degrees' => (float) $row['dec_degrees'],
    ];
}

$lineStmt = $pdo->query(
    "SELECT cl.constellation_id, cl.line_index, clp.point_index, clp.hip_number
     FROM constellation_lines cl
     INNER JOIN constellation_line_points clp ON clp.line_id = cl.id
     ORDER BY cl.constellation_id, cl.line_index, clp.point_index"
);
foreach ($lineStmt as $row) {
    $id = (string) $row['constellation_id'];
    $lineIndex = (int) $row['line_index'];
    $ensureConstellation($id);
    $constellations[$id]['lines'][$lineIndex] ??= [
        'index' => $lineIndex,
        'points' => [],
    ];
    $constellations[$id]['lines'][$lineIndex]['points'][] = [
        'index' => (int) $row['point_index'],
        'hip' => (int) $row['hip_number'],
    ];
}

foreach ($constellations as &$constellation) {
    if ($constellation['lines'] !== []) {
        ksort($constellation['lines']);
        $constellation['lines'] = array_values($constellation['lines']);
    }
}
unset($constellation);

ksort($constellations);

$generatedAt = gmdate('c');

$starsPayload = [
    'generated_at' => $generatedAt,
    'source' => 'star_hipparcos',
    'magnitude_limit' => $starMagnitudeLimit,
    'count' => count($stars),
    'stars' => $stars,
];

$constellationsPayload = [
    'generated_at' => $generatedAt,
    'sources' => [
        'constellations',
        'constellation_names',
        'constellation_label_positions',
        'constellation_lines',
        'constellation_line_points',
    ],
    'count' => count($constellations),
    'constellations' => array_values($constellations),
];

$jsonFlags = JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES;

$starsPath = $outputDir . DIRECTORY_SEPARATOR . 'stars_mag6.json';
$constellationsPath = $outputDir . DIRECTORY_SEPARATOR . 'constellations.json';

if (file_put_contents($starsPath, json_encode($starsPayload, $jsonFlags) . PHP_EOL) === false) {
    fwrite(STDERR, "Konnte {$starsPath} nicht schreiben.\n");
    exit(1);
}

if (file_put_contents($constellationsPath, json_encode($constellationsPayload, $jsonFlags) . PHP_EOL) === false) {
    fwrite(STDERR, "Konnte {$constellationsPath} nicht schreiben.\n");
    exit(1);
}

fwrite(STDOUT, "Export abgeschlossen.\n");
fwrite(STDOUT, "Sterne: " . count($stars) . " -> {$starsPath}\n");
fwrite(STDOUT, "Sternbilder: " . count($constellations) . " -> {$constellationsPath}\n");