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-boun...@lists.osgeo.org> Puolesta Andreas Oxenstierna Lähetetty: torstai 9. kesäkuuta 2022 9.50 Vastaanottaja: gdal-dev@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.oxenstie...@t-kartor.com<mailto:andreas.oxenstie...@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>
_______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
