[gdal-dev] ogrinfo UPDATE performance request
Even Rouault
even.rouault at spatialys.com
Thu Jun 9 03:16:03 PDT 2022
For the SQLite dialect, the page to consult is
https://gdal.org/user/sql_sqlite_dialect.html
Le 09/06/2022 à 11:47, Andreas Oxenstierna a écrit :
> Hi
>
> Thanks a lot and sorry for the noise.
> UPDATE do work as expected using the OGR SQL, i.e. one atomic db
> transaction executing in the database = vastly faster than using
> -dialect sqlite
> .
> The doc at https://gdal.org/user/ogr_sql_dialect.html needs some
> addition - I assume that INSERT, DELETE etc. also works
> "While in theory any sort of command could be handled this way, in
> practice the mechanism is used to provide a subset of SQL SELECT
> capability to applications."
>
> Maybe also clarify if the sqlite dialect is necessary to keep at all ...
>
> 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
> www.t-kartor.com <http://www.t-kartor.com>
> On 9 Jun 2022, 11:15 +0200, Rahkonen Jukka
> <jukka.rahkonen at maanmittauslaitos.fi>, wrote:
>>
>> Hi,
>>
>> This updated 166000 rows in 15 seconds for me on my laptop without
>> any workarounds:
>>
>> ogrinfo PG:"host=localhost port=5432 dbname=my_pg user=user
>> password=pw" -sql "update buildingtest set version=99"
>>
>> -Jukka Rahkonen-
>>
>> *Lähettäjä:* Andreas Oxenstierna <andreas.oxenstierna at t-kartor.com>
>> *Lähetetty:* torstai 9. kesäkuuta 2022 12.02
>> *Vastaanottaja:* gdal-dev at lists.osgeo.org; Rahkonen Jukka
>> <jukka.rahkonen at maanmittauslaitos.fi>
>> *Aihe:* Re: ogrinfo UPDATE performance request
>>
>> Hi
>>
>> AFAIK, UPDATE only works with ogrinfo and the sqlite dialect. We have
>> not tested the native PG SQL dialect extensively though, it took some
>> time to find the correct syntax.
>> And to answer one other mail, the table name needs to be written as
>> \”<schema>.<table>\”, at least on macOS
>>
>> The performance issue is mainly that ogrinfo updates one feature at a
>> time. Even if it should pack all data, copy it into a temp SQLite, do
>> the update and transact back, it would be vastly faster if it is done
>> in one transaction instead of one transaction for each feature as it
>> is done now.
>>
>> However, I realised that a workaround may be to add needed attribute
>> info to any file format (GeoPackage or shape) - need to verify the
>> performance though.
>> It should be nice to be able to execute this in a pure GDAL/OGR
>> environment - the use case is to aggregate on different values,
>> resolutions and interpolation methods for an AI platform so we may
>> need to test thousands of variants.
>>
>> 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
>> 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%7Cdbe5e7e115e843096f4508da49f6c062%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903621379745511%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=fOWKJE8n6lhdusAHklUPVfNqeIILJVDmszyuol6L6vs%3D&reserved=0>
>>
>> On 9 Jun 2022, 09:46 +0200, Rahkonen Jukka
>> <jukka.rahkonen at maanmittauslaitos.fi>, wrote:
>>
>> Hi,
>>
>> Do not use “-dialect sqlite” if you play with PostgreSQL but let
>> GDAL to use the native PG SQL dialect.
>>
>> -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
>> <https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgdal.org%2Fdevelopment%2Frfc%2Frfc13_createfeatures.html&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7Cdbe5e7e115e843096f4508da49f6c062%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903621379745511%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=AfWG17fsXJ1t6NaQAlAnz0b1bsgnwOwzHZJJVmfkyd4%3D&reserved=0>,
>> 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
>> 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%7Cdbe5e7e115e843096f4508da49f6c062%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903621379745511%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=fOWKJE8n6lhdusAHklUPVfNqeIILJVDmszyuol6L6vs%3D&reserved=0>
>>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
--
http://www.spatialys.com
My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220609/a7c19ec1/attachment-0001.htm>
More information about the gdal-dev
mailing list