Jukka, I'm very surprised that you need to do that explicitely, since the driver should already do that by default. This is something we discovered last year or the year before when you were experimenting OSM -> Spatialite conversions. Since OGR 1.10, the spatial index is created when the datasource is closed.
Look at this debug trace: $ ogr2ogr -f sqlite /vsimem/out.sqlite ../autotest/ogr/data/poly.shp -dsco spatialite=yes --debug on OGR: OGROpen(../autotest/ogr/data/poly.shp/0x69a3d0) succeeded as ESRI Shapefile. SQLITE: SpatiaLite v4 DB found ! OGR_SQLITE: exec(CREATE TABLE 'poly' ( OGC_FID INTEGER PRIMARY KEY)) OGR_SQLITE: exec(DELETE FROM geometry_columns WHERE f_table_name = 'poly') OGR_SQLITE: exec(SELECT AddGeometryColumn('poly', 'GEOMETRY', 325834, 'POLYGON', 2)) OGR_SQLITE: exec(ALTER TABLE 'poly' ADD COLUMN 'area' FLOAT) OGR_SQLITE: exec(ALTER TABLE 'poly' ADD COLUMN 'eas_id' FLOAT) OGR_SQLITE: exec(ALTER TABLE 'poly' ADD COLUMN 'prfedea' VARCHAR(16)) OGR_SQLITE: BEGIN Transaction OGR_SQLITE: prepare(INSERT INTO 'poly' ("GEOMETRY","area","eas_id","prfedea") VALUES (?,?,?,?)) OGR_SQLITE: COMMIT Transaction OGR2OGR: 10 features written in layer 'poly' OGR_SQLITE: exec(SELECT CreateSpatialIndex('poly', 'GEOMETRY')) SQLITE: Error no such table: layer_statistics OGR: Unloading VirtualOGR module Shape: 10 features read on layer 'poly'. Could try adding --debug on to your ogr2ogr command line and see when CreateSpatialIndex() is created ? Even > Hi, > > I took timings about adding 115 GML files (548 MB together, 3.2 million > linestrings) into a Spatialite table. With default settings the table gets > initialized with spatial index which makes following inserts slower. Another > alternative is to create the table without spatial index, append all the > data first and as a last step create spatial index for the ready made table. > > With spatial index on: > Append + index: 71 minutes > > With spatial index off: > Append: 9 minutes > Create spatial index: 6 minutes > Total: 15 minutes > > I was rather happy with the initial conversion speed until I made this test > which revealed that creating spatial index as a final step made the whole > process more than four times faster! This way both data table and spatial > index are probably in contiguous chunks in the SQLite datafile and there is > no need for post process VACUUM. Vacuuming in SQLite is rather slow and for > this 1.3 GB database it takes more than 4 minutes to run. > > Suggestion: Add a new performance hint on page > http://www.gdal.org/ogr/drv_sqlite.html > > "If many source files will be collected into the same Spatialite table it > can be much faster to initialize the table without a spatial index by using > -lco SPATIAL_INDEX=NO and to create spatial index with a separate command > after all the data are appended. Spatial index can be created with ogrinfo > command > ogrinfo db.sqlite -sql "SELECT > CreateSpatialIndex('table_name','geometry_column_name')" > > Perhaps it could also be mentioned as a performance hint that VACUUUM can > also be done from orginfo as > ogrinfo db.sqlite -sql "VACUUM" > > -Jukka Rahkonen- > > > > _______________________________________________ > gdal-dev mailing list > gdal-dev@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/gdal-dev > _______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev