I have created a test spatialite database and I am having trouble inserting geometry into the table. I have successfully created the geometry column in the table and then I run the insert command. The weird thing is that it runs without error, and the attributes get passed in, however the geometry column is empty when I view it through a ODBC connection, as well as no geometry coming through in QGIS.
My code to create the table:
with sqlite3.connect(r'somedatabase.sqlite') as conn: conn.enable_load_extension(True) conn.execute("SELECT load_extension('mod_spatialite')") conn.execute("SELECT InitSpatialMetaData()") c = conn.cursor() c.execute("""CREATE TABLE IF NOT EXISTS gid(gid_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, latitude REAL, longitude REAL)""") c.execute("SELECT AddGeometryColumn('gid','the_geom', 4283,'POINT','XY')") conn.commit()
And this is the code to create the geometry
someGeometry = [(150.895,-33.961),(150.896,-33.959),(150.893,-33.960)] for longitude,latitude in someGeometry: c.execute("""INSERT INTO gid(gid_id,latitude,longitude,the_geom) VALUES(NULL,?,?,GeomFromText('POINT((%f %f)',4283))""" %(longitude,latitude), (latitude,longitude,)) conn.commit()
I have also tried to remove the python string formatting by inserting only 1 point with specific coordinates like this
c.execute("""INSERT INTO gid(gid_id,latitude,longitude,the_geom) VALUES(NULL,?,?,GeomFromText('POINT((150.895,-33.961)',4283))""",(latitude,longitude,))