В процессе подготовки инструмента для автоматического определения субъекта РФ по точке (тип данных 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Инвертированный объект видно на вкладке 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.