Great tutorial on how to import geodataframe to spatialite database

[stuartong]

Thanks for this - first time using spatialite and I find this extremely helpful.

I am now running the executemany portion and my tuple length is 230k - it has been running awhile now (just at 1hr mark) - is this normal?

Also noticed ALOT of new triggers, index, views being created - is this the mod at work to enable the spatial queries?

Just trying to understand what is going on under the hood.

[jackdbd]

I'm afraid I can't help you that much. I've always worked with tiny datasets in SpatiaLite.

If the query takes a lot of time, I would try these 2 things:

1. work with a smaller dataset, and maybe performing the same query in batches.
2. work with an in-memory SQLite database.

I am defintely not an expert in SpatiaLite, but I read here that it defines a few triggers (and I guess also views and indexes) in order to compute and verify geometries.

If you are stuck, maybe try reading a few questions on gis.stackexchange.

[stuartong]

No problem! Found a solution but thanks anyway! If anyone is wondering in future when you use this method:

Create an index on some_id that you intend to use in your Update/Set/Where query and you should see significant improvement to your query.

For reference:
Without index - 1000points: 42 secs
With index - 230k points: 3secs