Перейти к основному содержимому
Перейти к основному содержимому

Геоданные с использованием набора данных о вышках сотовой связи

Цель​

В этом руководстве вы узнаете, как:

  • Загрузить данные OpenCelliD в ClickHouse
  • Подключить Apache Superset к ClickHouse
  • Создать панель управления на основе данных, доступных в наборе данных

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

Панель управления вышками сотовой связи по типу радио в mcc 204

Получение набора данных​

Этот набор данных предоставлен OpenCelliD - крупнейшей в мире открытой базой данных вышек сотовой связи.

На 2021 год он содержит более 40 миллионов записей о вышках сотовой связи (GSM, LTE, UMTS и т. д.) по всему миру с их географическими координатами и метаданными (код страны, сеть и т. д.).

Проект OpenCelliD лицензирован по лицензии Creative Commons Attribution-ShareAlike 4.0 International License, и мы повторно распространяем снимок этого набора данных на условиях той же лицензии. Актуальная версия набора данных доступна для загрузки после входа в систему.

Загрузить образец данных​

ClickHouse Cloud предоставляет простую кнопку для загрузки этого набора данных из S3. Войдите в вашу организацию ClickHouse Cloud или создайте бесплатную пробную версию на ClickHouse.cloud.

Выберите вашу службу, затем выберите Data souces -> Predefined sample data.

Меню действий облачной службы

Выберите набор данных Вышки сотовой связи на вкладке Образцы данных, и нажмите Загрузить данные:

Загрузить набор данных о вышках сотовой связи

Изучите схему таблицы cell_towers​

SQL консоль

Если вам нужно подключение клиента SQL, ваша служба ClickHouse Cloud имеет ассоциированную веб-основу SQL консоли; разверните Подключиться к SQL консоли ниже для подробностей.

Подключиться к SQL консоли

Из вашего списка служб ClickHouse Cloud нажмите на службу.

Подключиться к SQL консоли

Это перенаправит вас в SQL консоль.

SQL консоль

Это вывод команды DESCRIBE. Ниже в этом руководстве будут описаны варианты типов полей.

Выполните несколько примерных запросов​

  1. Число вышек сотовой связи по типу:
  1. Вышки сотовой связи по кодам мобильных стран (MCC):

На основании вышеизложенного запроса и списка MCC, страны с наибольшим количеством вышек сотовой связи: США, Германия и Россия.

Вы можете создать Словарь в ClickHouse, чтобы декодировать эти значения.

Случай использования: Включение геоданных​

Используя функцию pointInPolygon.

  1. Создайте таблицу, в которой мы будем хранить полигоны:
  1. Это грубая форма Москвы (без "новой Москвы"):
  1. Проверьте, сколько вышек сотовой связи в Москве:

Обзор схемы​

Перед созданием визуализаций в Superset обратите внимание на колонки, которые вы будете использовать. Этот набор данных в основном предоставляет местоположение (долгота и широта) и типы радиосигналов на мобильных вышках по всему миру. Описания колонок можно найти на форуме сообщества. Колонки, используемые в визуализациях, которые будут созданы, описаны ниже.

Вот описание колонок, взятых с форума OpenCelliD:

КолонкаОписание
radioПоколение технологии: CDMA, GSM, UMTS, 5G NR
mccКод страны мобильной связи: 204 - Нидерланды
lonДолгота: с широтой, примерно местоположение вышки
latШирота: с долготой, примерно местоположение вышки
mcc

Чтобы найти свой MCC, проверьте Коды мобильных сетей и используйте три цифры в колонке Код страны мобильной связи.

Схема этой таблицы была спроектирована для компактного хранения на диске и скорости запроса.

  • Данные radio хранятся как Enum8 (UInt8), а не как строка.
  • mcc или код мобильной страны хранится как UInt16, так как мы знаем, что диапазон составляет от 1 до 999.
  • lon и lat хранятся как Float64.

Никакие другие поля не используются в запросах или визуализациях в этом руководстве, но они описаны на упомянутом выше форуме, если вас это интересует.

Создание визуализаций с помощью Apache Superset​

Superset легко запускать из Docker. Если у вас уже запущен Superset, все, что вам нужно сделать, это добавить ClickHouse Connect с помощью pip install clickhouse-connect. Если вам нужно установить Superset, откройте Запустите Apache Superset в Docker прямо ниже.

Запуск Apache Superset в Docker

Superset предоставляет инструкции по установке Superset локально с использованием Docker Compose. После того как вы склонируете репозиторий Apache Superset с GitHub, вы можете запустить последний код разработки или конкретный тег. Мы рекомендуем релиз 2.0.0, так как это последний релиз, не помеченный как pre-release.

Перед запуском docker compose необходимо выполнить несколько задач:

  1. Добавить официальный драйвер ClickHouse Connect
  2. Получить ключ API Mapbox и добавить его в качестве переменной окружения (по желанию)
  3. Указать версию Superset для запуска
подсказка

Команды ниже должны выполняться из корневой директории репозитория GitHub, superset.

Официальный драйвер ClickHouse Connect​

Чтобы сделать драйвер ClickHouse Connect доступным в развертывании Superset, добавьте его в локальный файл зависимостей:

Mapbox​

