Creación de una tabla de materias de la Federación de Rusia en formato Geografía T-SQL (SQL Server)

En el proceso de preparación de la herramienta para la determinación automática del tema de la Federación de Rusia por punto (tipo de datos Punto), se requirió una tabla del formulario "Asunto de la Federación de Rusia" - "geografía :: Objeto".

Antecedentes: existe una gran flota de vehículos (> 1000 vehículos), que envía sus coordenadas al servidor como parte de los datos "Máquina" - "Hora UTC" - "Geografía :: Punto". El servidor tiene una segunda tabla de eventos específicos del vehículo en los datos "Coche" - "Hora (hora local)" - "Evento". Dos tareas son convertir la hora en la segunda tabla de local a UTC y luego usar ambas tablas para automatizar aún más el análisis de eventos de vehículos en relación con las entidades constituyentes de la Federación de Rusia.

Una búsqueda en Google de la frase "sujetos geojson de la Federación de Rusia" llevó a la página https://gist.github.com/max107/6571147 - enumera temas y listas de puntos de coordenadas - límites en formato JSON.

Si repasa el texto, la estructura de este JSON es la siguiente: en el nivel superior, un bloque, un tema. En el siguiente nivel, bloques numerados del 0 al, al parecer, 19. Esto significa que el tema consta de varias áreas y cada una de ellas es un polígono (polígono) separado. El archivo no contiene Crimea, Sebastopol. Moscú y San Petersburgo no están resaltados en el archivo. Yo mismo pinto Crimea, y el tiempo de Moscú y San Petersburgo no son fundamentales para mis tareas.

Obtuvimos el producto de un trabajo minucioso (muchas gracias al autor de este conjunto de coordenadas y bloques): los polígonos y sus límites. Queda por descubrir cómo analizarlo, lanzarlo al servidor y construir la mesa final.

Lo más probable es que haya formas más sencillas de resolver este problema, pero resolviéndolo por etapas, logramos comprender con más detalle la estructura de los objetos, para probar métodos de trabajo con datos espaciales.

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

La tabla no incluye Crimea y Sebastopol, Moscú y San Petersburgo no están resaltados. Además, algunos límites de sujetos se cruzan un poco o hay pequeños "espacios" de vacíos entre ellos. Esto no es muy crítico para mi tarea y, si es necesario, se puede eliminar especificando las coordenadas y reconstruyendo el valor del formato Geografía.