Create and use index with GDAL/ogr on shapefile

Question

I'm using OGR and VRT to create a new shapefile from two distinct sources (CSV file and shapefile).

It works well, but it is very slow. I want to make it more efficient, so I tried indexing my data before processing my query -as follow (my join is done on INSEE_COM attribute) and guided by the doc :

ogrinfo -sql "CREATE INDEX ON CHEF_LIEU USING INSEE_COM" ./IGN/ADMINEXPRESS/ADE-COG_1-1_SHP_LAMB93_FR/CHEF_LIEU.shp
ogrinfo -sql "CREATE SPATIAL INDEX ON CHEF_LIEU"  ./IGN/ADMINEXPRESS/ADE-COG_1-1_SHP_LAMB93_FR/CHEF_LIEU.shp

I only get "using driver 'ESRI Shapefile' successful." as instantaneous returned value, which sounds very suspect to me.

How can I be sure the indexing operation is working ? Is that the right way to do ?

Answer 1

If a spatial index for a Shapefile was created by GDAL, you will have a .qix file side-by-side to the rest of the Shapefile's files. And yes, the process is usually very fast.

If you want to see more output and have GDAL tell you about what it is doing, you can enable debug messages using --config CPL_DEBUG ON. Here is an example:

$ ls -1sh file.*
 50M file.dbf
4.0K file.prj
117M file.shp
556K file.shx

$ ogrinfo --config CPL_DEBUG ON -sql "CREATE SPATIAL INDEX ON file" file.shp
Shape: DBF Codepage = LDID/87 for file.shp
Shape: Treating as encoding 'ISO-8859-1'.
GDAL: GDALOpen(file.shp, this=0x5636d708a740) succeeds as ESRI Shapefile.
INFO: Open of `file.shp'
      using driver `ESRI Shapefile' successful.
Shape: Estimated spatial index tree depth: 15
Shape: Falling back to max number of allowed index tree levels (12).
SHAPE: Creating index file file.qix
GDAL: GDALClose(file.shp, this=0x5636d708a740)

$ ls -1sh file.*
 50M file.dbf
4.0K file.prj
2.3M file.qix
117M file.shp
556K file.shx

If you do the same for an already existing index (mind that GDAL can read .sbn and .sbx indexes) you will get messages about it trying to "unlink" files, that means it would delete them if they did exist.

$ ogrinfo --config CPL_DEBUG ON -sql "CREATE SPATIAL INDEX ON file" file.shp
Shape: DBF Codepage = LDID/87 for file.shp
Shape: Treating as encoding 'ISO-8859-1'.
GDAL: GDALOpen(file.shp, this=0x55e1355ab740) succeeds as ESRI Shapefile.
INFO: Open of `file.shp'
      using driver `ESRI Shapefile' successful.
SHAPE: Unlinking index file file.qix
SHAPE: Trying to unlink index file file.sbn
SHAPE: Failed to delete file file.sbn.
No such file or directory
SHAPE: Trying to unlink index file file.sbx
SHAPE: Failed to delete file file.sbx.
No such file or directory
Shape: Estimated spatial index tree depth: 15
Shape: Falling back to max number of allowed index tree levels (12).
SHAPE: Creating index file file.qix
GDAL: GDALClose(file.shp, this=0x55e1355ab740)