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: [email protected]
www.t-kartor.com <http://www.t-kartor.com>
On 9 Jun 2022, 11:15 +0200, Rahkonen Jukka
<[email protected]>, 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 <[email protected]>
*Lähetetty:* torstai 9. kesäkuuta 2022 12.02
*Vastaanottaja:* [email protected]; Rahkonen Jukka
<[email protected]>
*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: [email protected]
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
<[email protected]>, 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 <[email protected]>
*Puolesta* Andreas Oxenstierna
*Lähetetty:* torstai 9. kesäkuuta 2022 9.50
*Vastaanottaja:* [email protected]
*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: [email protected]
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
[email protected]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
--
http://www.spatialys.com
My software is free, but my time generally not.
_______________________________________________
gdal-dev mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
