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 < [email protected]> 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 <[email protected]> > *Lähetetty:* torstai 9. kesäkuuta 2022 11.17 > *Vastaanottaja:* Rahkonen Jukka <[email protected]> > *Kopio:* Andreas Oxenstierna <[email protected]>; > [email protected] > *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 < > [email protected]> 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 <[email protected]> *Puolesta *Andreas > Oxenstierna > *Lähetetty:* torstai 9. kesäkuuta 2022 9.50 > *Vastaanottaja:* [email protected] > *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: [email protected] > 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 > [email protected] > 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> > >
_______________________________________________ gdal-dev mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/gdal-dev
