2

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,))
10
  • 1
    I'd suggest not inserting "NULL" as gid_id. The WKT you are trying to interpolate is bogus ('POINT((%f %f)' perhaps should be 'POINT(%f %f)'). The last entry is bogus too ('POINT((150.895,-33.961)' is not valid WKT - perhaps you wanted 'POINT(150.895 -33.961)' ?). To debug this further, try showing the exact string you are trying to execute. I'd also suggest using SELECT InitSpatialMetaData(1) to do the insert in a transaction which will save some time.
    – BradHards
    Commented Dec 14, 2016 at 4:36
  • @BradHards Ah man, I was looking at it for ages, the sql is going to be it. Definitely an Extra bracket or two in there. I'll check when I'm back at work. Thanks. Commented Dec 14, 2016 at 4:45
  • Extra comma and extra bracket in the last bit of code.
    – BradHards
    Commented Dec 14, 2016 at 4:48
  • I believe that your GeomFromText is not right. Have you tested it? I would do select GeomFromText(('POINT(150.895 -33.961)'),4283)
    – user30184
    Commented Dec 14, 2016 at 5:43
  • Yeah I'm pretty sure it's wrong, I think what's happened is I have tried so many different combinations I have completely stuffed it. Not going to test until tomorrow. It's a pretty stupid question now it's been pointed out. However I'll leave it up because there is no warning that anything was going wrong with the insert. Commented Dec 14, 2016 at 5:45

1 Answer 1

1

In case anyone is trying the same, you can use geopandas with the to_file arguments to write to spatialite.

import geopandas as gpd
from shapely.geometry import Point

gdf = gpd.GeoDafaFrame([(0,150.895,-33.961,Point(150.895,-33.961)] columns=[gid_id,latitude,longitude,the_geom],geometry="the_geom")

gdf.to_file("somedatabase.sqlite", driver="SQLite", spatialite=True, layer="gid")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.