В процессе подготовки инструмента для автоматического определения субъекта РФ по точке (тип данных Point) потребовалась таблица вида "Субъект РФ" - "geography::Object".
Предыстория: есть большой автопарк (>1000 ТС), который отправляет свои координаты на сервер в составе данных "Машина" - "Момент времени UTC" - "geography:
oint". На сервере есть вторая таблица определенных событий по транспортному средству в составе данных "Машина" - "Момент времени (местное время)" - "Событие". Две задачи - перевести время во второй таблице из местного в 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:oint, наполняемой системой съема 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.keys():
str_ = str_ + '((' # описание полигона начинается двумя скобками
# вложенный цикл перебора списков - пар координат точек границ полигона
for k in range(len(data[j])):
lat_ = str(data[j][k][1]) # широта указана на втором месте в паре
lon_ = str(data[j][k][0]) # долгота указана на первом месте в паре
# описание полигона должно заканчиваться одинаковой парой координат
# в файле это не так, поэтому сохраним первую пару и потом добавим
# эту пару как завершающую перед закрытием полигона
if k == 0:
lat_beg = lat_
lon_beg = lon_
# добавляем к строке пару координат и запятую на случай начала описания
if str_[-2:] == '((':
str_ = str_ + lat_ + ' ' + lon_ + ', '
# добавляем к строке пару координат
# и завершающую пару координат если это окончание описания полигона
if k == len(data[j]) - 1:
str_ = str_ + lat_ + ' ' + lon_ + ', ' + lat_beg + ' ' + lon_beg + '))'
# добавляем к строке пару координат и запятую, если это не начало
# и не конец описания, можно соединить с первым IF и вписать в блок else
# второго IF, но так детальнее
if str_[-2:] != '((' and k != len(data[j]) - 1:
str_ = str_ + lat_ + ' ' + lon_ + ', '
# если это не последний полигон субъекта - ставим запятую
if int(j) < (len(data) - 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.
Источник статьи: https://habr.com/ru/post/563110/
Предыстория: есть большой автопарк (>1000 ТС), который отправляет свои координаты на сервер в составе данных "Машина" - "Момент времени UTC" - "geography:
oint". На сервере есть вторая таблица определенных событий по транспортному средству в составе данных "Машина" - "Момент времени (местное время)" - "Событие". Две задачи - перевести время во второй таблице из местного в 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:
oint, наполняемой системой съема 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.keys():
str_ = str_ + '((' # описание полигона начинается двумя скобками
# вложенный цикл перебора списков - пар координат точек границ полигона
for k in range(len(data[j])):
lat_ = str(data[j][k][1]) # широта указана на втором месте в паре
lon_ = str(data[j][k][0]) # долгота указана на первом месте в паре
# описание полигона должно заканчиваться одинаковой парой координат
# в файле это не так, поэтому сохраним первую пару и потом добавим
# эту пару как завершающую перед закрытием полигона
if k == 0:
lat_beg = lat_
lon_beg = lon_
# добавляем к строке пару координат и запятую на случай начала описания
if str_[-2:] == '((':
str_ = str_ + lat_ + ' ' + lon_ + ', '
# добавляем к строке пару координат
# и завершающую пару координат если это окончание описания полигона
if k == len(data[j]) - 1:
str_ = str_ + lat_ + ' ' + lon_ + ', ' + lat_beg + ' ' + lon_beg + '))'
# добавляем к строке пару координат и запятую, если это не начало
# и не конец описания, можно соединить с первым IF и вписать в блок else
# второго IF, но так детальнее
if str_[-2:] != '((' and k != len(data[j]) - 1:
str_ = str_ + lat_ + ' ' + lon_ + ', '
# если это не последний полигон субъекта - ставим запятую
if int(j) < (len(data) - 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.
Источник статьи: https://habr.com/ru/post/563110/