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 ;