Как мы перестали возить воздух: Маршрутизация доставки по Алматы через BigQuery GIS

Логистика в Алматы — это не просто наука о перемещении товаров из точки А в точку Б. Это суровое испытание на прочность для любого курьерского бизнеса, где законы евклидовой геометрии пасуют перед реальностью. Город, зажатый между горами, с его знаменитым уклоном «верх-низ», хроническими 10-балльными пробками на Аль-Фараби и запутанными микрорайонами, способен сломать любую стандартную логистическую модель.

Долгое время компании пытались решать эту задачу "в лоб". Они делили город на квадраты, привязывали курьеров к почтовым индексам или рисовали зоны обслуживания маркером на интерактивной карте. Но бизнес рос, а эффективность падала. Мы столкнулись с классической проблемой: курьеры возили воздух. Холостые пробеги достигали 35%, окна доставки срывались, а логисты выгорали, пытаясь перераспределить тысячи заказов вручную. В этой статье мы, инженеры OZAT, расскажем, как мы полностью перестроили логистический пайплайн крупного e-commerce проекта с помощью Google Cloud BigQuery GIS и Google Maps Platform, перейдя от статичных зон к динамической data-driven маршрутизации.

Почему статичные зоны доставки — это мертвый груз?

Классический подход к маршрутизации выглядит так: логист делит город на полигоны (например, Бостандыкский район, Медеуский район) и назначает каждому полигону определенное количество курьеров. Если заказ попадает в этот полигон, он падает в пул местного водителя. Что здесь не так?

• Игнорирование реальной плотности: Границы районов не отражают плотность заказов. Курьер может проехать 15 километров ради одной доставки на окраину Медеуского района, в то время как другой курьер "зашивается" на границе двух зон, доставляя 40 заказов в соседних ЖК.
• Прямая линия — это ложь: Стандартные системы часто считают расстояние по прямой (haversine distance). В Алматы точка может быть в 2 км по прямой, но из-за односторонних улиц (Сейфуллина, Наурызбай батыра) и отсутствия разворотов реальный путь составит 8 км.
• Временная слепота: Зоны не учитывают время суток. Утром трафик идет "вниз" по городу, вечером — "вверх". Паттерны заказов в будние дни (офисы) кардинально отличаются от выходных (спальные районы).

Наш клиент терял деньги на топливе, переработках курьеров и упущенной выгоде от недовольных клиентов. Нам нужно было интеллектуальное решение. Как мы уже рассказывали в нашей статье про автоматизацию бухгалтерии через Document AI, попытки решить сложные нелинейные задачи простыми шаблонами обречены на провал. Нужен был глубокий анализ данных.

Data-Driven логистика: Внедрение BigQuery GIS

Для решения проблемы мы решили применить геопространственную аналитику данных (Data Analytics). У клиента были накоплены терабайты данных: GPS-треки курьеров за три года, координаты всех успешных доставок, временные метки статусов. Обычная реляционная база данных подавилась бы при попытке сджойнить и кластеризовать миллионы координат. Поэтому мы развернули хранилище в Google BigQuery.

BigQuery обладает мощным модулем GIS (Geographic Information Systems), который позволяет выполнять сложнейшие пространственные вычисления прямо в SQL-запросах, используя синтаксис ST_* функций.

Отказ от районов: Магия кластеризации DBSCAN

Вместо того чтобы привязываться к вымышленным границам административных районов, мы заставили данные сами формировать зоны доставки. Мы использовали алгоритм DBSCAN (Density-Based Spatial Clustering of Applications with Noise), встроенный в BigQuery (ST_CLUSTERDBSCAN).

Алгоритм работает элегантно: он находит "горячие точки" (hotspots) — скопления координат заказов, которые находятся близко друг к другу. Если точки расположены слишком далеко (шум), он их отбрасывает. Это позволило нам динамически формировать кластеры на основе реального спроса. Например, алгоритм выявил, что бизнес-центр "Нурлы Тау" и прилегающие ЖК генерируют столько же заказов в пятницу днем, сколько целый спальный микрорайон за неделю. Соответственно, система выделила это в отдельный микро-кластер.

