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.

2 Answers 2


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())) 
  • This is pretty embarrassing and I guess its just one of these days. Sorry for disturbing :|
    – Chau
    Commented Sep 11, 2017 at 12:11
  • 2
    Sometimes you just need a 2nd pair of eyes :)
    – user2856
    Commented Sep 11, 2017 at 21:16

Looks like you're just missing the closing bracket:

data_source.ExecuteSQL('SELECT CreateSpatialIndex(\'the_table\', \'' + layer.GetGeometryColumn() + '\''))
  • +1, Thanks for answering my question. I have accepted Lukes answer since it is a bit more elaborate.
    – Chau
    Commented Sep 11, 2017 at 12:13

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.