OGR SQL: "SELECT DISTINCT hstore_get_value(...)" doesn't work

[Robinlovelace]

Expected behavior and actual behavior.

wget https://github.com/ropensci/osmextract/raw/master/inst/its-example.osm.pbf
ogrinfo \
  -sql "SELECT DISTINCT hstore_get_value(other_tags, 'bicycle') from lines" \
  its-example.osm.pbf 

Should generate a valid result, like:

ogrinfo -dialect sqlite -sql "SELECT DISTINCT \
  hstore_get_value(other_tags, 'bicycle') from lines" \
  its-example.osm.pbf 

...
hstore_get_value(other_tags, 'bicycle'): String (0.0)
OGRFeature(SELECT):0
  hstore_get_value(other_tags, 'bicycle') (String) = (null)

OGRFeature(SELECT):1
  hstore_get_value(other_tags, 'bicycle') (String) = designated

OGRFeature(SELECT):2
  hstore_get_value(other_tags, 'bicycle') (String) = yes

Instead it generates an error message:

Layer name: lines
Geometry: None
ERROR 1: Invalid index : -1
ERROR 1: Invalid index : -1
...

Steps to reproduce the problem.

See reproducible example above.

Operating system

Ubuntu 20.04

GDAL version and provenance

From ubuntugis-unstable PPA

gdalinfo --version
GDAL 3.2.1, released 2020/12/29

Background: https://gis.stackexchange.com/questions/393540

[jratike80]

The OGR SQL dialect and SQLite dialect do not support exactly same set of SQL functions. For example, all SpatiaLite functions are only avaialble with the SQLite function.

This hstore query thing is something special, though. Hstore is something that does not exist in the source data that is in PBF format but the OSM driver creates an virtual attribute that behaves like hstore behaves in PostGIS. It seems to be undocumented that you can use SQL hstore_get_value also against PBF by using the SQLite dialect. I am not sure where in the documentation to mention that and if there is more to inform.

[Robinlovelace]

So maybe this issue could be addressed by updating the docs. Happy to help with that although I must say I am new to SQL queries in this area.

[rouault]

hstore_get_value() is documented as available in both OGR and SQLite dialects in https://gdal.org/user/ogr_sql_dialect.html?highlight=hstore_get_value and https://gdal.org/user/sql_sqlite_dialect.html?highlight=hstore_get_value . The issue here is the use of DISTINCT function_name() with the OGR SQL dialect, which I assume isn't supported currently bit due to its many limitations. Only DISTINCT column_name is

[Robinlovelace]

The issue here is the use of DISTINCT function_name() with the OGR SQL dialect, which I assume isn't supported currently bit due to its many limitations. Only DISTINCT column_name is

Thanks for the explanation. Not sure if this is actionable. Keen to help out if so and happy to close if not.