[gdal-dev] ogrinfo UPDATE performance request

Thu Jun 9 01:57:32 PDT 2022

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<mailto: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<mailto: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<mailto: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<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<mailto: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/7c49d5cd/attachment-0001.htm>