我有一個 Sqlite 數據庫，其中包含一個包含地理列的表。當我將此表作為圖層添加到 QGIS 中時，它顯示了帶有多邊形的芝加哥地圖，如下所示。我認為，多邊形點存儲在名為 geo 的列中。https://i.stack.imgur.com/ZZvh2.png 我正在嘗試在 Python 中繪制相同的內容，以便能夠使用 Matplotlib 在此布局之上添加更多內容。首先，我可以使用以下代碼（我編寫的）在 Python 中加載名為“Zone”的表：import sqlite3 # Package for SQLite### BEGIN DEFINING A READER FUNCTION ###def Conditional_Sqdb_reader(Sqdb,Tablename,Columns,Condition): conn = sqlite3.connect(Sqdb) # Connects the file to Python print("\nConnected to %s.\n"%(Sqdb)) conn.execute('pragma foreign_keys = off') # Allows making changes into the SQLite file print("SQLite Foreign_keys are unlocked...\n") c = conn.cursor() # Assigns c as the cursor print("Importing columns: %s \nin table %s from %s.\n"%(Columns,Tablename,Sqdb)) c.execute('''SELECT {columns} FROM {table} {condition}'''.format(table=Tablename, columns=Columns, condition=Condition)) # Selects the table to read/fetch Sql_headers = [description[0] for description in c.description] Sql_columns = c.fetchall() # Reads the table and saves into the memory as Sql_rows print("Importing completed...\n") conn.commit() # Commits all the changes made conn.execute('pragma foreign_keys = on') # Locks the SQLite file print("SQLite Foreign_keys are locked...\n") conn.close() # Closes the SQLite file print("Disconnected from %s.\n"%(Sqdb)) return Sql_headers,Sql_columns### END DEFINING A READER FUNCTION ###Sqdb = '/mypath/myfile.sqlite'Tablename = "Zone" # Change this with your desired table to play withColumns = """*""" # Changes this with your desired columns to importCondition = '' # Add your condition and leave blank if no conditionheadings,data = Conditional_Sqdb_reader(Sqdb,Tablename,Columns,Condition)
1 回答
不負相思意
TA貢獻1777條經驗 獲得超10個贊
經過幾個小時的學習并學習了很多東西之后，我找到了解決方案。基本上，在 sqlite3 中使用 mod_spatialite 是這里的關鍵。當我嵌入這個包時，它允許我使用spatialite函數，例如ST_As_Text將sql二進制字符串轉換為以POLYGON((....geopanda類型條目開頭的字符串。有很多資料解釋了我們如何繪制這些數據。本質上，這是我的代碼（將其與我的問題中的代碼進行比較）：
import sqlite3? # Package for SQLite
### BEGIN DEFINING A READER FUNCTION ###
def Conditional_Sqdb_reader(Sqdb,Tablename,Columns,Condition):
? ? conn = sqlite3.connect(Sqdb) # Connects the file to Python
? ? conn.enable_load_extension(True)
? ? #mod_spatialite (recommended)
? ? conn.execute('SELECT load_extension("mod_spatialite.so")')? ?
? ? conn.execute('SELECT InitSpatialMetaData(1);')??
? ? print("\nConnected to %s.\n"%(Sqdb))
? ? conn.execute('pragma foreign_keys = off') # Allows making changes into the SQLite file
? ? print("SQLite Foreign_keys are unlocked...\n")
? ? c = conn.cursor() # Assigns c as the cursor
? ? print("Importing columns: %s \nin table %s from %s.\n"%(Columns,Tablename,Sqdb))
? ? c.execute('''SELECT {columns}?
? ? ? ? ? ? ? ? ?FROM {table}
? ? ? ? ? ? ? ? ?{condition}'''.format(table=Tablename,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?columns=Columns,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? condition=Condition)) # Selects the table to read/fetch
? ? Sql_headers = [description[0] for description in c.description]
? ? Sql_columns = c.fetchall() # Reads the table and saves into the memory as Sql_rows
? ? print("Importing completed...\n")
? ? conn.commit() # Commits all the changes made
? ? conn.execute('pragma foreign_keys = on') # Locks the SQLite file
? ? print("SQLite Foreign_keys are locked...\n")
? ? conn.close() # Closes the SQLite file
? ? print("Disconnected from %s.\n"%(Sqdb))
? ? return Sql_headers,Sql_columns
### END DEFINING A READER FUNCTION ###
Sqdb = '/Users/tanercokyasar/Desktop/Qgis/chicago2018-Supply.sqlite'
Tablename = "Zone" # Change this with your desired table to play with
Columns = """*,
? ? ? ? ? ? ?ST_AsText(GEO) as GEO""" # Changes this with your desired columns to import
Condition = ''? ? # Add your condition and leave blank if no condition
headings,data = Conditional_Sqdb_reader(Sqdb,Tablename,Columns,Condition)
- 1 回答
- 0 關注
- 156 瀏覽
添加回答
舉報
0/150
提交
取消