ogr2ogr sql: what is the name of the geometry field?

Question

I have the following ogrinfo -so output about a shapefile:

Layer name: PREDEFINED
Geometry: Polygon
Feature Count: 58075
Extent: (425995.430000, 336399.500000) - (447356.663000, 359782.760000)
Layer SRS WKT:
PROJCS["OSGB 1936 / British National Grid",
    GEOGCS["OSGB 1936",
        DATUM["OSGB_1936",
            SPHEROID["Airy 1830",6377563.396,299.3249646,
                AUTHORITY["EPSG","7001"]],
            TOWGS84[446.448,-125.157,542.06,0.15,0.247,0.842,-20.489],
            AUTHORITY["EPSG","6277"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AXIS["Latitude",NORTH],
        AXIS["Longitude",EAST],
        AUTHORITY["EPSG","4277"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",49],
    PARAMETER["central_meridian",-2],
    PARAMETER["scale_factor",0.9996012717],
    PARAMETER["false_easting",400000],
    PARAMETER["false_northing",-100000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH],
    AUTHORITY["EPSG","27700"]]
gml_id: String (0.0) NOT NULL
LABEL: Integer (0.0)

I want to run an ogr2ogr SQL query to get back just the gml_id and the spatial field as WKT. This is what I'm trying:

ogr2ogr -f GeoJSON output.geojson input.gml -sql "select gml_id, geom from PREDEFINED"

But I get ERROR 1: Unrecognized field name geom..

How can I work out what the geometry field name is? I can't see it in the ogrinfo output - is it a standard name?

ThomasG77 · Accepted Answer

Changing geom to Geometry should solve the issue. However, I also get the same error after this change. You should also add the -dialect sqlite option (I've tested with another dataset on my computer)

Running the following solve the issue

ogr2ogr -f GeoJSON output.geojson input.gml -dialect sqlite -sql "select gml_id, Geometry from PREDEFINED"

FIY, you may want to look at the gdal-cheat-sheet github repository that give a bunch of recipes to better use gdal/ogr.

PS: you can also use OGR_Geometry instead of Geometry if you don't want/need to use -dialect sqlite option (credit to @user30184 answer I upvoted)

user30184 · Answer

One possibility is to check the documentation
https://www.gdal.org/ogr_sql.html

Geometry field
  
  The GEOMETRY special field represents the geometry of the feature
  returned by OGRFeature::GetGeometryRef(). It can be explicitly
  specified in the result column list of a SELECT, and is automatically
  selected if the wildcard is used.
  
  For OGR layers that have a non-empty geometry column name (generally
  for RDBMS datasources), as returned by OGRLayer::GetGeometryColumn(),
  the name of the geometry special field in the SQL statement will be
  the name of the geometry column of the underlying OGR layer.

https://www.gdal.org/ogr_sql.html

OGR_GEOMETRY
  
  Some of the data sources (like MapInfo tab) can handle geometries of
  different types within the same layer. The OGR_GEOMETRY special field
  represents the geometry type returned by
  OGRGeometry::getGeometryName() and can be used to distinguish the
  various types. By using this field one can select particular types of
  the geometries like:

Or then you can make a try with ogrinfo

ogrinfo -sql "select * from test" test.shp
INFO: Open of `test.shp'
      using driver `ESRI Shapefile' successful.

Layer name: test
Geometry: 3D Polygon
Feature Count: 1
Extent: (327.000000, 228.000000) - (630.000000, 631.000000)
Layer SRS WKT:
(unknown)
Geometry Column = _ogr_geometry_
OGRFeature(test):0
  POLYGON Z ((327 497 20,447 631 20,630 498 20,381 228 20,327 497 20))

ogrinfo -dialect sqlite -sql "select * from test" test.shp
INFO: Open of `test.shp'
      using driver `ESRI Shapefile' successful.

Layer name: SELECT
Geometry: 3D Polygon
Feature Count: 1
Extent: (327.000000, 228.000000) - (630.000000, 631.000000)
Layer SRS WKT:
(unknown)
Geometry Column = GEOMETRY
OGRFeature(SELECT):0
  POLYGON Z ((327 497 20,447 631 20,630 498 20,381 228 20,327 497 20))

So the name of the geometry is "OGR_GEOMETRY" for the OGR SQL and either "GEOMETRY" or the real name that is used in the datasource for the SQLite SQL dialect.

junique · Answer

The best thing is to read the documentation, which is now here: https://gdal.org/user/ogr_sql_dialect.html
In my case, the source layer had format GeoPackage, and here [https://gdal.org/drivers/vector/gpkg.html] one can find that the geometry column for GeoPackage is named geom.
So to add SELECT …, geom FROM geoPkgLayer did it for me.

ogr2ogr sql: what is the name of the geometry field?

3 Answers

Add your own answers!

Ask a Question