Русский
Русский
English
Статистика
Реклама

Multipolygon sql

Создание таблицы субъектов РФ в формате Geography T-SQL (SQL Server)

16.06.2021 18:22:08 | Автор: admin

В процессе подготовки инструмента для автоматического определения субъекта РФ по точке (тип данных Point) потребовалась таблица вида "Субъект РФ" - "geography::Object".

Предыстория: есть большой автопарк (>1000 ТС), который отправляет свои координаты на сервер в составе данных "Машина" - "Момент времени UTC" - "geography::Point". На сервере есть вторая таблица определенных событий по транспортному средству в составе данных "Машина" - "Момент времени (местное время)" - "Событие". Две задачи - перевести время во второй таблице из местного в UTC и далее использовать обе таблицы для дальнейшей автоматизации аналитики по событиям ТС в привязке к субъектам РФ.

Поиск в гугле по фразе "geojson субъекты РФ" привел на страницу https://gist.github.com/max107/6571147 - на ней в формате JSON перечислены субъекты и списки точек координат - границ.

Если вы пробежитесь по тексту, то структура этого JSON такая: на верхнем уровне один блок - один субъект. На следующем уровне вниз - блоки с нумерацией от 0 до, кажется, 19. Это означает, что субъект состоит из нескольких областей и каждая из них - это отдельный полигон (многоугольник). В файле нет Крыма, Севастополя. В файле не выделены Москва и СПб. Крым я дорисую сам, а Мск и СПб для моих задач не принципиальны.

Мы получили продукт кропотливого труда (спасибо большое автору этого массива координат и блоков) - полигоны и их границы. Осталось понять как его разобрать, забросить на сервер и сконструировать финальную таблицу.

Скорее всего есть более простые способы решения этой задачи, но, решая её поэтапно, удалось более детально разобраться в структуре объектов, попробовать методы работы с пространственными данными.

Скопировал текст опубликованного JSON в текстовый файл в блокнот, выбрал кодировку ANSI, сохранил в файл координаты_субъектов_РФ_json.txt

Далее использовал Python и SQL Server Express в двух средах программирования, например (не реклама, это просто одни из множества): PyCharm Community Edition и SSMS. В SSMS на сервере заведем таблицу-приёмник, а в Python разберем текст из файла с JSON, сформируем в цикле текстовую строку для каждого субъекта и забросим на сервер.

Как указал выше: по структуре JSON понимаем, что некоторые из субъектов состоят из нескольких многоугольных областей. Одна область (не субъект, а блок внутри субъекта) соответствует типу пространственных данных Polygon. А несколько связанных областей, поименованных одним названием субъекта - MultiPolygon.

Сконструировать и записать в таблицу объект типа Polygon или Multipoligon можно методами STPolyFromText и STMPolyFromText, которые принимают два аргумента - текст с описанием фигуры и SRID - параметр, который говорит о выбранной системе измерений и координат. Он должен быть в создаваемом объекте таким же, как и в предмете будущего сравнения или иной связанной обработки (в моем случае это таблица с данными в формате geography::Point, наполняемой системой съема GPS-координат транспортного средства). Номер SRID можно получить методом .STSrid. У меня получилось 4326. Значит и субъекты РФ будут сконструированы с этим SRID.

Поскольку областей в рамках одного субъекта может быть много, все их я решил признать мультиполигонами и конструировать инструкцией ... = geography::STMPolyFromText('text', 4326).

Текстовая строка для этого метода должна быть такой: 'MULTIPOLYGON(((список точек границ 1 полигона)), ((список точек границ 2 полигона)), ... , ((список точек границ последнего полигона)))'

Точки указываются парой в формате "долгота пробел широта", список перечисляется через запятую.

Создаем на сервере в БД таблицу-приемник.

