Front page

Story 02 · Champion Trials hackathon · Digital School

CityCare

Smart reporting platform


A complete, production-like city reporting platform built with pure PHP, MySQL, Bootstrap and Leaflet maps.

Made for the Champion Trials hackathon at Digital School. It features AI-powered prioritisation and cluster detection for efficient city management.

Version one, ChampionTrials, is HTML. Version two, ChampionTrials2, is PHP.

Stack
PHP, MySQL, Bootstrap, Leaflet
Event
Champion Trials hackathon, Digital School

Finding clusters of reports

app/reports.php · lines 322 to 353

One SQL query pairs reports of the same category that sit within 0.005° of each other and were filed within the same time window, then ranks the biggest clusters first.

app/reports.phpChampionTrials2
/** * Detect clusters of reports (within distance and time window) */function detectClusters($category = null, $distanceMeters = 500, $timeHours = 48) {    $pdo = getDB();     $categoryFilter = $category ? "AND r.category = :category" : "";    $params = ['distance' => $distanceMeters, 'hours' => $timeHours];    if ($category) {        $params['category'] = $category;    }     $stmt = $pdo->prepare("        SELECT r1.report_id, r1.latitude, r1.longitude, r1.category,               COUNT(r2.report_id) as cluster_size        FROM reports r1        JOIN reports r2 ON (            r1.report_id != r2.report_id            AND r1.category = r2.category            AND ABS(r1.latitude - r2.latitude) < 0.005            AND ABS(r1.longitude - r2.longitude) < 0.005            AND ABS(TIMESTAMPDIFF(HOUR, r1.created_at, r2.created_at)) <= :hours        )        WHERE r1.created_at >= DATE_SUB(NOW(), INTERVAL :hours HOUR)        {$categoryFilter}        GROUP BY r1.report_id        HAVING cluster_size >= 2        ORDER BY cluster_size DESC    ");     $stmt->execute($params);    return $stmt->fetchAll();

Scoring priority with AI

app/ai.php · lines 197 to 207

The model’s reply is decoded as JSON, rejected if it has no priority, and clamped to a 1 to 5 scale before it is stored.

app/ai.phpChampionTrials2
$jsonStr = $result["choices"][0]["message"]["content"];$parsed = json_decode($jsonStr, true); if (!$parsed || !isset($parsed["priority"])) {    error_log("AI Priority: Invalid response format");    return false;} // Ensure priority is between 1 and 5$priority = max(1, min(5, intval($parsed["priority"])));$reason = $parsed["reason"] ?? "AI analysis completed";