Front page
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.
- PHP, MySQL, Bootstrap, Leaflet
- Champion Trials hackathon, Digital School
Finding clusters of reports
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.
/** * 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
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.
$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";