[gdal-dev] [EXTERNAL] Re: GPKG geometry column name expectations with sqlite dialect

[Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC]]

Thanks Scott and Even,

Maybe it’s an unexpected interaction with Spatialite, but explicitly providing the geometry column’s name results in bogus outputs.  Is the SQL string wrong somehow?

f"select count(ST_Area('{lyr_name + '.' + geom_column_name}'))". (same outcome if re-arranged as count(…) from lyr_name instead)

No errors.  And the resulting layer has a single row with a 0 value, which I know is incorrect (in terms of what I am expecting, modulo PEBKAC)

From: Even Rouault <even.rouault at spatialys.com>
Date: Monday, September 30, 2024 at 2:57 PM
To: Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] <jesse.r.meyer at nasa.gov>, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev <gdal-dev at lists.osgeo.org>
Subject: [EXTERNAL] Re: [gdal-dev] GPKG geometry column name expectations with sqlite dialect
CAUTION: This email originated from outside of NASA.  Please take care when clicking links or opening attachments.  Use the "Report Message" button to report suspicious messages to the NASA SOC.



Jesse,
Le 30/09/2024 à 20:35, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND APPLICATIONS INC] via gdal-dev a écrit :
Hi,

I have two geopackage database files that were constructed, somehow, in a way that one has a geometry column called ‘geometry’ and the other ‘geom’.  QGIS nor OGR seem to have much an issue with this.  Both handle / display all the features in the expected ways.  However, for the gpkg with the ‘geom’ column name (which I believe is irregular), the usual SQLisms (with dialect=sqlite) seem to fail.  For the former gpkg, I can use the GEOMETRY identifier as documented successfully, however, this fails with the latter with an ‘no such column: GEOMETRY’ error.  The documentation leads me to believe that that identifier _should_ resolve to whatever the geometry column name is,
Hum, that was an unfortunate copy&paste from the OGR SQL dialect... Will be fixed per https://github.com/OSGeo/gdal/pull/10900

and maybe it does internally but then something later goes haywire for opaque reasons.

Only for OGR SQL, not for the SQLite native SQL as used by GPKG. For the later you need to use exactly the geometry column name of the table.
Even

--

http://www.spatialys.com<http://www.spatialys.com/>

My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20240930/ad9b857d/attachment.htm>