[gdal-dev] ogrinfo UPDATE performance request

[Rahkonen Jukka]

Hi again,

It might be good to know how the SQLite dialect works. When it is used for other datasources than natively SQLite based ones then GDAL creates a virtual table into a SQLite database with a VirtualOGR system. There is some information about that in https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualOGR.

As Sandro writes, it is really amazing, but you should not await that it is amazingly fast in all use cases. You compare direct SQL inside PostGIS with a process that copies data from PostGIS into SQLite, updates the features, and writes them back to PostGIS. Fortunately you do not need to use that long route with PostGIS because GDAL knows how to use native PostgreSQL dialect. However, sometimes it makes sense to use "-dialect SQLite" also with PostGIS because SpatiaLite has some nice functions that PostGIS does not have https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

-Jukka Rahkonen-


Lähettäjä: gdal-dev <gdal-dev-bounces at lists.osgeo.org> Puolesta Andreas Oxenstierna
Lähetetty: torstai 9. kesäkuuta 2022 9.50
Vastaanottaja: gdal-dev at lists.osgeo.org
Aihe: [gdal-dev] ogrinfo UPDATE performance request

Dear developers

Ogr SQL update capabilities are really impressing but there is one major performance issue with update of many features, exemplified by:
ogrinfo -dialect sqlite -sql "UPDATE <table> SET x = 1" PG:"<connection>"

This is painfully slow because ogr updates features one by one and furthermore updates all existing attributes incl. geometries.
Eg. updating 10000 features in pgAdmin/psql with UPDATE <table> SET x = 1 executes in milliseconds but takes several minutes with ogr.

The current ogr functionality is also not correct from a database transactional point of view.

I found an old RFC, https://gdal.org/development/rfc/rfc13_createfeatures.html, requesting this but it was withdrawn for reasons not anymore digitally available.

Best Regards

Andreas Oxenstierna
T-Kartor Geospatial AB
Olof Mohlins väg 12 Kristianstad
mobile: +46 733 206831
mailto: andreas.oxenstierna at t-kartor.com<mailto:andreas.oxenstierna at t-kartor.com>
www.t-kartor.com<https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.t-kartor.com%2F&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7Ced60af7ce08c45c3998108da49e44745%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C1%7C637903542052214466%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=UNIxumUx%2BtBz8Vb7jjH3Uvnbjwo3WYvj9tsRmrFHw5g%3D&reserved=0>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220609/670db1b0/attachment-0001.htm>