Квазигеофильтрация в PowerPivot

Бизнес-сценарий использования на примере радиуса поиска

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

Эта реализация есть в моем примере анализа вторичного рынка автомобилей в Поволжье по сайту бесплатных объявлений.

Интерфейсно в PowerView это выглядит следующим образом:

GeoFiltering1

 

GeoFiltering2

Пользователь выбирает фильтром Мой город (например, Киров), в каталоге марок автомобилей выбирает конкретную марку (например, Audi). В области фильтров движком задается предельный радиус поиска объявлений о продаже автомобилей, общее количество объявлений отображается на карте размером кружка. Внизу на графике показана средняя цена на данную  марку в разрезе по всем городам. Такой подход к фильтрации заменяет привычный множественный фильтр и часто используется на зарубежных автосайтах, но почему-то игнорируется отечественными. Для потребителя же важно увидеть объявления из нескольких регионов, находящихся в разных федеральных округах, но в пределах досягаемости. Не каждый из Кирова поедет за интересным предложением в Краснодар, но в Казань (ПФО), Сыктывкар (СЗФО) или Кострому (ЦФО) ездят часто. По рисунку видно, что в радиусе 750 км наибольшее количество предложений в Кирове, Перми, Нижнем Новгороде и Казани. При этом наиболее низкая средняя цена по рынку из приведенного списка городов в Кирове. Такой подход существенно упрощает задачу по экспресс-мониторингу рынка.

Техническая реализация в PowerPivot и PowerQuery

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

GeoFiltering3

Дальше необходимо отменить свертывание таблицы в терминах PowerQuery (операция unpivot) и заполнить пары расстояний (Город1 – Город 2; Город 2 – Город 1). Эти операции удобно выполнить в PowerQuery:

GeoFiltering4

В результате матрица расстояний преобразуется к такому виду:

GeoFiltering5

Как видно из схемы модели PowerPivot, поле Мой город таблицы Расстояния выносится в фильтр отчета, а связь с таблицей фактов Предложения осуществляется через промежуточное измерение Города для реализации связи многие-ко-многим, поскольку поле Город поиска таблицы Расстояния дублируется.

GeoFiltering6

Важно отметить, что для правильного вычисления с учетом устанавливаемых фильтров вычисляемая мера Кол-во предложений расположена в таблице Расстояния, именно она используется для отображения в отчете.

Как видно из примера, интересное интерфейсное решение прикладной задачи по геофильтрации данных имеет простое решение на PowerPivot и PowerQuery.

Один ответ на Квазигеофильтрация в PowerPivot

  1.  

    Это реально круто!

leave your comment


− четыре = 2