在Python中从SQL类型地理列绘制多边形

Question

我有一个 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)

Answer 1

经过几个小时的学习并学习了很多东西之后，我找到了解决方案。基本上，在 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)