Création d'un tableau des sujets de la Fédération de Russie au format Geography T-SQL (SQL Server)

Lors de la préparation de l'outil de détermination automatique du sujet de la Fédération de Russie par point (type de données Point), un tableau du formulaire "Sujet de la Fédération de Russie" - "géographie :: Objet" était requis.

Contexte : il existe une grande flotte de véhicules (> 1000 véhicules), qui envoie ses coordonnées au serveur dans le cadre des données "Machine" - "Heure UTC" - "Géographie :: Point". Le serveur dispose d'un deuxième tableau d'événements spécifiques au véhicule dans les données « Voiture » ​​- « Heure (heure locale) » - « Événement ». Deux tâches consistent à convertir l'heure dans la deuxième table de local à UTC, puis à utiliser les deux tables pour automatiser davantage l'analyse des événements de véhicule en relation avec les entités constitutives de la Fédération de Russie.

Une recherche Google de l'expression "sujets géojson de la Fédération de Russie" a conduit à la page https://gist.github.com/max107/6571147 - elle répertorie les sujets et les listes de points de coordonnées - limites au format JSON.

Si vous parcourez le texte, la structure de ce JSON est la suivante : au niveau supérieur, un bloc - un sujet. Au niveau suivant - des blocs numérotés de 0 à, semble-t-il, 19. Cela signifie que le sujet se compose de plusieurs zones et chacune d'elles est un polygone séparé (polygone). Le fichier ne contient pas la Crimée, Sébastopol. Moscou et Saint-Pétersbourg ne sont pas mis en évidence dans le fichier. Je peins moi-même la Crimée, et l'heure de Moscou et de Saint-Pétersbourg ne sont pas fondamentales pour mes tâches.

Nous avons obtenu le produit d'un travail minutieux (merci beaucoup à l'auteur de ce tableau de coordonnées et de blocs) - les polygones et leurs limites. Il reste à comprendre comment l'analyser, le lancer sur le serveur et construire la table finale.

Il existe très probablement des moyens plus simples de résoudre ce problème, mais en le résolvant par étapes, nous avons réussi à comprendre plus en détail la structure des objets, à essayer des méthodes de travail avec des données spatiales.

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

Le tableau n'inclut pas la Crimée et Sébastopol, Moscou et Saint-Pétersbourg ne sont pas mis en évidence. De plus, certaines limites de sujets se croisent un peu ou il y a de petits "espaces" de vides entre eux. Ce n'est pas très critique pour ma tâche et, si nécessaire, peut être supprimé en spécifiant les coordonnées et en reconstruisant la valeur du format Géographie.