How to extract unique values from .pbf file with GDAL

Question

I would like GDAL to return the unique values in a column. Reproducible example:

wget https://github.com/ropensci/osmextract/raw/master/inst/its-example.osm.pbf

ogrinfo its-example.osm.pbf 
ogrinfo its-example.osm.pbf lines > lines.txt
head lines.txt

## --2021-04-11 09:32:38--  https://github.com/ropensci/osmextract/raw/master/inst/its-example.osm.pbf
## Resolving github.com (github.com)... 140.82.121.3
## Connecting to github.com (github.com)|140.82.121.3|:443... connected.
## HTTP request sent, awaiting response... 302 Found
## Location: https://raw.githubusercontent.com/ropensci/osmextract/master/inst/its-example.osm.pbf [following]
## --2021-04-11 09:32:39--  https://raw.githubusercontent.com/ropensci/osmextract/master/inst/its-example.osm.pbf
## Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.109.133, 185.199.108.133, 185.199.111.133, ...
## Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.109.133|:443... connected.
## HTTP request sent, awaiting response... 200 OK
## Length: 40792 (40K) [application/octet-stream]
## Saving to: ‘its-example.osm.pbf.4’
## 
##      0K .......... .......... .......... .........            100% 3.04M=0.01s
## 
## 2021-04-11 09:32:39 (3.04 MB/s) - ‘its-example.osm.pbf.4’ saved [40792/40792]
## 
## INFO: Open of `its-example.osm.pbf'
##       using driver `OSM' successful.
## 1: points (Point)
## 2: lines (Line String)
## 3: multilinestrings (Multi Line String)
## 4: multipolygons (Multi Polygon)
## 5: other_relations (Geometry Collection)
## INFO: Open of `its-example.osm.pbf'
##       using driver `OSM' successful.
## 
## Layer name: lines
## Geometry: Line String
## Feature Count: -1
## Extent: (-1.561196, 53.806303) - (-1.549845, 53.809293)
## Layer SRS WKT:
## GEOGCRS["WGS 84",
##     DATUM["World Geodetic System 1984",

ogrinfo \
  -dialect sqlite -sql "SELECT highway, COUNT(*) from lines" \
  its-example.osm.pbf 

## Had to open data source read-only.
## INFO: Open of `its-example.osm.pbf'
##       using driver `OSM' successful.
## 
## Layer name: SELECT
## Geometry: None
## Feature Count: 1
## Layer SRS WKT:
## (unknown)
## highway: String (0.0)
## COUNT(*): Integer (0.0)
## OGRFeature(SELECT):0
##   highway (String) = footway
##   COUNT(*) (Integer) = 189

ogrinfo \
  -sql "SELECT DISTINCT highway from lines" \
  its-example.osm.pbf 

## Had to open data source read-only.
## INFO: Open of `its-example.osm.pbf'
##       using driver `OSM' successful.
## 
## Layer name: lines
## Geometry: None
## Feature Count: 13
## Layer SRS WKT:
## (unknown)
## highway: String (0.0)
## OGRFeature(lines):0
##   highway (String) = footway
## 
## OGRFeature(lines):1
##   highway (String) = cycleway
## 
## OGRFeature(lines):2
##   highway (String) = service
## 
## OGRFeature(lines):3
##   highway (String) = residential
## 
## OGRFeature(lines):4
##   highway (String) = trunk
## 
## OGRFeature(lines):5
##   highway (String) = steps
## 
## OGRFeature(lines):6
##   highway (String) = unclassified
## 
## OGRFeature(lines):7
##   highway (String) = track
## 
## OGRFeature(lines):8
##   highway (String) = pedestrian
## 
## OGRFeature(lines):9
##   highway (String) = trunk_link
## 
## OGRFeature(lines):10
##   highway (String) = tertiary
## 
## OGRFeature(lines):11
##   highway (String) = (null)
## 
## OGRFeature(lines):12
##   highway (String) = corridor

# fails
ogrinfo \
  -sql "SELECT DISTINCT other_tags from lines" \
  its-example.osm.pbf > test.txt # works

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

## ERROR 1: Invalid index : -1

What is the error message and how can I stop it?

Answer 1

Don't ask me why but it works if you make GDAL use its SQLite dialect via -dialect sqlite.

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

Had to open data source read-only.
INFO: Open of `its-example.osm.pbf'
      using driver `OSM' successful.

Layer name: SELECT
Geometry: None
Feature Count: 3
Layer SRS WKT:
(unknown)
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

It would be great if you could check with the developers if it should work with the default dialect as well and file a bug if not. Thanks!

If you want to extract just the resulting values, the only way I know from the top of my head would be a GROUP_CONCAT of the features' (~=rows) values in the query followed by commandline magic with grep or sed. For example:

$ ogrinfo -dialect sqlite -sql "SELECT \
GROUP_CONCAT(v) AS unique_values FROM \
(SELECT DISTINCT hstore_get_value(other_tags, \
'bicycle') AS v FROM lines)" \
its-example.osm.pbf 

gets you

Had to open data source read-only.
INFO: Open of `its-example.osm.pbf'
      using driver `OSM' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
unique_values: String (0.0)
OGRFeature(SELECT):0
  unique_values (String) = designated,yes

With some piping you can extract just the relevant part (will fail if your values include a literal "="):

$ ogrinfo -dialect sqlite -sql "SELECT \
GROUP_CONCAT(v) AS unique_values FROM \
(SELECT DISTINCT hstore_get_value(other_tags, \
'bicycle') AS v FROM lines)" \
its-example.osm.pbf \
 | grep " unique_values" | sed 's#.*= ##'

gets you

designated,yes

Of course this might not be very efficient and fast but that might not be an issue for you.