<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>For the SQLite dialect, the page to consult is
<a class="moz-txt-link-freetext" href="https://gdal.org/user/sql_sqlite_dialect.html">https://gdal.org/user/sql_sqlite_dialect.html</a><br>
</p>
<div class="moz-cite-prefix">Le 09/06/2022 à 11:47, Andreas
Oxenstierna a écrit :<br>
</div>
<blockquote type="cite"
cite="mid:c8306fca-4e2b-41bf-9655-bd79d71090b1@Spark">
<title></title>
<div name="messageBodySection">
<div dir="auto">Hi<br>
<br>
Thanks a lot and sorry for the noise. <br>
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<br>
.<br>
The doc at <a
href="https://gdal.org/user/ogr_sql_dialect.html"
target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">https://gdal.org/user/ogr_sql_dialect.html</a> needs
some addition - I assume that INSERT, DELETE etc. also works<br>
"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."<br>
<br>
Maybe also clarify if the sqlite dialect is necessary to keep
at all ...</div>
</div>
<div name="messageSignatureSection"><br>
<div class="matchFont">Best Regards<br>
<br>
Andreas Oxenstierna<br>
T-Kartor Geospatial AB<br>
Olof Mohlins väg 12 Kristianstad<br>
mobile: +46 733 206831<br>
mailto: <a href="mailto:andreas.oxenstierna@t-kartor.com"
moz-do-not-send="true" class="moz-txt-link-freetext">andreas.oxenstierna@t-kartor.com</a><br>
<a href="http://www.t-kartor.com" moz-do-not-send="true">www.t-kartor.com</a></div>
</div>
<div name="messageReplySection">On 9 Jun 2022, 11:15 +0200,
Rahkonen Jukka <a class="moz-txt-link-rfc2396E" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><jukka.rahkonen@maanmittauslaitos.fi></a>,
wrote:<br>
<blockquote type="cite">
<div class="WordSection1">
<p class="MsoNormal"><span>Hi,</span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span lang="EN-US">This updated 166000
rows in 15 seconds for me on my laptop without any
workarounds:</span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo
PG:"host=localhost port=5432 dbname=my_pg user=user
password=pw" -sql "update buildingtest set version=99"</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<div>
<div>
<p class="MsoNormal"><b>Lähettäjä:</b> Andreas
Oxenstierna <a class="moz-txt-link-rfc2396E" href="mailto:andreas.oxenstierna@t-kartor.com"><andreas.oxenstierna@t-kartor.com></a><br>
<b>Lähetetty:</b> torstai 9. kesäkuuta 2022 12.02<br>
<b>Vastaanottaja:</b> <a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>;
Rahkonen Jukka
<a class="moz-txt-link-rfc2396E" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><jukka.rahkonen@maanmittauslaitos.fi></a><br>
<b>Aihe:</b> Re: ogrinfo UPDATE performance request</p>
</div>
</div>
<p class="MsoNormal"> </p>
<div name="messageBodySection">
<div>
<p class="MsoNormal">Hi<br>
<br>
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. <br>
And to answer one other mail, the table name needs to
be written as \”<schema>.<table>\”, at
least on macOS<br>
<br>
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.<br>
<br>
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.<br>
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.</p>
</div>
</div>
<div name="messageSignatureSection">
<p class="MsoNormal"> </p>
<div>
<p class="MsoNormal">Best Regards<br>
<br>
Andreas Oxenstierna<br>
T-Kartor Geospatial AB<br>
Olof Mohlins väg 12 Kristianstad<br>
mobile: +46 733 206831<br>
mailto: <a
href="mailto:andreas.oxenstierna@t-kartor.com"
moz-do-not-send="true" class="moz-txt-link-freetext">andreas.oxenstierna@t-kartor.com</a><br>
<a
href="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"
moz-do-not-send="true">www.t-kartor.com</a></p>
</div>
</div>
<div name="messageReplySection">
<p class="MsoNormal">On 9 Jun 2022, 09:46 +0200, Rahkonen
Jukka <<a
href="mailto:jukka.rahkonen@maanmittauslaitos.fi"
moz-do-not-send="true" class="moz-txt-link-freetext">jukka.rahkonen@maanmittauslaitos.fi</a>>,
wrote:<br>
<br>
</p>
<blockquote>
<div>
<p class="MsoNormal"><span>Hi,</span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span lang="EN-US">Do not use
“-dialect sqlite” if you play with PostgreSQL but
let GDAL to use the native PG SQL dialect.</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka
Rahkonen-</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<div>
<div>
<p class="MsoNormal"><b>Lähettäjä:</b> gdal-dev
<<a
href="mailto:gdal-dev-bounces@lists.osgeo.org"
moz-do-not-send="true"
class="moz-txt-link-freetext">gdal-dev-bounces@lists.osgeo.org</a>>
<b>Puolesta</b> Andreas Oxenstierna<br>
<b>Lähetetty:</b> torstai 9. kesäkuuta 2022 9.50<br>
<b>Vastaanottaja:</b> <a
href="mailto:gdal-dev@lists.osgeo.org"
moz-do-not-send="true"
class="moz-txt-link-freetext">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] ogrinfo UPDATE
performance request</p>
</div>
</div>
<p class="MsoNormal"> </p>
<div name="messageBodySection">
<div>
<p class="MsoNormal">Dear developers<br>
<br>
Ogr SQL update capabilities are really
impressing but there is one major performance
issue with update of many features, exemplified
by:<br>
ogrinfo -dialect sqlite -sql "UPDATE
<table> SET x = 1" PG:”<connection>"<br>
<br>
This is painfully slow because ogr updates
features one by one and furthermore updates all
existing attributes incl. geometries.<br>
Eg. updating 10000 features in pgAdmin/psql with
UPDATE <table> SET x = 1 executes in
milliseconds but takes several minutes with ogr.<br>
<br>
The current ogr functionality is also not
correct from a database transactional point of
view.<br>
<br>
I found an old RFC, <a
href="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"
moz-do-not-send="true">
https://gdal.org/development/rfc/rfc13_createfeatures.html</a>,
requesting this but it was withdrawn for reasons
not anymore digitally available.</p>
</div>
</div>
<div name="messageSignatureSection">
<p class="MsoNormal"> </p>
<div>
<p class="MsoNormal">Best Regards<br>
<br>
Andreas Oxenstierna<br>
T-Kartor Geospatial AB<br>
Olof Mohlins väg 12 Kristianstad<br>
mobile: +46 733 206831<br>
mailto: <a
href="mailto:andreas.oxenstierna@t-kartor.com"
moz-do-not-send="true"
class="moz-txt-link-freetext">andreas.oxenstierna@t-kartor.com</a><br>
<a
href="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"
moz-do-not-send="true">www.t-kartor.com</a></p>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
gdal-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
<pre class="moz-signature" cols="72">--
<a class="moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</body>
</html>