<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title></title>
</head>
<body>
<div name="messageBodySection">
<div dir="auto">Hi<br>
<br>
Thanks again - that explains my confusion. So there is really three SQL dialect options with quite variable capabilities and issues.<br>
Or four if the INDIRECT_SQLITE is an own dialect as mentioned in <a href="https://gdal.org/programs/ogrinfo.html" target="_blank">https://gdal.org/programs/ogrinfo.html</a> -dialect<br>
<br>
I assume that ”nativeSQL” is the default dialect for any RDBMS datasource and OGRSQL for all other datasources.<br>
This is not clear in the doc what I can find/understand.<br>
<br>
Maybe the example given last in <a href="https://gdal.org/programs/ogrinfo.html" target="_blank">https://gdal.org/programs/ogrinfo.html</a><span style="color:var(--linkColor)"> </span>should be complemented with how an UPDATE in PG (or any RDBMS) is done using
native SQL.<br>
</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">andreas.oxenstierna@t-kartor.com</a><br>
<a href="http://www.t-kartor.com">www.t-kartor.com</a></div>
</div>
<div name="messageReplySection">On 9 Jun 2022, 12:18 +0200, Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi>, wrote:<br>
<blockquote type="cite" style="border-left-color: grey; border-left-width: thin; border-left-style: solid; margin: 5px 5px;padding-left: 10px;">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,</span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">You are getting closer but at the bottom of the page
<a href="https://gdal.org/user/ogr_sql_dialect.html">https://gdal.org/user/ogr_sql_dialect.html</a> there is an important paragraph:<br>
<br>
Non-OGR SQL</span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">All OGR drivers for database systems: MySQL, PostgreSQL / PostGIS, Oracle Spatial, SQLite / Spatialite RDBMS, ODBC RDBMS, ESRI Personal GeoDatabase, SAP HANA and MSSQLSpatial
- Microsoft SQL Server Spatial Database, override the GDALDataset::ExecuteSQL() function with dedicated implementation and, by default, pass the SQL statements directly to the underlying RDBMS. In these cases the SQL syntax varies in some particulars from
OGR SQL. Also, anything possible in SQL can then be accomplished for these particular databases. Only the result of SQL WHERE statements will be returned as layers.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">So if you do not select SQL dialect for PostGIS you do not use the OGR SQL dialect but the native PostgreSQL one. For using OGR SQL with PostgreSQL (maybe it could make
sense in some special case but I am not sure) use “-dialect OGRSQL”. If you are uncertain about what dialect is in use you can make a tests with some native-only functions like -sql "select postgis_version()" or -sql “select sqlite_version”.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">-Jukka Rahkonen-</span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> Andreas Oxenstierna <andreas.oxenstierna@t-kartor.com><br>
<b>Lähetetty:</b> torstai 9. kesäkuuta 2022 12.48<br>
<b>Vastaanottaja:</b> gdal-dev@lists.osgeo.org; Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi><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>
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://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgdal.org%2Fuser%2Fogr_sql_dialect.html&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638713449%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=X63CofyOIyNwpeP%2Ba605L5ZCCn7Z8qP36rt5VwQc8eE%3D&reserved=0" target="_blank">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 ...</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">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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PSddxQ5cOcxyh9rEAqWw791nqfWO%2B%2BKJbDSWGuSOOpY%3D&reserved=0">www.t-kartor.com</a></p>
</div>
</div>
<div name="messageReplySection">
<p class="MsoNormal">On 9 Jun 2022, 11:15 +0200, Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>>, wrote:<br>
<br>
</p>
<blockquote style="border:none;border-left:solid windowtext 1.0pt;padding:0cm 0cm 0cm 8.0pt;margin-left:3.75pt;margin-top:3.75pt;margin-right:3.75pt;margin-bottom:3.75pt">
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="mso-fareast-language:EN-US">Hi,</span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="mso-fareast-language:EN-US"> </span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">This updated 166000 rows in 15 seconds for me on my laptop without any workarounds:</span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml: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" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">-Jukka Rahkonen-</span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b>Lähettäjä:</b> Andreas Oxenstierna <<a 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 href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>; Rahkonen Jukka <<a 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" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> </p>
<div name="messageBodySection">
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> </p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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">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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PSddxQ5cOcxyh9rEAqWw791nqfWO%2B%2BKJbDSWGuSOOpY%3D&reserved=0">www.t-kartor.com</a></p>
</div>
</div>
<div name="messageReplySection">
<p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:12.0pt">On 9 Jun 2022, 09:46 +0200, Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>>, wrote:</p>
<blockquote style="border:none;border-left:solid windowtext 1.0pt;padding:0cm 0cm 0cm 8.0pt;margin-left:3.75pt;margin-top:3.75pt;margin-right:3.75pt;margin-bottom:3.75pt">
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="mso-fareast-language:EN-US">Hi,</span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="mso-fareast-language:EN-US"> </span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml: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" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US">-Jukka Rahkonen-</span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="mso-fareast-language:EN-US" xml:lang="EN-US"> </span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b>Lähettäjä:</b> gdal-dev <<a href="mailto:gdal-dev-bounces@lists.osgeo.org">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">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] ogrinfo UPDATE performance request</p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> </p>
<div name="messageBodySection">
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=kt57EZEaekVUQ3o1yZSAHv0pMtSQMV50HIhGKSEdues%3D&reserved=0">
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" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> </p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">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">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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PSddxQ5cOcxyh9rEAqWw791nqfWO%2B%2BKJbDSWGuSOOpY%3D&reserved=0">www.t-kartor.com</a></p>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
</div>
</body>
</html>