thetool/scripts/geocode-addresses.php

#!/usr/bin/php
<?php
/**
 * Geocode Addresses Script
 *
 * Populates gps_lat and gps_long for addresses missing coordinates.
 * Uses Google Geocoding API.
 *
 * Usage: php scripts/geocode-addresses.php [--limit=100] [--dry-run] [--verbose]
 */

require("../config/config.php");

define('FRONKDB_SQLDEBUG', false);
error_reporting(E_ALL & ~(E_NOTICE | E_STRICT | E_DEPRECATED));

require_once(LIBDIR . "/mvcfronk/mfRouter/mfRouter.php");
require_once(LIBDIR . "/mvcfronk/mfBase/mfBaseModel.php");
require_once(LIBDIR . "/mvcfronk/mfBase/mfBaseController.php");

// Parse command line arguments
$options = getopt('', ['limit::', 'dry-run', 'verbose']);
$limit = isset($options['limit']) ? intval($options['limit']) : 100;
$dryRun = isset($options['dry-run']);
$verbose = isset($options['verbose']);

// Check API key
if (!defined('TT_GEOCODING_API_SECRET') || empty(TT_GEOCODING_API_SECRET)) {
    die("ERROR: TT_GEOCODING_API_SECRET is not defined in config.\n");
}

$apiKey = TT_GEOCODING_API_SECRET;

// Get database connection
$db = FronkDB::singleton();

// Find addresses missing coordinates
$sql = "SELECT id, street, zip, city, country_id
        FROM Address
        WHERE (gps_lat IS NULL OR gps_lat = 0)
          AND street != ''
          AND street IS NOT NULL
          AND city != ''
          AND city IS NOT NULL
        ORDER BY id DESC
        LIMIT {$limit}";

$result = $db->query($sql);

if (!$result) {
    die("ERROR: Database query failed.\n");
}

$total = $db->num_rows($result);
echo "Found {$total} addresses to geocode" . ($dryRun ? " (DRY RUN)" : "") . "\n";

if ($total === 0) {
    echo "Nothing to do.\n";
    exit(0);
}

$success = 0;
$failed = 0;
$skipped = 0;

while ($row = $result->fetch_assoc()) {
    $id = $row['id'];
    $street = $row['street'];
    $zip = $row['zip'];
    $city = $row['city'];

    // Build address string
    $addressParts = [];
    if ($street) $addressParts[] = $street;
    if ($zip) $addressParts[] = $zip;
    if ($city) $addressParts[] = $city;
    $addressParts[] = 'Austria'; // Default country

    $addressString = implode(', ', $addressParts);

    if ($verbose) {
        echo "Processing ID {$id}: {$addressString}... ";
    }

    if ($dryRun) {
        if ($verbose) echo "SKIPPED (dry run)\n";
        $skipped++;
        continue;
    }

    // Call Google Geocoding API
    $encodedAddress = urlencode($addressString);
    $url = "https://maps.googleapis.com/maps/api/geocode/json?address={$encodedAddress}&key={$apiKey}&region=at";

    $response = @file_get_contents($url);
    if (!$response) {
        if ($verbose) echo "FAILED (API error)\n";
        $failed++;
        continue;
    }

    $data = json_decode($response, true);

    if ($data['status'] !== 'OK' || empty($data['results'])) {
        if ($verbose) {
            echo "FAILED (no results, status: {$data['status']})\n";
        }
        $failed++;
        continue;
    }

    $location = $data['results'][0]['geometry']['location'];
    $lat = $location['lat'];
    $lng = $location['lng'];

    // Update database
    $db->query("UPDATE Address SET gps_lat = {$lat}, gps_long = {$lng} WHERE id = {$id}");

    if ($verbose) {
        echo "OK ({$lat}, {$lng})\n";
    }

    $success++;

    // Rate limiting - 100ms between requests
    usleep(100000);
}

echo "\n";
echo "Completed:\n";
echo "  Success: {$success}\n";
echo "  Failed:  {$failed}\n";
echo "  Skipped: {$skipped}\n";
echo "  Total:   {$total}\n";