• Python
• Microsoft 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.