Plotting polygons from SQL type geo column in Python

Question

I have an Sqlite database with a table that includes a geo column. When I add this table into QGIS as a layer, it shows a map of Chicago with polygons as shown below. I think, the polygon points are stored in the column named geo.

I am trying to plot the same in Python to be able to add more things on top of this layout using Matplotlib. To begin with, I could load the table named "Zone" in Python using the following (that I wrote):

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 with
Columns = """*""" # 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)

The data on the table is stored in "data" as a list. So, data[0][-1] yields the geo of the polygon of the first row, which looks something like: b'\x00\x01$i\x00\x00@\xd9\x94\x8b\xd6<\x1bAb\xda7\xb6]\xb1QA\xf0\xf7\x8b\x19UC\x1bA\x9c\xde\xc5\r\xc3\xb1QA|\x03\x00\x00\x00\x01\x00\x00\x00\x06\x00\x00\x00Hlw\xef-C\x1bA\x9c\xde\xc5\r\xc3\xb1QA\xf0\xf7\x8b\x19UC\x1bAv\xc0u)^\xb1QA\xbcw\xd4\x88\xf1<\x1bAb\xda7\xb6]\xb1QA\xa5\xdc}n\xd7<\x1bA\x84.\xe1r\xbe\xb1QA@\xd9\x94\x8b\xd6<\x1bA\xce\x8eT\xef\xc1\xb1QAHlw\xef-C\x1bA\x9c\xde\xc5\r\xc3\xb1QA\xfe' I do not know how to decode this and convert to a meaningful series of points, but that is what it is and QGIS apparently can do it with no hassle. How can I plot all these polygons in Python while being able to add other things within the Matplotlib world later on?

Answer 1

After spending quite a few hours and learning a lot of things, I found the solution. Basically, using mod_spatialite in sqlite3 was the key per here. When I embedded this package, it allowed me use spatialite functions such as ST_As_Text which converts the sql binary string to a string starting with POLYGON((.... which is sort of a geopanda type entry. There is plenty of sources explaining how we can plot such data. In essence, here is my code (compare it to the one in my question):

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)