Creating a table of subjects of the Russian Federation in the Geography T-SQL format (SQL Server)

In the process of preparing the tool for automatic determination of the subject of the Russian Federation by point (data type Point), a table of the form "Subject of the Russian Federation" - "geography :: Object" was required.

Background: there is a large fleet of vehicles (> 1000 vehicles), which sends its coordinates to the server as part of the data "Car" - "UTC time" - "geography :: Point". The server has a second table of specific vehicle events in the data "Car" - "Time (local time)" - "Event". Two tasks are to convert the time in the second table from local to UTC and then use both tables to further automate analytics for vehicle events in relation to the constituent entities of the Russian Federation.

A Google search for the phrase "geojson subjects of the Russian Federation" led to the page https://gist.github.com/max107/6571147 - it lists subjects and lists of coordinate points - boundaries in JSON format.

If you go over the text, then the structure of this JSON is as follows: at the top level, one block - one subject. On the next level down - blocks numbered from 0 to, it seems, 19. This means that the subject consists of several areas and each of them is a separate polygon (polygon). The file does not contain Crimea, Sevastopol. Moscow and St. Petersburg are not highlighted in the file. I will finish the Crimea myself, and Moscow time and St. Petersburg are not fundamental for my tasks.

We got the product of painstaking work (thanks a lot to the author of this array of coordinates and blocks) - polygons and their boundaries. It remains to figure out how to parse it, throw it on the server and construct the final table.

Most likely, there are simpler ways to solve this problem, but solving it in stages, we managed to understand in more detail the structure of objects, to try methods of working with spatial data.

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, .

The table does not include Crimea and Sevastopol, Moscow and St. Petersburg are not highlighted. Also, some boundaries of subjects cross each other a little or there are small "gaps" of voids between them. This is not very critical for my task and, if necessary, can be removed by specifying the coordinates and re-constructing the Geography format value.