Это необязательно, вы можете отображать данные о местоположении в Superset без ключа API Mapbox, но вы получите сообщение с предложением добавить ключ, и фоновое изображение карты будет отсутствовать (вы увидите только точки данных, а не фоновую карту). Mapbox предоставляет бесплатный тариф, если вы хотите его использовать.

Некоторые из образцов визуализаций, которые предлагают создать руководства, используют данные о местоположении, например долготу и широту. Superset включает поддержку карт Mapbox. Чтобы использовать визуализации Mapbox, вам нужен ключ API Mapbox. Зарегистрируйтесь для получения бесплатного тарифа Mapbox и сгенерируйте ключ API.

Сделайте ключ API доступным для Superset:

Развертывание Superset версии 2.0.0​

Чтобы развернуть релиз 2.0.0, выполните:

Чтобы создать панель управления Superset, используя набор данных OpenCelliD, вам нужно:

  • Добавить вашу службу ClickHouse как базу данных Superset
  • Добавить таблицу cell_towers как набор данных Superset
  • Создать несколько диаграмм
  • Добавить диаграммы на панель управления

Добавьте вашу службу ClickHouse как базу данных Superset​

Чтобы подключиться к ClickHouse с помощью HTTP(S), вам нужна следующая информация:

  • ХОСТ и ПОРТ: обычно порт 8443 при использовании TLS или 8123 при его отсутствии.

  • НАЗВАНИЕ БАЗЫ ДАННЫХ: по умолчанию есть база данных с именем default, используйте имя базы данных, к которой вы хотите подключиться.

  • ИМЯ ПОЛЬЗОВАТЕЛЯ и ПАРОЛЬ: по умолчанию имя пользователя default. Используйте имя пользователя, подходящее для вашего случая использования.

Данные для вашей службы ClickHouse Cloud доступны в консоли ClickHouse Cloud. Выберите службу, к которой вы будете подключаться, и нажмите Подключиться:

Кнопка подключения к службе ClickHouse Cloud

Выберите HTTPS, и детали будут доступны в примере команды curl.

Детали подключения ClickHouse Cloud через HTTPS

Если вы используете self-managed ClickHouse, детали подключения задаются вашим администратором ClickHouse.

В Superset базу данных можно добавить, выбрав тип базы данных, а затем предоставив данные подключения. Откройте Superset и найдите +, у него есть меню с вариантами Данные и Подключить базу данных.

Добавл�ение базы данных

Выберите ClickHouse Connect из списка:

Выбор clickhouse connect как типа базы данных

примечание

Если ClickHouse Connect не является одним из ваших вариантов, то вам нужно будет установить его. Команда для этого: pip install clickhouse-connect, и более подробная информация доступна здесь.

Добавьте ваши данные подключения:​

подсказка

Убедитесь, что вы включили SSL при подключении к ClickHouse Cloud или другим системам ClickHouse, которые требуют использования SSL.

Добавить ClickHouse как источник данных Superset

Добавьте таблицу cell_towers как набор данных Superset​

В Superset набор данных соответствует таблице внутри базы данных. Щелкните "Добавить набор данных" и выберите вашу службу ClickHouse, базу данных, содержащую вашу таблицу (default) и выберите таблицу cell_towers:

Добавить таблицу cell_towers как набор данных

Создайте несколько диаграмм​

Когда вы решите добавить диаграмму в Superset, вам нужно будет указать набор данных (cell_towers) и тип диаграммы. Поскольку набор данных OpenCelliD предоставляет координаты долготы и широты для вышек сотовой связи, мы создадим диаграмму Карта. Тип deck.gl Scatterplot подходит для этого набора данных, так как он хорошо работает с плотными точками данных на карте.

Создать карту в Superset

Укажите запрос, используемый для карты​

Декартовская диаграмма требует долготы и широты, также можно применить один или несколько фильтров к запросу. В этом примере применяются два фильтра: один для вышек с UMTS радиосигналами и один для кода мобильной страны, назначенного Нидерландам.

Поля lon и lat содержат долготу и широту:

Укажите поля долготы и широты

Добавьте фильтр с mcc = 204 (или замените на любое другое значение mcc):

Фильтр для MCC 204

Добавьте фильтр с radio = 'UMTS' (или замените на любое другое значение radio, можно увидеть варианты в выводе DESCRIBE TABLE cell_towers):

Фильтр по радио = UMTS

Это полная конфигурация для диаграммы, фильтрующей по radio = 'UMTS' и mcc = 204:

Диаграмма для UMTS радиосигналов в MCC 204

Нажмите ОБНОВИТЬ ДИАГРАММУ, чтобы отобразить визуализацию.

Добавьте диаграммы на панель управления​

Этот скриншот показывает местоположение вышек сотовой связи с LTE, UMTS и GSM радиосигналами. Все диаграммы созданы одинаковым образом и добавлены на панель управления.

Панель управления вышками сотовой связи по типу радио в mcc 204

подсказка

Данные также доступны для интерактивных запросов в Playground.

Этот пример заполнит имя пользователя и даже запрос за вас.

Хотя вы не можете создавать таблицы в Playground, вы можете выполнять все запросы и даже использовать Superset (откорректируйте имя хоста и номер порта).