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

[Scott]

"Geometry Column = ..." in ogrinfo output. If the method returns an 
empty string then a special name "_ogr_geometry_" must be used.

That's from the OGR driver docs:
https://gdal.org/en/latest/user/ogr_sql_dialect.html

I've always found it a bit weird as well. If I'm not sure I'll try, 
geom, geometry, _ogr_geometry_.

Then, when creating a gpkg, I'll assign a generic name, geometry

Scott

On 9/30/24 11:35, Meyer, Jesse R. (GSFC-618.0)[SCIENCE SYSTEMS AND 
APPLICATIONS INC] via gdal-dev wrote:
> 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
> 
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev