Geo code телефонных номеров

Привет! Меня зовут Виктор, и у меня есть некоторый опыт в области телефонии и разработки. Хотел бы поделиться своим проектом — тепловой картой телефонных номеров. Хотя подобные карты уже существуют, аналогичной визуализации звонков на Хабре пока не встречал. Вот пример того, как она выглядит:

В настоящее время я работаю на одном из телеканалов. Передо мной поставили задачу создания тепловой карты входящих звонков клиентов на номера телеканала по всей территории Российской Федерации. В моём распоряжении находятся АТС Asterisk и соответствующие записи CDR (Call Detail Records), а также система мониторинга Grafana, тестовый сервер и две MySQL-базы данных.

Задача заключается в том, чтобы извлечь номера телефонов из CDR, определить их географическое местоположение и затем отобразить эти данные на карте, используя соответствующие координаты. Хочу сразу отметить, что DEF-номера не имеют прямой географической привязки, но в реестре российской системы нумерации такая информация всё же присутствует. Именно оттуда я загрузил CSV-файлы для импорта в базу данных.

Для получения координат (широта и долгота) мне пришлось воспользоваться API. Признаюсь, я обращался к платному ресурсу дадата, хотя они утверждают, что используют бесплатный источник информации с openstreetmap.org. Создаю запросы через API, извлекая уникальные строки из базы данных и добавляя координаты после получения ответа от сервиса:

<?php  // Настройки подключения к базе данных $host = 'Адрес_базы'; $db   = 'Выбор_базы'; $user = 'Пользователь'; $pass = 'Пароль'; $charset = 'utf8mb4';  // Подключение к базе данных $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; $options = [     PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,     PDO::ATTR_EMULATE_PREPARES   => false, ];  try {     $pdo = new PDO($dsn, $user, $pass, $options); } catch (\PDOException $e) {     throw new \PDOException($e->getMessage(), (int)$e->getCode()); }  // Функция для логирования function log_message($message) {     // Сохраняем сообщение в лог-файл в директории /scripts     $logFilePath = '/scripts/log.txt';      // Выводим сообщение в консоль     echo "$message\n";      // Пишем сообщение в файл     file_put_contents($logFilePath, "$message\n", FILE_APPEND | LOCK_EX); }  // Чтение данных из таблицы $sql = "SELECT DISTINCT region FROM asterisk.region"; $stmt = $pdo->query($sql); $regions = $stmt->fetchAll(PDO::FETCH_COLUMN);  // Массив для хранения уникальных регионов $uniqueRegions = [];  // Обрабатываем каждый уникальный регион foreach ($regions as $region) {     // Добавляем в массив уникальных регионов     $uniqueRegions[] = ['region' => $region];      // Логируем обработку региона     log_message("Обрабатывается регион: $region"); }  // Ключ API $apiToken = 'TOKEN'; $apiSecret = 'KEY';  // URL для обращения к API $apiUrl = 'https://dadata.ru/api/address';  // Отправка запросов к API и получение координат foreach ($uniqueRegions as $item) {     sleep(1); // Пауза перед каждым запросом на 1 секунду      // Данные для отправки     $postData = json_encode([$item['region']]);      // Инициализируем cURL     $ch = curl_init();      // Устанавливаем параметры cURL     curl_setopt($ch, CURLOPT_URL, $apiUrl);     curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);     curl_setopt($ch, CURLOPT_POST, true);     curl_setopt($ch, CURLOPT_HTTPHEADER, array(         'Content-Type: application/json',         'Accept: application/json',         'Authorization: Token ' . $apiToken,         'X-Secret: ' . $apiSecret     ));     curl_setopt($ch, CURLOPT_POSTFIELDS, $postData);      // Выполняем запрос     $response = curl_exec($ch);      // Проверяем, был ли получен ответ     if ($response === false) {         log_message("Не удалось получить координаты для региона {$item['region']} (ошибка HTTP).");         continue;     }      // Закрываем соединение     curl_close($ch);      // Парсим JSON-ответ     $data = json_decode($response, true);      // Пример проверки наличия координат в новом формате ответа     if (isset($data[0]['geo_lat'], $data[0]['geo_lon'])) {         $latitude = $data[0]['geo_lat'];  // Здесь предполагаемый путь к широте         $longitude = $data[0]['geo_lon']; // Здесь предполагаемый путь к долготе          // Формируем строку для записи в БД         $geo = "$latitude,$longitude";          // Запись координат в базу данных         $updateSql = "UPDATE asterisk.region SET geo = :geo WHERE region = :region";         $stmt = $pdo->prepare($updateSql);         $stmt->execute([':geo' => $geo, ':region' => $item['region']]);          // Логируем успешную запись         log_message("Записаны координаты для региона {$item['region']}: $geo");     } else {         // Логируем отсутствие координат         log_message("Не удалось получить координаты для региона {$item['region']}");     } }  // Логируем завершение обработки log_message("Обновление базы данных завершено.");  ?>

В результате получилась вот такая картина, но некоторые координаты остались пустыми — около 106 000 строк не заполнились. Чтобы решить эту проблему, я вручную добавил координаты для крупных регионов, используя Яндекс.Карты и выполнив массовые обновления в базе данных.

Основная часть работы сделана — координаты получены. Теперь нужно взять номера из CDR, сопоставить их с префиксами и найти соответствующие координаты. Скрипт занял много времени, поэтому я решил упростить процесс и создал триггер прямо в базе данных. Возможно, это не самый правильный подход, но мне было важно ускорить работу. Этот триггер срабатывает каждый раз, когда появляется новая запись в CDR: номер парсится, и результат записывается в таблицу geo.

DELIMITER $$  CREATE TRIGGER trg_insert_geo_code AFTER INSERT ON asterisk_cdr.cdr FOR EACH ROW BEGIN     DECLARE v_region VARCHAR(255);     DECLARE v_latitude DECIMAL(10,6);     DECLARE v_longitude DECIMAL(10,6);          -- Получаем данные для вставки в geo_codes     SELECT r.region,            SUBSTRING_INDEX(r.geo, ',', 1),            SUBSTRING_INDEX(SUBSTRING_INDEX(r.geo, ',', -1), ',', 1)     INTO v_region, v_latitude, v_longitude     FROM asterisk_cdr.region r     WHERE LEFT(NEW.src, 3) = r.prefix       AND CAST(SUBSTR(NEW.src, 4) AS UNSIGNED) BETWEEN r.`from` AND r.`to`;            -- Вставляем данные в geo_codes     INSERT INTO asterisk_cdr.geo_codes (Name, Latitude, Longitude, record_id)     VALUES (v_region, v_latitude, v_longitude, NEW.id); END$$  DELIMITER ;