Excel/PowerQuery/VBA, SQL, Анализ данных

Невидимый во времени маршрут

Время прочтения: 4 мин.

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

Таким, образом, условная база данных формирует сеть и огромное количество информации. Следовательно, такие базы данных требуют анализ и обработку с помощью различного программного обеспечения. В данном случае мы будем использовать функционал языка запросов SQL (Structure Query Language), Microsoft – VBA (Visual Basic for Application), Microsoft Office Excel.

Естественно любая техника под управлением программы или человека может совершать ошибки при работе и соответственно возникает потеря сигнала на маршруте.  При аномальных стечениях обстоятельств аудиторам требуется найти подобные ситуации и выяснить причины их возникновения. Все маршруты передвижений спец. автомобилей и их координаты заранее известны и занесены в базу. Исходя из маршрута, где произошла потеря связи, рассчитываем с помощью Яндекс-карты координаты долготы и широты зоны отсутствия сигналов. Вместе с тем построить по точкам долготы и широты все пути потери маршрута. Зная эти точки можно выяснить, были трудности у других ТС в этих координатах. Существовала ли в определенной местности подобная проблема с приемом и передачей данных о положении транспорта.

По этой причине мы создали запрос по маршрутам и, получив их, осуществили обработку данных. Каждый маршрут был разбит на n-ое количество отрезков по которым мы получали координаты маршрута. Долгота и широта была выбрана из URL ссылки, путем вырезания определенных участков фраз ссылки. При помощи Excel мы произвели расчеты по полученным данным и рассчитали расстояние между контрольными точками.

=ATAN2(SIN(D3*ПИ()/180)*SIN(D2*ПИ()/180)+COS(D3*ПИ()/180)*COS(D2*ПИ()/180)*COS(C2*ПИ()/180-C3*ПИ()/180);КОРЕНЬ(COS(D2*ПИ()/180)*SIN(C2*ПИ()/180-C3*ПИ()/180)*COS(D2*ПИ()/180)*SIN(C2*ПИ()/180-C3*ПИ()/180)+(COS(D3*ПИ()/180)*SIN(D2*ПИ()/180)-SIN(D3*ПИ()/180)*COS(D2*ПИ()/180)*COS(C2*ПИ()/180-C3*ПИ()/180))*(COS(D3*ПИ()/180)*SIN(D2*ПИ()/180)-SIN(D3*ПИ()/180)*COS(D2*ПИ()/180)*COS(C2*ПИ()/180-C3*ПИ()/180))))*6372795

Формула для расчета расстояния между точками.

Рис. 1 Ключевые ячейки, участвующие в расчете

Это производилось для проверки на среднее отклонение наших предположений от предполагаемого маршрута.  Дальнейшая информация помещалась в переменную SQL для формирования запроса.

Select  m.[time],
m.[id], 
m.[CarNumber]
m.[x2],
m.[y2]
m.[Latitude],
m.[Latitude],
From [qwe]. GpsHistory m, [qwe].Devices m
select top(0) '22.1' as [route], 1 as [point], m.Latitude, m.Longitude, 5000 as [rad], 100 as [CountPoint]
 into #Signal Loss
 From [qwe].GpsHistory m
Insert Into #SignalLoss ([route], [point], Longitude, Latitude, [rad], [CountPoint]) Values 
('1', 1, 78.369439, 56.350901, 1000, 40), ('1', 2, 78.354329, 56.346517, 1000, 40), ('1', 3, 78.345715, 56.338579, 1000, 40), ('1', 4, 78.335454, 56.331198, 1000, 40)
('1', 5, 78.325202, 56.325570, 1000, 40), ('1', 6, 78.311239, 56.319585, 1000, 40), ('1', 7, 78.297662, 56.313598, 1000, 40), ('1', 8, 78.294971, 56.306601, 1000, 40)
('1', 9, 78.29 и5784, 56.298006, 1000, 40), ('1', 10, 78.301307, 56.289027, 1000, 40), ('1', 11, 78.302309, 56.280721, 1000, 40), ('1', 12, 78.319057, 56.276830, 1000, 40)

Пример SQL запроса по точкам координат.

Далее осуществлялась обработка алгоритма в программном обеспечении VBA. Алгоритм создавал Excel-файл и заполнял его:

  1. Номер машины,
  2. Номер маршрута,
  3. Количество контрольных точек на маршруте,
  4. Количество подачи сигнала от автомобиля в определенном радиусе.

В данном запросе было проанализировано 49 маршрутов в разных регионах Российской Федерации. Среднее расстояние маршрута составляет 75 км. с средним радиусом в 3 км. в каждой контрольной точке.

В результате вычисления алгоритма мы получили таблицу связи «Транспорт – база» (Рис. 2).

Рис. 2. Результат вычисления транспорта на каждом отрезке маршрута.

На основание расчета по таблице для каждого маршрута были выстроены графики (Рисунок 6), показывающие аномалию прохождение пути. Проанализировав возможные потери сигнала на отрезках, рассматривая связи машин с базовой станцией, можно выявлять возможную неисправность аппарата.

Рис. 3. График прохождения транспорта по маршруту в зоне потери сигнала.

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

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

Советуем почитать