[gdal-dev] ogrinfo UPDATE performance request

Thu Jun 9 03:02:29 PDT 2022

Hi Jukka,
Not entirely sure what you mean by 'have you tested the sql with psql',
since this is an ogr2ogr process which works.
It is a remnant of a case where multiple iv (iv1, iv2 etc.) shapefile
columns were needed, so the sql used to be
like
coalesce(iv1,'')||coalesce(iv2,'')||coalesce(iv2,'')||coalesce(iv3,'')||coalesce(iv4,'')
instead of a single (useless) coalesce,
which I kept as it might come in handy later.
I am reconstructing my stuff (new update), as the idea of a good speed up
by leaving out -dialect sqlite is attractive.
So far I noticed that one postgis function requires sqlite dialect:
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
ERROR 1: Undefined function 'ST_GeometryType' used.
(I know that I could have used -skipfailures to get rid of linestring
geometries here, but the specific geometry type selection seemed better)
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.
Hope this rings a bell about parsing the sql with postgis functions in
postgresql dialect?
Best regards,
Jan

On Thu, Jun 9, 2022 at 10:57 AM Rahkonen Jukka <
jukka.rahkonen at maanmittauslaitos.fi> wrote:

> Hi Jan,
>
>
>
> I had a try with a table that I created from the “states” shapefile from
> the Geoserver demo data.
>
>
>
> 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"
>
>
>
> 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?
>
>
>
> -Jukka Rahkonen-
>
>
>
> *Lähettäjä:* Jan Heckman <jan.heckman at gmail.com>
> *Lähetetty:* torstai 9. kesäkuuta 2022 11.17
> *Vastaanottaja:* Rahkonen Jukka <jukka.rahkonen at maanmittauslaitos.fi>
> *Kopio:* Andreas Oxenstierna <andreas.oxenstierna at t-kartor.com>;
> gdal-dev at lists.osgeo.org
> *Aihe:* Re: [gdal-dev] ogrinfo UPDATE performance request
>
>
>
> 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)
>
> 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
>
> This gives me acceptable performance, btw. It's handling a really large
> dataset, so having to wait for some minutes did not bother me.
>
> This is on version 3.4.2.
>
> The transaction behavior in ogr2ogr can be influenced with the -gt option,
> with -gt unlimited to do everything in a single transaction.
>
> 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...
>
> Best regards,
>
> Jan
>
>
>
> On Thu, Jun 9, 2022 at 9:46 AM Rahkonen Jukka <
> jukka.rahkonen at maanmittauslaitos.fi> 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 <gdal-dev-bounces at lists.osgeo.org> *Puolesta *Andreas
> Oxenstierna
> *Lähetetty:* torstai 9. kesäkuuta 2022 9.50
> *Vastaanottaja:* gdal-dev at lists.osgeo.org
> *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%7Ca5ad6f614e254c2857c908da49f07cc1%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903594478569882%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=5lD3AZEeicoNPWSa4PFwpNcmqVhTPKOR9jArFnzxKkA%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: andreas.oxenstierna at t-kartor.com
> 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%7Ca5ad6f614e254c2857c908da49f07cc1%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903594478569882%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=WK25LhkFBg31rJ%2Fvy09FN2ZLXrRen5zKF1cQROwj8AY%3D&reserved=0>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
> <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>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220609/3c285084/attachment.htm>