Adding spatial index to spatialite file in python using OGR?

Question

I have a spatialite data set without a spatial index on the geometry column and I would like to create it.

In OGR command line I can use the following to create it (OGR Spatialite driver):

ogrinfo db.sqlite -sql "SELECT CreateSpatialIndex('the_table','GEOMETRY')"

I would like to do this using the GDAL/OGR python bindings instead and I have tried to mimic the command line procedure:

driver = ogr.GetDriverByName('SQLite')    
data_source = driver.CreateDataSource('db.sqlite', ['SPATIALITE=YES'])    
layer = data_source.CreateLayer('the_table', None, ogr.wkbLineString25D, ['SPATIAL_INDEX=NO'])

data_source.ExecuteSQL('SELECT CreateSpatialIndex(\'the_table\', \'' + layer.GetGeometryColumn() + '\'')

The above returns the error message:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT CreateSpatialIndex('the_table', 'GEOMETRY'):
near "'GEOMETRY'": syntax error

What should I do different to make this work?

The answer to the obvious question, "why do I create a spatialite file without the spatial index just to create it afterwards?" is because I need to do bulk insertion.

Answer 1

You're missing a closing bracket. To make it easier to pick up these issues, you can use double quotes around your expression instead of having to escape literal single quotes. You can also use string formatting instead of multiple concatenation operators.

data_source.ExecuteSQL("SELECT CreateSpatialIndex('the_table', '{}')".format(layer.GetGeometryColumn())) 

Answer 2

Looks like you're just missing the closing bracket:

data_source.ExecuteSQL('SELECT CreateSpatialIndex(\'the_table\', \'' + layer.GetGeometryColumn() + '\''))