How do I turn an SQLite table into a SpatiaLite table?-CodePudding

I've got a Pandas table in an SQLite database with 40 odd columns all of which might need to be interrogated in QGIS. The table contains XY data and a previous Python code captured the EPSG code needed to create the points. How do I transform my Pandas or SQLite table into a SpatiaLite table?

spatialite_path = 'C:\Program Files (x86)\Spatialite'
os.environ['PATH'] = spatialite_path   ';'   os.environ['PATH']

con.enable_load_extension(True)
con.load_extension("mod_spatialite")
con.execute("SELECT InitSpatialMetaData();")

Tables = table_clean_wGIS,pandas_table_wXY,db_table_wXY
Variables = db_name (string), EPSG_Code (int)

Initially, and according to the old SpatiaLite Cookbook, I created a table with a Primary Key and X and Y using AddGeometryColumn:

con = sqlite3.connect(db_name)
cur = con.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS table_clean_wGIS (ID REAL PRIMARY KEY, UniquePointName TEXT DEFAULT 0, X DOUBLE DEFAULT 0, Y DOUBLE DEFAULT 0, Date TEXT )')

cur.execute('SELECT AddGeometryColumn("table_clean_wGIS", "geometry",(?),"POINT",0)', (EPSG_Code,))
cur.execute('SELECT CreateSpatialIndex("table_clean_wGIS","geometry")')
cur.execute('INSERT INTO table_clean_wGIS(ID, UniquePointName,X,Y, Date, geometry) SELECT ID, UniquePointName, X,Y,Date,MakePoint(X,Y,?) FROM pandas_table_wXY', (EPSG_Code,))
con.commit()
con.close()

This worked but only had a few columns. I tried FULL OUTER JOIN (SQLite doesn't support it), then a double LEFT JOIN but I think it forgot it was geometry. Then I set about just AddGeometryColumn to the initial 40 column table with Insert Into but this created double the rows with the correct geometries but not related to the rows.

con = sqlite3.connect(db_name)
cur = con.cursor()        

cur.execute('SELECT AddGeometryColumn ("db_table_wXY", "geometry",(?),"POINT",0)', (EPSG_Code,))

cur.execute('SELECT CreateSpatialIndex("db_table_wXY","geometry")')

cur.execute('INSERT INTO db_table_wXY(geometry) SELECT MakePoint(X,Y,?) FROM db_table_wXY', (EPSG_Code,))
con.commit()
con.close()

I tried Update-Set and seemed this worked but the results didn't show in QGIS.

cur.execute('SELECT AddGeometryColumn ("db_pss", "geometry",(?),"POINT",0)', (EPSG_Code,))

cur.execute('UPDATE db_pss SET geometry =MakePoint(X,Y,?)', (EPSG_Code,))

cur.execute('SELECT CreateSpatialIndex("db_pss","geometry")')
con.commit()
con.close()

CodePudding user response：

Finally I guess'n'checked my way into using this tidbit at the end of the code:

cur.execute('SELECT RecoverGeometryColumn("table_with_XY", "geometry", (?), "POINT", "XY")', (EPSG_Code,))

Full code:

#This only has to happen once per computer (i think) so feel free to skip these 2 lines
spatialite_path = 'C:\Program Files (x86)\Spatialite'
os.environ['PATH'] = spatialite_path   ';'   os.environ['PATH']

#Variables: db_name, EPSG_Code
#Connect to the database
con = sqlite3.connect(db_name)
cur = con.cursor()  
#Initialise Spatialite - this has to be done to create geometry binaries
con.enable_load_extension(True)
con.load_extension("mod_spatialite")
con.execute("SELECT InitSpatialMetaData();")      

#Add a blank/empty geometry column (I'm using Point data)
#the ? is to add a variable into a python based sql statement. EPSG_Code
cur.execute('SELECT AddGeometryColumn ("table_with_XY", "geometry",(?),"POINT",0)', (EPSG_Code,))

#This is where we make our points from the X and Y column in the table
cur.execute('UPDATE table_with_XY SET geometry =MakePoint(X,Y,?)', (EPSG_Code,))

#This line I have to use even though I don't think it should be necessary but it makes my code work so...
cur.execute('SELECT RecoverGeometryColumn("table_with_XY", "geometry", (?), "POINT", "XY")', (EPSG_Code,))
#Spatial Index to improve performance 
cur.execute('SELECT CreateSpatialIndex("table_with_XY","geometry")')
#commit the changes and always close your connection to the database
con.commit()
con.close()

This seems to be the way you can easily create geometries from X and Y data in the same table. I'm not sure its optimal. In hindsight maybe this has to do with the Primary Key.