Performance problems using Sqlite function InitSpatialMetaData() to create a Spatialite database through Python

Question

I'm creating a dynamic database through python. The code above work's fine but when I use the function InitSpatialMetadata() in query, the database is created very slowly.
The file starts with 0kb and it's increases something like 2-5kb per second until it is completely create. All the process takes about 2-3 minutes to complete and the final file has 3mb. Someone already had this problem?

import sqlite3
db = sqlite3.connect('C:/test.sqlite')
cursor = db.cursor()
cursor.execute("SELECT InitSpatialMetadata()")

score 9 · Answer 1 · answered Aug 15 '16 at 07:01

9

This is an old question, but per this thread on gdal-dev, and depending on your version of the library,

InitSpatialMetadata(1) // Note the parameter

might run much faster. It did for me.

answered Aug 15 '16 at 07:01

Rob Skelly

211
3
2

score 3 · Answer 2 · answered Jul 15 '14 at 15:20

It's building the spatial metadata into the base and 3mb is a standard size.

To speed up the request, just use a begin/commit transaction to proceed in silently mode:

cursor.execute("BEGIN ;")
cursor.execute("SELECT InitSpatialMetadata()")
cursor.execute("COMMIT ;")

score 1 · Answer 3 · answered Dec 01 '17 at 18:51

You can set some pragma to make the this procedure faster:

Before execute "SELECT InitSpatialMetadata()", execute:

"PRAGMA synchronous = OFF" and "PRAGMA journal_mode = MEMORY'

Then go back to the settings:

"PRAGMA synchronous = FULL" and "PRAGMA journal_mode = DELETE'

This works with me

score 0 · Answer 4 · answered Oct 23 '15 at 14:05

0

I found this was no longer a problem when using pyspatialite in place of the basic sqlite3 module (on Windows at least) - https://github.com/lokkju/pyspatialite

answered Oct 23 '15 at 14:05

ssast

779
1
8
17

Performance problems using Sqlite function InitSpatialMetaData() to create a Spatialite database through Python

4 Answers4