WITH ValidDeliveries AS (
  SELECT
    order_id,
    courier_id,
    ST_GEOGPOINT(longitude, latitude) AS geo_point,
    delivery_timestamp,
    EXTRACT(DAYOFWEEK FROM delivery_timestamp) AS day_of_week,
    TIMESTAMP_DIFF(delivery_timestamp, created_timestamp, MINUTE) AS delivery_time_minutes
  FROM
    `ozat-kz-analytics.logistics.completed_orders`
  WHERE
    delivery_status = 'SUCCESS'
    AND delivery_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    -- Отсекаем GPS-аномалии: берем только точки в радиусе 50 км от центра Алматы
    AND ST_DISTANCE(ST_GEOGPOINT(longitude, latitude), ST_GEOGPOINT(76.9286, 43.2567)) < 50000 
),
ClusteredHotspots AS (
  SELECT
    order_id,
    geo_point,
    day_of_week,
    delivery_time_minutes,
    -- Магия DBSCAN: Кластеризуем точки доставки (радиус 400 метров, минимум 15 заказов)
    -- Партиционируем по дню недели, так как паттерны в будни и выходные разные
    ST_CLUSTERDBSCAN(geo_point, 400, 15) OVER (PARTITION BY day_of_week) AS cluster_id
  FROM
    ValidDeliveries
)
SELECT
  cluster_id,
  day_of_week,
  COUNT(order_id) AS total_orders,
  AVG(delivery_time_minutes) AS avg_delivery_time,
  -- Вычисляем геометрический центр кластера (центроид) для стоянки курьера
  ST_CENTROID_AGG(geo_point) AS cluster_center,
  -- Очерчиваем реальный полигон (границы кластера) вместо статичных зон
  ST_CONVEXHULL(ST_UNION_AGG(geo_point)) AS cluster_polygon
FROM
  ClusteredHotspots
WHERE
  cluster_id IS NOT NULL
GROUP BY
  cluster_id, day_of_week
ORDER BY
  total_orders DESC;

Смотреть код на GitHub (OZAT-kz)

Этот SQL-запрос, выполняющийся за секунды на миллионах строк в инфраструктуре Google Cloud, позволил нам получить реальные центроиды скопления заказов и очертить вокруг них ST_CONVEXHULL (выпуклую оболочку). Теперь у нас были настоящие зоны доставки, меняющиеся в зависимости от дня недели.

Маршрутизация и Задача Коммивояжера (VRP)

Сгруппировав заказы по плотности, мы перешли ко второй части марлезонского балета: построению идеального маршрута внутри каждого кластера. Здесь в игру вступил Google Maps Platform и Routes API (Distance Matrix).

Проблема коммивояжера (Vehicle Routing Problem) — это классическая NP-трудная задача. Нужно найти кратчайший путь для обхода N точек. Мы не могли просто соединить точки прямыми линиями. Мы скармливали координаты всех заказов кластера в Distance Matrix API, который возвращал матрицу реального времени в пути (с учетом пробок, одностороннего движения и текущего трафика).

Полученную матрицу мы обрабатывали с помощью оптимизационного солвера (на базе OR-Tools). Солвер распределял заказы между курьерами так, чтобы минимизировать суммарное время в пути и соблюсти окна доставки (Time Windows).

Результаты: Цифры, которые спасли бюджет

Переход от статичной логистики к динамической гео-маршрутизации через BigQuery GIS дал феноменальные результаты. Система была запущена в тестовую эксплуатацию за 3 месяца и окупила затраты на разработку в первые же полгода работы.

Посмотрим на метрики:

• Холостой пробег снизился с 35% до 12%. Курьеры перестали "возить воздух" на окраинах, так как система балансирует нагрузку на основе реальной плотности кластеров.
• Доставка в срок (SLA) выросла с 68% до 94%. Использование реальной матрицы расстояний (Distance Matrix) вместо расчетов "по прямой" позволило выдавать клиентам точное ETA (ожидаемое время прибытия).
• Экономия топлива: Снижение пробега привело к сокращению затрат на ГСМ на 22% в рамках всего автопарка.

Итоги

Логистика в мегаполисе — это математика. Попытки управлять ею "на глаз" или с помощью маркера на карте обходятся бизнесу слишком дорого. Инструменты геопространственного анализа, такие как BigQuery GIS, открывают совершенно новый уровень понимания бизнеса. Данные лежат у вас под ногами, их просто нужно правильно кластеризовать.

Мы в OZAT глубоко убеждены: каждая казахстанская компания, управляющая собственным автопарком или курьерской службой, должна переходить на Data-Driven рельсы. Если вы хотите узнать больше о том, как мы оптимизируем бизнес-процессы с помощью облачных технологий, изучите наши успешные кейсы или напишите нам. Перестаньте возить воздух — начните возить прибыль!

💡 Совет OZAT: Готовы к внедрению? Рассчитайте архитектуру и бюджет через Scope Builder или пройдите бесплатный ИИ-аудит.