<div dir="ltr">Hi Jukka,<div>Not entirely sure what you mean by 'have you tested the sql with psql', since this is an ogr2ogr process which works.</div><div>It is a remnant of a case where multiple iv (iv1, iv2 etc.) shapefile columns were needed, so the sql used to be</div><div>like coalesce(iv1,'')||coalesce(iv2,'')||coalesce(iv2,'')||coalesce(iv3,'')||coalesce(iv4,'') instead of a single (useless) coalesce,</div><div>which I kept as it might come in handy later. </div><div>I am reconstructing my stuff (new update), as the idea of a good speed up by leaving out -dialect sqlite is attractive.</div><div>So far I noticed that one postgis function requires sqlite dialect:</div><div>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.<br></div><div>(I know that I could have used -skipfailures to get rid of linestring geometries here, but the specific geometry type selection seemed better)</div><div>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.</div><div>Hope this rings a bell about parsing the sql with postgis functions in postgresql dialect?</div><div>Best regards,</div><div>Jan</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jun 9, 2022 at 10:57 AM Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="FI" style="overflow-wrap: break-word;">
<div class="gmail-m_6498802722788211128WordSection1">
<p class="MsoNormal"><span>Hi Jan,<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I had a try with a table that I created from the “states” shapefile from the Geoserver demo data.
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><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"<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><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? <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-<br>
<br>
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class="MsoNormal"><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<u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">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)<u></u><u></u></p>
<div>
<p class="MsoNormal">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<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">This gives me acceptable performance, btw. It's handling a really large dataset, so having to wait for some minutes did not bother me.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">This is on version 3.4.2.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">The transaction behavior in ogr2ogr can be influenced with the -gt option, with -gt unlimited to do everything in a single transaction.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">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...<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Best regards,<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Jan<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">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:<u></u><u></u></p>
</div>
<blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal">Hi,<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></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><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-
</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<div>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class="MsoNormal"><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<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"> <u></u><u></u></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%7Ca5ad6f614e254c2857c908da49f07cc1%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903594478569882%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=5lD3AZEeicoNPWSa4PFwpNcmqVhTPKOR9jArFnzxKkA%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.<u></u><u></u></p>
</div>
</div>
<div name="messageSignatureSection">
<p class="MsoNormal"> <u></u><u></u></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" 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%7Ca5ad6f614e254c2857c908da49f07cc1%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903594478569882%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=WK25LhkFBg31rJ%2Fvy09FN2ZLXrRen5zKF1cQROwj8AY%3D&reserved=0" target="_blank">www.t-kartor.com</a><u></u><u></u></p>
</div>
</div>
</div>
</div>
<p class="MsoNormal">_______________________________________________<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%7Ca5ad6f614e254c2857c908da49f07cc1%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903594478569882%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=39wIlMAl6pw9zwU3hQ3tpQ2S9BD3aVexhZZ%2FIU2gxEY%3D&reserved=0" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><u></u><u></u></p>
</blockquote>
</div>
</div>
</div>
</blockquote></div>