[gdal-dev] ogrinfo UPDATE performance request

Thu Jun 9 04:02:33 PDT 2022

Hi Jukka,

Thanks for all the dialect info.

Reading your last message about testing the dialect in use, I have to admit
that I did not think about native sql
(dialect) being deduced from the source type rather than from any dbms
involved.
That is clear to me now, of course.

Would it be correct, then, that, when using SQL when converting from
shapefile to postgresql/postgis,
dialect sqlite (or ogr_sql) cannot be avoided? I can live with that, of
course. Perhaps I'll start converting my
'special' geometry functions to dll's in postgresql so that I don't need to
convert and reconvert to/from shapefile.

Specifically I am working on what in arcgis is called union, which divides
overlapping polygons in constituent
parts without overlaps. This function does not seem present in postgis and
is problematic anyway when
the dataset is large enough.
(The Dutch spatial planning dataset 'Enkelbestemming' is such a dataset
with over 2.6 million entries with
overlaps both small and large, but to me, at least in terms of efficient
data access, the very essence of the idea
is that you have at most one valid geometry with attributes for each
(point)location.)

Best regards and thanks,
Jan

On Thu, Jun 9, 2022 at 12:19 PM Rahkonen Jukka <
jukka.rahkonen at maanmittauslaitos.fi> wrote:

> Hi,
>
>
>
> You are getting closer but at the bottom of the page
> https://gdal.org/user/ogr_sql_dialect.html there is an important
> paragraph:
>
> Non-OGR SQL
>
> All OGR drivers for database systems: MySQL, PostgreSQL / PostGIS, Oracle
> Spatial, SQLite / Spatialite RDBMS, ODBC RDBMS, ESRI Personal GeoDatabase,
> SAP HANA and MSSQLSpatial - Microsoft SQL Server Spatial Database, override
> the GDALDataset::ExecuteSQL() function with dedicated implementation and,
> by default, pass the SQL statements directly to the underlying RDBMS. In
> these cases the SQL syntax varies in some particulars from OGR SQL. Also,
> anything possible in SQL can then be accomplished for these particular
> databases. Only the result of SQL WHERE statements will be returned as
> layers.
>
>
>
> So if you do not select SQL dialect for PostGIS you do not use the OGR SQL
> dialect but the native PostgreSQL one. For using OGR SQL with PostgreSQL
> (maybe it could make sense in some special case but I am not sure) use
> “-dialect OGRSQL”. If you are uncertain about what dialect is in use you
> can make a tests with some native-only functions like -sql "select
> postgis_version()" or -sql “select sqlite_version”.
>
>
>
> -Jukka Rahkonen-
>
>
>
> *Lähettäjä:* Andreas Oxenstierna <andreas.oxenstierna at t-kartor.com>
> *Lähetetty:* torstai 9. kesäkuuta 2022 12.48
> *Vastaanottaja:* gdal-dev at lists.osgeo.org; Rahkonen Jukka <
> jukka.rahkonen at maanmittauslaitos.fi>
> *Aihe:* Re: ogrinfo UPDATE performance request
>
>
>
> Hi
>
> Thanks a lot and sorry for the noise.
> UPDATE do work as expected using the OGR SQL, i.e. one atomic db
> transaction executing in the database = vastly faster than using -dialect
> sqlite
> .
> The doc at https://gdal.org/user/ogr_sql_dialect.html
> <https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgdal.org%2Fuser%2Fogr_sql_dialect.html&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638713449%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=X63CofyOIyNwpeP%2Ba605L5ZCCn7Z8qP36rt5VwQc8eE%3D&reserved=0> needs
> some addition - I assume that INSERT, DELETE etc. also works
> "While in theory any sort of command could be handled this way, in
> practice the mechanism is used to provide a subset of SQL SELECT capability
> to applications."
>
> Maybe also clarify if the sqlite dialect is necessary to keep at all ...
>
>
>
> 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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PSddxQ5cOcxyh9rEAqWw791nqfWO%2B%2BKJbDSWGuSOOpY%3D&reserved=0>
>
> On 9 Jun 2022, 11:15 +0200, Rahkonen Jukka <
> jukka.rahkonen at maanmittauslaitos.fi>, wrote:
>
> Hi,
>
>
>
> This updated 166000 rows in 15 seconds for me on my laptop without any
> workarounds:
>
> ogrinfo PG:"host=localhost port=5432 dbname=my_pg user=user password=pw"
> -sql "update buildingtest set version=99"
>
>
>
> -Jukka Rahkonen-
>
>
>
> *Lähettäjä:* Andreas Oxenstierna <andreas.oxenstierna at t-kartor.com>
> *Lähetetty:* torstai 9. kesäkuuta 2022 12.02
> *Vastaanottaja:* gdal-dev at lists.osgeo.org; Rahkonen Jukka <
> jukka.rahkonen at maanmittauslaitos.fi>
> *Aihe:* Re: ogrinfo UPDATE performance request
>
>
>
> Hi
>
> AFAIK, UPDATE only works with ogrinfo and the sqlite dialect. We have not
> tested the native PG SQL dialect extensively though, it took some time to
> find the correct syntax.
> And to answer one other mail, the table name needs to be written as
> \”<schema>.<table>\”, at least on macOS
>
> The performance issue is mainly that ogrinfo updates one feature at a
> time. Even if it should pack all data, copy it into a temp SQLite, do the
> update and transact back, it would be vastly faster if it is done in one
> transaction instead of one transaction for each feature as it is done now.
>
> However, I realised that a workaround may be to add needed attribute info
> to any file format (GeoPackage or shape) - need to verify the performance
> though.
> It should be nice to be able to execute this in a pure GDAL/OGR
> environment - the use case is to aggregate on different values, resolutions
> and interpolation methods for an AI platform so we may need to test
> thousands of variants.
>
>
>
> 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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PSddxQ5cOcxyh9rEAqWw791nqfWO%2B%2BKJbDSWGuSOOpY%3D&reserved=0>
>
> On 9 Jun 2022, 09:46 +0200, 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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=kt57EZEaekVUQ3o1yZSAHv0pMtSQMV50HIhGKSEdues%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%7C40cc50d74e814135428b08da49fd17a3%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C0%7C637903648638869695%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PSddxQ5cOcxyh9rEAqWw791nqfWO%2B%2BKJbDSWGuSOOpY%3D&reserved=0>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220609/99c5fe93/attachment.htm>