ogrinfo SQL GROUP BY Clause

Question

Does ogrinfo support a GROUP BY clauses? I could not find any good documentation of all the SQL syntax features it supports.

I tried the following:

ogrinfo . -geom=no -sql 'SELECT lsad FROM tl_2011_us_county GROUP BY lsad'

And got the following error:

ERROR 1: SQL: Failed to parse SELECT statement, extra input at BY token.
INFO: Open of `.'
      using driver `ESRI Shapefile' successful.

What I really want to do:

SELECT lsad, count(*)
FROM tl_2011_us_county
GROUP BY lsad

What I can do:

SELECT DISTINCT lsad
FROM tl_2011_us_county

And then:

SELECT count(*)
FROM tl_2011_us_county
WHERE lsad = 'foo'

... but that is annoying.

Answer 1

Depends on the datasource - if OGR needs to fall back to OGR SQL (like when using shapefiles) then no. But if you are using PostGIS for instance you can use GROUP BY.

Answer 2

For those who are looking at group by clause in ogrinfo/ogr2ogr try using -dialect sqlite option:

For example:

ogr2ogr -dialect sqlite -f CSV "output.csv" "input.shp" -sql "select field1,sum(field2) from input group by field1 order by field1" -lco SEPARATOR=SEMICOLON

Above will convert shape file to csv (without geometry), separated by SEMICOLON and using group by clause of sql.