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

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

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, and maybe it does internally but then something later goes haywire for opaque reasons.  If I manually provide the geometry column’s name in the latter case, no error is raised but the results are an empty table, but I know there should be positive results.  At the very least I don’t think this is intuitive behavior and I wonder if there’s an OGR bug.  Very basic usage, nothing special, no arrow interfacing, etc.

We run a little behind master, at v3.8.1.  There were a half dozen or so related changes to gpkg / executesql in the meanwhile but nothing stuck out to me as addressing this issue, but I could be wrong.

Willing to supply both .gpkg files and the associated sql query on request.

Best,
Jesse
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20240930/f8e943ae/attachment.htm>