[gdal-dev] Selecting just the geometry column with OGR SQL

[Rahkonen Jukka (MML)]

Even Rouault wrote: 
maanantai 9. marraskuuta 2020 17.10

> Jukka,

>> I believe that OGR SQL dialect adds the geometry column into SQL 
>> selection by default. Am I right with this? I volunteer to edit the 
>> documentation https://gdal.org/user/ogr_sql_dialect.html if this is 
>> the case. But what I can't understand is another side of the story, 
>> how to select just the geometry field with OGR SQL dialect from some data sources.
>> 
>> This command returns both time column and geometry from GML ogrinfo 
>> -sql "select time from timetest" timetest.gml I can select just the 
>> geometry from geometry column named as "geometryProperty" by ogrinfo 
>> -sql "select geometryProperty from timetest"
>> timetest.gml
>> 
>> The behavior is the same with SpatiaLite when I use "-dialect OGRSQL".
>> Geometry is selected automatically but it can be selected also by 
>> name. But I cannot discover any way to select just the geometry field from shapefile.
>> Is it because geometry column in shapefile does not have any name as 
>> this Python test seems to prove?
>> >>> from osgeo import ogr
>> >>> shp_ds = ogr.Open('timetest.shp')
>> >>> shp_lyr = shp_ds.GetLayer(0)
>> >>> shp_lyr.GetGeometryColumn()
>> 
>> ''
>> 
>> OpenJUMP JML driver does not report a name for geometry column either. 
>> Is it rather an exception that drivers give names for the geometry 
>> columns? I know I can select only the geometry column with the SQLite 
>> dialect, but I am curious.

> Indeed, in OGR SQL, if the geometry column has a nonempty name (typically for 
> database drivers), you can use it to select it. When the geometry column name 
> is empty, such as in the shapefile or OpenJUMP driver, you can use 
> _ogr_geometry_ (with leading and trainling underscore) to select it. This is likely undocumented.

> As far as I can see, this was introduced per 
> https://github.com/OSGeo/gdal/commit/1519dcb00bd7802d2f701114f0e1cb9b524a547f
> to fix an issue with comparison with empty string literals (this was before OGR enforced the
>  difference between single-quoted string literals and double- quoted identifiers)

I could not manage to guess the right syntax for Windows right ahead but solved the quiz easily 
with your double-quote hint

First trial:
ogrinfo -sql "select _ogr_geometry_ from timetest" timetest.shp
INFO: Open of `timetest.shp'
      using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected $undefined. Occurred around :
select _ogr_geometry_ from timetest

Second trial:
ogrinfo -sql "select \"_ogr_geometry_\" from timetest" timetest.shp 

-Jukka-

> Even

--
Spatialys - Geospatial professional services http://www.spatialys.com