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

В настоящее время я работаю на одном из телеканалов. Передо мной поставили задачу создания тепловой карты входящих звонков клиентов на номера телеканала по всей территории Российской Федерации. В моём распоряжении находятся АТС 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 "$messagen";
// Пишем сообщение в файл
file_put_contents($logFilePath, "$messagen", 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 ;

В grafana все стандартное и идет из коробки, просто добавил сервера для обращения и все.







Вот и всё - теперь есть красивая карта, которая автоматически обновляется и радует глаз.

Не судите строго, это первая публикация.
Автор: VictorSkvorez