5

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.

2 Answers 2

7

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.

1
  • I am in a catch 22 then. I wanted to inspect the shapefiles before I insert them in my postgis table, but to do it conveniently, I must insert them into a postgis table. Oh well. thanks!
    – kzh
    Jun 24, 2012 at 16:13
1

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.

1
  • This is the correct anwer. It works when querying a shapefile (of course really a dBaseIII + geometry) and that is what the OP wanted; I happen also to be helped by this answer.
    – Jan
    Mar 8, 2022 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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