<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.Shkpostityyli18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Sorry, I did not read well enough about what you were doing. You wrote “</span><span lang="EN-US">slightly more involved sql on a postgresql table requires dialect=sqlite to work at
all” but in your example you are reading data from a shapefile “alltogether_pruned_out.shp”. That you write data into PostGIS does not play any role, select is executed against the source. For shapefile there is no native SQL dialect so the alternatives are
OGR SQL that is the default but does not support coalesce, and SQLite SQL that supports coalesce but must be especially selected with -dialect sqlite.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I misread that you were reading data from PostGIS and in that case coalesce is supported with the default SQL that is PostgreSQL SQL then. Suggestion to test the SQL with psql was referring to my wrong assumption about
the source data.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka-</span><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> Jan Heckman <jan.heckman@gmail.com> <br>
<b>Lähetetty:</b> torstai 9. kesäkuuta 2022 13.02<br>
<b>Vastaanottaja:</b> Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi><br>
<b>Kopio:</b> Andreas Oxenstierna <andreas.oxenstierna@t-kartor.com>; gdal-dev@lists.osgeo.org<br>
<b>Aihe:</b> Re: [gdal-dev] ogrinfo UPDATE performance request<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Hi Jukka,<o:p></o:p></p>
<div>
<p class="MsoNormal">Not entirely sure what you mean by 'have you tested the sql with psql', since this is an ogr2ogr process which works.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">It is a remnant of a case where multiple iv (iv1, iv2 etc.) shapefile columns were needed, so the sql used to be<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">like coalesce(iv1,'')||coalesce(iv2,'')||coalesce(iv2,'')||coalesce(iv3,'')||coalesce(iv4,'') instead of a single (useless) coalesce,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">which I kept as it might come in handy later. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">I am reconstructing my stuff (new update), as the idea of a good speed up by leaving out -dialect sqlite is attractive.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">So far I noticed that one postgis function requires sqlite dialect:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">D:\ro_enkel\data>ogr2ogr -f postgresql -append -gt unlimited PG:"user=postgres dbname=test sslmode=disable" -sql "select *,substr(plangebied,9,4) as ovh_code from enkelbestemming where ST_GeometryType(geometrie)='POLYGON'" -nlt multipolygon
-nln ro_enkel.enkelbestemming enkelbestemming.gml.gz -progress<br>
ERROR 1: Undefined function 'ST_GeometryType' used.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(I know that I could have used -skipfailures to get rid of linestring geometries here, but the specific geometry type selection seemed better)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">I'll check my other sqlite dialect stuff as soon as I get to it, but first I need to wait for a conversion to complete.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Hope this rings a bell about parsing the sql with postgis functions in postgresql dialect?<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Best regards,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Jan<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Thu, Jun 9, 2022 at 10:57 AM Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi Jan,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I had a try with a table that I created from the “states” shapefile from the Geoserver demo data.
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">ogrinfo PG:"host=localhost port=5432 dbname=my_pg user=user password=pw" -sql "select trim(coalesce(state_name,state_fips),';') as fidstring, state_fips, st_union(wkb_geometry)
as multipolygon from states group by state_name,state_fips"</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I think that the SQL is fundamentally the same and it does work for me with the default dialect once I edited the geometry column name to the one I have in PostGIS.
Are you sure that it requires SQLite dialect for you? Have you tested the SQL with psql?
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:12.0pt"><span lang="EN-US">-Jukka Rahkonen-</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<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> Jan Heckman <<a href="mailto:jan.heckman@gmail.com" target="_blank">jan.heckman@gmail.com</a>>
<br>
<b>Lähetetty:</b> torstai 9. kesäkuuta 2022 11.17<br>
<b>Vastaanottaja:</b> Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Kopio:</b> Andreas Oxenstierna <<a href="mailto:andreas.oxenstierna@t-kartor.com" target="_blank">andreas.oxenstierna@t-kartor.com</a>>;
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] ogrinfo UPDATE performance request<o:p></o:p></p>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Afaik as I know, slightly more involved sql on a postgresql table requires dialect=sqlite to work at all, e.g. (picked a random example using ogr2ogr instead of ogrinfo)<o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">ogr2ogr -f postgresql -dialect sqlite -append PG:"user=%user% dbname=%dbname%" -sql "select trim(coalesce(iv1,''),';') as fidstring, code, st_union(geometry) as multipolygon from
alltogether_pruned_out group by iv1,code" -nln %schema%.%baseout%_pruned -nlt multipolygon alltogether_pruned_out.shp<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">This gives me acceptable performance, btw. It's handling a really large dataset, so having to wait for some minutes did not bother me.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">This is on version 3.4.2.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">The transaction behavior in ogr2ogr can be influenced with the -gt option, with -gt unlimited to do everything in a single transaction.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">But it would be nice if this imo strange need to refer to dialect sqlite were removed. Especially strange when you use postgis functions in the sql statement...<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Best regards,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Jan<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">On Thu, Jun 9, 2022 at 9:46 AM Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><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><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">-Jukka Rahkonen-
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></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" target="_blank">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" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] ogrinfo UPDATE performance request<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></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%7C9ff03396a5954105d3e408da49ff3282%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903657673321226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=WehlZFsuwgmvdPUfyCHexruhTlcfQxd%2BvBDwuxu%2FFZE%3D&reserved=0" target="_blank">
https://gdal.org/development/rfc/rfc13_createfeatures.html</a>, requesting this but it was withdrawn for reasons not anymore digitally available.<o:p></o:p></p>
</div>
</div>
<div name="messageSignatureSection">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></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" target="_blank">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%7C9ff03396a5954105d3e408da49ff3282%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903657673321226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=93%2Fl9Bg3BnXiFrVETO2tNYh1Lt1UYDltf%2B9qKHd4HOQ%3D&reserved=0" target="_blank">www.t-kartor.com</a><o:p></o:p></p>
</div>
</div>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fgdal-dev&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7C9ff03396a5954105d3e408da49ff3282%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903657673321226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0%2Bp97xy9g46eIiZbqbEd9pS9ix5Qji0LEnaqAVrHlgg%3D&reserved=0" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><o:p></o:p></p>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</body>
</html>