1

I'm using GDAL (3.3.1) Python (3.8.11) bindings to process vector layers and am trying to use OGR SQL dialect to filter the layer, like this:

    sql = f"SELECT * from {out_lyr.GetName()} WHERE OGR_GEOM_AREA < {area_threshold}"
    out_lyr_selection = out_ds.ExecuteSQL(sql)

This works fine on FileGDB and Shapefile, when I try to use it on GeoPackage it gives the following error.

RuntimeError: In ExecuteSQL(): sqlite3_prepare_v2(SELECT * from merged WHERE OGR_GEOM_AREA < 20.0):
  no such column: OGR_GEOM_AREA

This post suggests I should use "indirect_sqlite" dialect, so I checked the OGR doc page. It suggested this would work:

    sql = f"SELECT * from {out_lyr.GetName()} WHERE ST_Area(GEOMETRY) < {area_threshold}"
    out_lyr_selection = out_ds.ExecuteSQL(sql, dialect="indirect_sqlite")

However, this time the error was:

RuntimeError: In ExecuteSQL(): sqlite3_prepare_v2(SELECT * from merged WHERE ST_Area(GEOMETRY) < 20.0):
  no such column: GEOMETRY

Am I doing something wrong, or is it not possible to use OGR SQL dialect to filter by area with GeoPackage?

1 Answer 1

1

For OGR SQL use dialect="OGRSQL". It is the default SQL dialect but only when the datasource has no support for a native SQL dialect. Therefore if you read PostGIS, SpatiaLite, GeoPackage, Oracle etc. you must explicitly select the ORG SQL dialect.

When using indirect_sqlite dialect pay attention to error message no such column: GEOMETRY. Check the real name of the geometry column with ogrinfo. It can be whatever but quite often you will see

Geometry Column = geom

2
  • Awesome, thanks. It was the dialect name I was missing - was trying "OGR SQL". If the correct name is written in the doc somewhere, I haven't found it. Also I didn't know about the geometry column name in ogrinfo - that's very handy.
    – jon_two
    Oct 27, 2021 at 9:21
  • 1
    Geometry name is reported if format allows to select it so it applies to database formats and XML/GML. The keywords of dialects are not really well enough documented in the documentation about SQL. Consider to help be pressing the "Edit on GitHub" button in gdal.org/user/ogr_sql_dialect.html#ogr-sql-dialect and gdal.org/user/sql_sqlite_dialect.html. Adding the right keywords to use, emphasized as in the ogr2ogr documentation gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-dialect would help other users in the future.
    – user30184
    Oct 27, 2021 at 9:36

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.