3

I've a gpkg with several vector layers inside, i'd like to build a spatial index for each

I'm using very simple gdal/sql process:

ogrinfo -SQL "SELECT CreateSpatialIndex('gis_layer_01', 'geom')" \gpkg\C:\temp\world.gpkg ogrinfo -SQL "SELECT CreateSpatialIndex('gis_layer_02', 'geom')" \gpkg\C:\temp\world.gpkg ...

Instead of doing one layer after the next I'd like to loop that but can't figure it out how to in the windows CMD How to do it ? cmd or batch script preferabl

3 Answers 3

1

It is not straight forward to do that with a Windows batch file. The first step would be to read the layer list with ogrinfo, for example

ogrinfo algeria.gpkg
INFO: Open of `algeria.gpkg'
      using driver `GPKG' successful.
1: points (Point)
2: lines (Line String)
3: multilinestrings (Multi Line String)
4: multipolygons (Multi Polygon)
5: other_relations (Geometry Collection)

I believe it would be possible to get the layer names by using a FOR loop with tokens https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/for. However, it is much easier to just use ogr2ogr and create a new GeoPackage.

ogr2ogr -f GPKG new_with_statial_indexes.gpkg original.gpkg

This command copies all tables into a new GeoPackage and creates spatial indexes for all of the tables because that is the default as documented in https://gdal.org/drivers/vector/gpkg.html#layer-creation-options

SPATIAL_INDEX: If set to “YES” will create a spatial index for this layer. Default to YES

As the last step you can delete the original GeoPackage. Naturally the process will take more time than it would take to create just the indexes but if the GeoPackage is small, perhaps under 10 GB or so, it might not be critical. Make a test and consider.

2
  • Thank you, i get the problem, what about using python with gdal? How would that look? The gpkg is already 280gb so i need something efficient
    – Vectorito
    Commented May 9, 2022 at 18:20
  • Python is probably the best option then. Not a one line script though. You can find Python examples from this autotest script github.com/OSGeo/gdal/blob/master/autotest/ogr/ogr_gpkg.py.
    – user30184
    Commented May 9, 2022 at 18:33
0

If pyqgis is also an option you could use this:

from qgis.core import QgsVectorLayer

gpkg_fileName = "C:/my_folder/my_file.gpkg"
gpkg_layer = QgsVectorLayer(gpkg_fileName,"test","ogr")
subLayers =gpkg_layer.dataProvider().subLayers()

for layer in subLayers:
    name = layer.split('!!::!!')[1]
    uri = "%s|layername=%s" % (fileName, name)
    print(uri)
    v_layer = QgsVectorLayer(uri, name, 'ogr')
    v_layer.dataProvider().createSpatialIndex()
0

As it was mentioned in a comment that python is also an option... this is how you can do it using the geofileops package

import geofileops as gfo

path = r"C:\Temp\lds-nz-building-outlines\nz-building-outlines.gpkg"
for layer in gfo.listlayers(path):
    gfo.create_spatial_index(path, layer, exist_ok=True)

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.