3

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.

4

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 Sep 11 '17 at 12:11
  • 2
    Sometimes you just need a 2nd pair of eyes :) – user2856 Sep 11 '17 at 21:16
2

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 Sep 11 '17 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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