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

В процессе подготовки инструмента для автоматического определения субъекта РФ по точке (тип данных 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-server import json import 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()  #открываем файл и забираем содержимое в переменную data with 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()

Инвертированный объект видно на вкладке 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.