2

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?

7

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.