CREATE TABLE [dbo].[geozones_RF_subj]([Subj_name] [nvarchar](250) NULL,[Polygon_geo] [geography] NULL,[List_of_coords] [nvarchar](max) NULL,) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
# импортируем модули для работы с JSON и с SQL-serverimport jsonimport pyodbc#подключаемся к базе данных на сервере#используйте название своего сервера и БД#драйвер может отличаться, если версия сервера старшеcnxn = pyodbc.connect(    r'DRIVER={ODBC Driver 17 for SQL Server};'    r'SERVER=LAPTOP-7TOVC7TC\SQLEXPRESS_AMO83;'    r'DATABASE=sandbox;trusted_connection=yes')#объявляем курсорcursor = cnxn.cursor()#открываем файл и забираем содержимое в переменную datawith open(r"D:\координаты_субъектов_РФ_json.txt", "r") as read_file:    data = json.load(read_file)    #переменная data приняла словарь субъектов из вложенных словарей - полигонов,#вложеные словари - списки пар координат границ#цикл перебора словаря субъектовfor i in data.keys():    str_: str = 'MULTIPOLYGON('  # пишем в переменную стартовое слово          # вложенный цикл перебора словарей - полигонов    for j in data[i].keys():        str_ = str_ + '(('  # описание полигона начинается двумя скобками                # вложенный цикл перебора списков - пар координат точек границ полигона        for k in range(len(data[i][j])):            lat_ = str(data[i][j][k][1])  # широта указана на втором месте в паре            lon_ = str(data[i][j][k][0])  # долгота указана на первом месте в паре                    # описание полигона должно заканчиваться одинаковой парой координат        # в файле это не так, поэтому сохраним первую пару и потом добавим        # эту пару как завершающую перед закрытием полигона        if k == 0:            lat_beg = lat_            lon_beg = lon_                    # добавляем к строке пару координат и запятую на случай начала описания        if str_[-2:] == '((':            str_ = str_ + lat_ + ' ' + lon_ + ', '        # добавляем к строке пару координат        # и завершающую пару координат если это окончание описания полигона        if k == len(data[i][j]) - 1:            str_ = str_ + lat_ + ' ' + lon_ + ', ' + lat_beg + ' ' + lon_beg + '))'        # добавляем к строке пару координат и запятую, если это не начало        # и не конец описания, можно соединить с первым IF и вписать в блок else        # второго IF, но так детальнее        if str_[-2:] != '((' and k != len(data[i][j]) - 1:            str_ = str_ + lat_ + ' ' + lon_ + ', '    # если это не последний полигон субъекта - ставим запятую    if int(j) < (len(data[i]) - 1):        str_ = str_ + ', '# завершаем скобкой мультиполигон (субъект РФ)str_ = str_ + ')'# пишем SQL-инструкцию для добавления строки в таблицу,# добавляем только название субъекта в первый стобец# и полученную текстовую строку описания - в третий столбецcomm: str = 'INSERT INTO sandbox.dbo.geozones_RF_subj VALUES(' + \            "'" + i + "'" + ', NULL, ' + "'" + str_ + "'" + ')'# запускаем SQL-инструкцию на сервереcursor.execute(comm)#  записываем изменение таблицы (новую добавленную строку)cnxn.commit()#закрываем соединение с серверомcnxn.close()

В итоге получаем таблицу - заготовку для конструирования соответствующего субъекту РФ значения в формате Geography

Таблица с текстовыми описаниями мультиполигонов субъектов РФТаблица с текстовыми описаниями мультиполигонов субъектов РФ

Заполним значениями столбец Polygon_geo предварительными значениями

UPDATE [sandbox].[dbo].[geozones_RF_subj] SET Polygon_geo = geography::STMPolyFromText(List_of_coords, 4326)

Некоторые субъекты получились инвертированными - т.е. они состоят из всего земного шара, за исключением самого субъекта РФ. Также некоторые субъекты получились некорректными. Проверяется методом .STIsValid()

Определение правильных и неправильных объектов типа GeographyОпределение правильных и неправильных объектов типа Geography

Инвертированный объект видно на вкладке Spatial results - он будет белым, а вся остальная область - цветная.

Инвертированный объект на вкладке Spatial resultsИнвертированный объект на вкладке Spatial results

Для того, чтобы исправить обе проблемы: "неправильность" и инвертированность - сначала применим метод MakeValid() и перезапишем столбец Polygon_geo

UPDATE [sandbox].[dbo].[geozones_RF_subj] SET Polygon_geo=Polygon_geo.MakeValid()

Затем инвертируем те значения, площадь которых более 500 млн. км2

UPDATE [sandbox].[dbo].[geozones_RF_subj] SET Polygon_geo=Polygon_geo.ReorientObject() where Polygon_geo.STArea()/1000000>500000000

Результат: таблица субъектов РФ в формате geography::MiltiPolygon, которая готова служить для определения наименования субъекта РФ и часового пояса по координатам геопозиции объекта.

Готовая таблица субъектов РФ в формате Geography, вывод на вкладке Spatial ResultsГотовая таблица субъектов РФ в формате Geography, вывод на вкладке Spatial Results

В таблице нет Крыма и Севастополя, не выделены Москва и СПб. Также некоторые границы субъектов немного друг друга пересекают или между ними есть небольшие "щели" пустоты. Это не очень критично для моей задачи и при необходимости можно убрать путем уточнения координат и повторного конструирования значения формата Geography.

Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru