1

I have an example file, example.geojson:

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "id": "abc123", "properties": { "data": "a", "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] } },
{ "type": "Feature", "id": "abc123", "properties": { "data": "b", "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 1.0, 1.0 ] } }
]
}

It has both feature ids and id also appears in properties:

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "id": "abc123", "properties": { "data": "a", "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] } },
{ "type": "Feature", "id": "abc123", "properties": { "data": "b", "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 1.0, 1.0 ] } }
]                      ^                                            ^
}                      |                                            |
                   feature id                                 id property

(More on feature ids: GeoJSON specification. Search for "id".)

Following this answer to the question Generating unique positive integers as id with ogr2ogr, I do:

ogr2ogr -sql "SELECT *, ROW_NUMBER() OVER() AS generated_id FROM example" \
        -dialect sqlite \
        example_newids.geojson example.geojson \
        -lco ID_FIELD='generated_id'

example_newids.geojson becomes:

{
"type": "FeatureCollection",
"name": "SELECT",
"features": [
{ "type": "Feature", "id": 1, "properties": { "data": "a" }, "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] } },
{ "type": "Feature", "id": 2, "properties": { "data": "b" }, "geometry": { "type": "Point", "coordinates": [ 1.0, 1.0 ] } }
]
}

The id field disappeared from properties. I would prefer this not to happen. Expected output:

{
"type": "FeatureCollection",
"name": "SELECT",
"features": [
{ "type": "Feature", "id": 1, "properties": { "data": "a", "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] } },
{ "type": "Feature", "id": 2, "properties": { "data": "b", "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 1.0, 1.0 ] } }
]
}

What to modify on the ogr2ogr command above to avoid dropping ids from properties?

If possible, I would prefer to not having to know anything about the input GeoJSON in advance, such as fields within properties.

2 Answers 2

1

I can't explain why, but this seems to work:

ogr2ogr -sql "SELECT geometry, id as id,ROW_NUMBER() OVER() AS generated_id FROM id" -dialect sqlite id2.json id.json -lco ID_FIELD="generated_id"

Result:

{
"type": "FeatureCollection",
"name": "SELECT",
"features": [
{ "type": "Feature", "id": 1, "properties": { "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] } },
{ "type": "Feature", "id": 2, "properties": { "id": "abc123" }, "geometry": { "type": "Point", "coordinates": [ 1.0, 1.0 ] } }
]
}

The fields to select can be found with ogrinfo file.json -al -so. Most recent GDAL even supports ogrinfo output in JSON format that should help with parsing the result. See example below.

ogrinfo id.json -al -so -json
{
  "description":"id.json",
  "driverShortName":"GeoJSON",
  "driverLongName":"GeoJSON",
  "layers":[
    {
      "name":"id",
      "metadata":{
      },
      "geometryFields":[
        {
          "name":"",
          "type":"Point",
          "nullable":true,
          "extent":[
            0.0,
            0.0,
            1.0,
            1.0
          ],
          "coordinateSystem":{
            "wkt":"GEOGCRS[\"WGS 84\",\n    DATUM[\"World Geodetic System 1984\",\n        ELLIPSOID[\"WGS 84\",6378137,298.257223563,\n            LENGTHUNIT[\"metre\",1]]],\n    PRIMEM[\"Greenwich\",0,\n        ANGLEUNIT[\"degree\",0.0174532925199433]],\n    CS[ellipsoidal,2],\n        AXIS[\"geodetic latitude (Lat)\",north,\n            ORDER[1],\n            ANGLEUNIT[\"degree\",0.0174532925199433]],\n        AXIS[\"geodetic longitude (Lon)\",east,\n            ORDER[2],\n            ANGLEUNIT[\"degree\",0.0174532925199433]],\n    ID[\"EPSG\",4326]]",
            "projjson":{
              "$schema":"https://proj.org/schemas/v0.5/projjson.schema.json",
              "type":"GeographicCRS",
              "name":"WGS 84",
              "datum":{
                "type":"GeodeticReferenceFrame",
                "name":"World Geodetic System 1984",
                "ellipsoid":{
                  "name":"WGS 84",
                  "semi_major_axis":6378137,
                  "inverse_flattening":298.257223563
                }
              },
              "coordinate_system":{
                "subtype":"ellipsoidal",
                "axis":[
                  {
                    "name":"Geodetic latitude",
                    "abbreviation":"Lat",
                    "direction":"north",
                    "unit":"degree"
                  },
                  {
                    "name":"Geodetic longitude",
                    "abbreviation":"Lon",
                    "direction":"east",
                    "unit":"degree"
                  }
                ]
              },
              "id":{
                "authority":"EPSG",
                "code":4326
              }
            },
            "dataAxisToSRSAxisMapping":[
              2,
              1
            ]
          }
        }
      ],
      "featureCount":2,
      "fields":[
        {
          "name":"data",
          "type":"String",
          "nullable":true,
          "uniqueConstraint":false
        },
        {
          "name":"id",
          "type":"String",
          "nullable":true,
          "uniqueConstraint":false
        }
      ]
    }
  ],
  "metadata":{
  },
  "domains":{
  }
}
3
  • Yes, good point! It has the disadvantage of having to know the fields within properties in advance (if I don't want to lose those key-value pairs). Maybe I can get around that using jq or similar.
    – zabop
    Commented Jan 9, 2023 at 15:34
  • You can get a list of fields with ogrinfo.
    – user30184
    Commented Jan 9, 2023 at 15:46
  • Nice. I come up with a jq version (see gis.stackexchange.com/a/449190/191643). My ogrinfo isn't updated enough so the -json flag does not work. Should probably update but a bit afraid I'm gonna break existing code. Once I update, I'll attempt to post a full solution using ogrinfo instead of jq, probably it's more elegant.
    – zabop
    Commented Jan 9, 2023 at 16:25
1

This seems to work:

ogr2ogr -sql "SELECT \
        $(for key in $(cat example.geojson | jq --join-output '.features[0].properties | keys | join(" ")'); do echo -n "'example'.${key}, "; done)\
        'example'.geometry, \
        ROW_NUMBER() OVER() AS generated_id \
        FROM 'example'" \
        -dialect sqlite \
        example_newids.geojson example.geojson \
        -lco ID_FIELD='generated_id'

It might be more elegant to follow the suggestion in this answer and use ogrinfo instead of jq.

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.