[gdal-dev] ogr2ogr skipfailures and sqlite missing indexes

[Seth G]

Hi Jukka,

Thanks for the note about row by row inserts - this would explain the large performance difference. 
I'd agree it is best to filter out features explicitly. Sometimes -skipfeatures is just handy for having a quick look at the data though in a different format. 

The main issue was the results could be inconsistent - 4 datasets would be created with indexes and the 5th would not, with no warning or error. Then later on there could be a query in SQLite with a WHERE clause such as:

ogc_fid in (select rowid from SpatialIndex WHERE f_table_name = 'mydataset5' AND search_frame = ST_Buffer(p.Shape, 50)) 

Would cause no results would be returned, as there is no spatial index named 'mydataset5' (which appears to be a flaw in the suggested use of spatial indexes in Spatialite). The ogr2ogr fix avoids this case. 

Seth

--
web:http://geographika.co.uk
twitter: @geographika

On Thu, Nov 29, 2018, at 1:09 PM, jratike80 wrote:
> Hi Seth,
> 
> Even if the creation of spatial index with -skipfeatures is now fixed,
> generally that is a thing that should not really be used with GeoPackage and
> SQLite/Spatialite. That leads to single-row transactions and very poor
> performance. 
> There are usually other ways to avoid the problems and still keep large
> transactions. You already used "-nlt GEOMETRY" (I would have used "-nlt
> PROMOTE_TO_MULTI"), invalid geometries can be skipped with SQL (-sql "select
> * from my_layer where ST_IsValid(my_geometry)=1"), geometry collections can
> be skipped with SQL and so on. Fixing the data before conversion is the
> solution I prefer if I have time for that.
> 
> -Jukka Rahkonen-
> 
> 
> Seth G-2 wrote
> > Excellent - thanks for the incredibly fast fix Even - wasn't expecting
> > that!
> > 
> > Seth
> 
> 
> 
> 
> 
> --
> Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev