Membuat tabel mata pelajaran Federasi Rusia dalam format Geografi T-SQL (SQL Server)

Dalam proses menyiapkan alat untuk penentuan otomatis subjek Federasi Rusia berdasarkan poin (Tipe tipe data), diperlukan tabel formulir "Subjek Federasi Rusia" - "geografi :: Objek".

Latar Belakang: ada armada kendaraan yang besar (> 1000 kendaraan), yang mengirimkan koordinatnya ke server sebagai bagian dari data "Mesin" - "Waktu UTC" - "geografi :: Titik". Server memiliki tabel kedua peristiwa kendaraan tertentu dalam data "Mobil" - "Waktu (waktu setempat)" - "Acara". Dua tugas adalah mengonversi waktu di tabel kedua dari lokal ke UTC dan kemudian menggunakan kedua tabel untuk lebih mengotomatiskan analitik untuk peristiwa kendaraan dalam kaitannya dengan entitas konstituen Federasi Rusia.

Pencarian Google untuk frasa "subyek geojson dari Federasi Rusia" mengarah ke halaman https://Gist.github.com/max107/6571147 - ini mencantumkan subjek dan daftar titik koordinat - batas dalam format JSON.

Jika Anda melihat teks, maka struktur JSON ini adalah sebagai berikut: di tingkat atas, satu blok - satu subjek. Pada tingkat berikutnya ke bawah - blok bernomor dari 0 hingga, tampaknya, 19. Ini berarti bahwa subjek terdiri dari beberapa area dan masing-masing adalah poligon (poligon) yang terpisah. File tidak mengandung Crimea, Sevastopol. Moskow dan St. Petersburg tidak disorot dalam file. Saya melukis Krimea sendiri, dan waktu Moskow dan Sankt Peterburg tidak penting untuk tugas saya.

Kami mendapatkan produk dari kerja keras (terima kasih banyak kepada penulis susunan koordinat dan blok ini) - poligon dan batasnya. Masih mencari cara untuk menguraikannya, membuangnya ke server dan membuat tabel final.

Kemungkinan besar ada cara yang lebih sederhana untuk menyelesaikan masalah ini, tetapi menyelesaikannya secara bertahap, kami berhasil memahami lebih detail struktur objek, untuk mencoba metode bekerja dengan data spasial.

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

Tabel tidak termasuk Krimea dan Sevastopol, Moskow dan St. Petersburg tidak disorot. Juga, beberapa batas subjek saling bersilangan sedikit atau ada "celah" kecil di antara mereka. Ini tidak terlalu penting untuk tugas saya dan, jika perlu, dapat dihapus dengan menentukan koordinat dan menyusun ulang nilai format Geografi.