Рвав-рвав, собака Смайл нынче землемер!
Собака Смайл все глубже погружается в процесс создания пользовательских функций, а поскольку теория без практики мертва, стараемся сразу подыскать им практическое применение. Поэтому сегодня мы рассмотрим такую задачу, как определение расстояния по координатам.
Вообще надо сказать, что, пока обдумывал материал в голове, что отделаюсь "легким испугом", типа: ну вот будет у меня 1-я точка как 0, и вторая как 25, чего не посчитать-то? А вот фига, все оказалось куда интереснее. При том то, что удалось нарыть, можно еще и оптимизировать дополнительно.
Демонстрационный пример
Таблица "Маршруты" содержит название маршрута, а также координаты начальной и конечной точки каждого маршрута:
![](https://static.tildacdn.com/tild3764-6563-4666-b635-363565656261/izmerenie-rasstoyani.png)
Задача
На основе представленных данных необходимо определить расстояние между 2-мя точками маршрута, используя два уровня вычисления, а именно Power Query и DAX.
Уровень Power Query
Поскольку наша задача связана с некоторыми специфическими вычислениями, придется вспоминать такую науку, как "Тригонометрия".
Для решения поставленной задачи при помощи Power Query необходимо последовательно выполнить следующие шаги:
- Всем географическим координатам необходимо присвоить правильные типы данных ("Десятичное число"):
![](https://static.tildacdn.com/tild6538-6261-4538-b964-343261313362/izmerenie-rasstoyani.png)
Рвав-рвав, а вот дальше начинается самое интересное!
Оказывается, нельзя просто так взять координаты, и получить нормальный результат точки старта и точки финиша. И планета наша не шар, а эллипс, и расчеты в зависимости от единиц измерения разные...
Подробные пояснения этого всего от скромного собакена здесь не ждите, если есть соответствующее желание, то Гугл вам поможет :-)
- Далее, для начала расчета, необходимо перевести заданные координаты в радианы. Можно было создать еще 4 дополнительных столбца, и вписать в них данные вручную, но лучше написать следующую функцию с незамысловатым названием:
![](https://static.tildacdn.com/tild6138-3038-4834-a332-613462383363/izmerenie-rasstoyani.png)
Код созданной пользовательской функции "RadiansCoordinates" весьма простой:
(Radians) =>
Radians / 180 * Number.PI
- Применив указанную функцию к нашим данным, и затем изменив тип данных в столбцах, мы получим искомые значения:
![](https://static.tildacdn.com/tild6462-3039-4639-b262-626135343161/izmerenie-rasstoyani.png)
- После осуществления пересчета в качестве последнего шага необходимо добавить настраиваемый столбец, нажав соответствующую кнопку, расположенную на закладке "Добавление столбца":
- Создаваемый настраиваемый столбец "Расстояние в километрах" имеет следующую формулу:
Number.Acos(
Number.Sin([#"Широта - старт"])
* Number.Sin([#"Широта - финиш"]) + Number.Cos([#"Широта - старт"])
* Number.Cos([#"Широта - финиш"])
* Number.Cos([#"Долгота - финиш"] - [#"Долгота - старт"])
)
* 6371
Рвав-рвав, на всякий случай: значение "6371" – это общепринятый радиус нашей планеты в километрах.
- Последним шагом решения задачи на уровне Power Query является изменение типа данных в указанном столбце:
![](https://static.tildacdn.com/tild6565-6338-4165-b639-306338363861/izmerenie-rasstoyani.png)
Уровень DAX
Решение, используемое на уровне DAX, гораздо короче, но, естественно, не проще.
Для решения поставленной задачи при помощи DAX необходимо последовательно выполнить следующие шаги:
- Создать вычисляемый столбец "Расстояние в километрах", используя следующую формулу:
Расстояние в километрах =
VAR _NumberPI = 3.141592653589794
VAR _ValueDegrees = 180
VAR _CorrectionFactor =
DIVIDE ( _NumberPI, _ValueDegrees, 0 )
VAR _RadiusEarth = 6371 //Общепринятая веричина радиуса Земли в километрах, для расчета в милях нужно использовать 3959
RETURN
ACOS (
SIN ( 'Маршруты DAX'[Широта - старт] * _CorrectionFactor )
* SIN ( 'Маршруты DAX'[Широта - финиш] * _CorrectionFactor )
+ COS ( 'Маршруты DAX'[Широта - старт] * _CorrectionFactor )
* COS ( 'Маршруты DAX'[Широта - финиш] * _CorrectionFactor )
* COS ( ( 'Маршруты DAX'[Долгота - финиш] * _CorrectionFactor ) - ( 'Маршруты DAX'[Долгота - старт] * 0.0174532925199433 ) )
) * _RadiusEarth
- Проверить получившийся результат:
![](https://static.tildacdn.com/tild3162-3830-4366-b238-343031663662/izmerenie-rasstoyani.png)
Независимая проверка
Для итоговой проверки можно воспользоваться каким-нибудь интернет-сервисом:
![](https://static.tildacdn.com/tild3733-3261-4036-a132-356233333063/izmerenie-rasstoyani.png)
Расстояние, равное 635-ти километрам, соответствует полученным нами результатам.
Рвав-рвав, вот такие дела.
При промышленном применении данного функционала нужно иметь ввиду, как минимум, следующие моменты: расстояние считается по прямой, и маршрут по городам из примера "Санкт-Петербург → Москва" на самом деле несколько не верен, поскольку его величина рассчитывается от Дворцовой площади до Красной площади.Ваш Смайл