1

I have an example GeoJSON, and I would like to use it as input to an ogr2ogr command. I aim to generate a new file with positive integer feature ids, as described here. The example GeoJSON, src.geojson:

{
"type": "FeatureCollection",
"name": "layername_example",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "idnumber": 12 }, "geometry": { "type": "Point", "coordinates": [ 0, 1 ] } },
{ "type": "Feature", "properties": { "idnumber": 34 }, "geometry": { "type": "Point", "coordinates": [ 2, 3 ] } },
{ "type": "Feature", "properties": { "idnumber": 56 }, "geometry": { "type": "Point", "coordinates": [ 4, 5 ] } }
]
}

The ogr2ogr command I use to process the file:

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

This works perfectly. However, if I slightly change the input file, and call the idnumber property id instead, ie if src.geojson is:

{
"type": "FeatureCollection",
"name": "layername_example",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "id": 12 }, "geometry": { "type": "Point", "coordinates": [ 0, 1 ] } },
{ "type": "Feature", "properties": { "id": 34 }, "geometry": { "type": "Point", "coordinates": [ 2, 3 ] } },
{ "type": "Feature", "properties": { "id": 56 }, "geometry": { "type": "Point", "coordinates": [ 4, 5 ] } }
]
}

Then the same command results in:

ERROR 1: Cannot create virtual table for layer 'layername_example' : CREATE VIRTUAL: invalid SQL statement : CREATE TABLE "layername_example"("id" INTEGER HIDDEN PRIMARY KEY NOT NULL,"id" INTEGER,OGR_STYLE VARCHAR HIDDEN,"GEOMETRY" BLOB_POINT_XY_4326, OGR_NATIVE_DATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR)
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT *, ROW_NUMBER() OVER() AS generated_id FROM layername_example):
  no such table: layername_example

The problem seems to be sqlite specific: if I run ogr2ogr dst.geojson src.geojson -dialect sqlite -sql "SELECT * FROM layername_example", I get the error message above, while ogr2ogr dst.geojson src.geojson -sql "SELECT * FROM layername_example" runs without issues.

I would like to be able to use the sqlite dialect, as the ROW_NUMBER() OVER() AS generated_id part of the query is very useful in my real world scenario.

How do I use the sqlite dialect if one of the GeoJSON property fields is id?


I am using GDAL 3.8.3, released 2024/01/04.

3
  • Do you definitely want to have a property named "id"? This part of the GeoJSON specification datatracker.ietf.org/doc/html/rfc7946 makes that complicated: "If a Feature has a commonly used identifier, that identifier SHOULD be included as a member of the Feature object with the name "id", and the value of this member is either a JSON string or number". But it is not an error to have "id" in properties if you have a good reason for that.
    – user30184
    Commented Feb 16 at 10:11
  • I get the problematic GeoJSON from a data source. As a workaround, I can remove that property. It's a bit hacky, it would be better if I could handle all GeoJSONs from the source.
    – zabop
    Commented Feb 16 at 10:21
  • Btw, this is the datasource: environment.data.gov.uk/arcgis/rest/services/NE/….
    – zabop
    Commented Feb 16 at 10:21

1 Answer 1

1

GeoJSON defines a special meaning for "id" in https://datatracker.ietf.org/doc/html/rfc7946.

If a Feature has a commonly used identifier, that identifier SHOULD be included as a member of the Feature object with the name "id", and the value of this member is either a JSON string or number.

It means that "id" as a member of the Feature object is a Feature ID (FID), and FID has a special handling in GDAL. Most important, FID is used as a primary key when the data format supports PK. GeoJSON specification does not say that "id" as one of the properties should mean feature ID. However, such data are rather common and GDAL considers that "id" when found from the properies means FID as well. This is a feature that in your case turns into something like a bug.

Ogrinfo proves that "id" is interpreted to be a FID.

ogrinfo id.json -al -so
INFO: Open of `id.json'
      using driver `GeoJSON' successful.
Layer name: layername_example
...
FID Column = id
id: Integer (0.0)

The next problem comes from the way how the SQLite dialect works. The first step in using SQLite dialect is to make a virtual copy of the source data into an SQLite database. The error message shows what GDAL tries to do

ERROR 1: Cannot create virtual table for layer 'layername_example' : CREATE VIRTUAL: invalid SQL statement : CREATE TABLE "layername_example"("id" INTEGER HIDDEN PRIMARY KEY NOT NULL,"id" INTEGER,OGR_STYLE VARCHAR HIDDEN,"GEOMETRY" BLOB_POINT_XY_4326, OGR_NATIVE_DATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR)

The error happens because the "id" field is first used for the primary key "id" INTEGER HIDDEN PRIMARY KEY NOT NULL and then it is tried to be used again as a normal attribute "id" INTEGER.

I do not know how difficult it would be handle this case in the GDAL code page. You can make a feature request into GDAL GitHub repository if you think that the workarounds are not good enough.

When it comes to workarounds, there are certainly many. Easiest of all would be to edit the source data and change "id" into anything else. I tested a two-step procedure that seems to work with the native data.

  1. Convert data into a GeoPackage data with ogr2ogr by using the unsetfid option. By default the PK field is then getting values starting from 1

enter image description here

ogr2ogr -f gpkg id.gpkg id.json -unsetfid

  1. Convert data from the GeoPackage table into a new GeoJSON file but instead of the "id" field use the PK field "fid" that is renamed into "id".

    ogr2ogr -f GeoJSON -dialect OGRSQL -sql "select fid as id,* EXCLUDE (id) from layername_example" id2.json id.gpkg

Notice the use of the brand new convenience option EXCLUDE in the OGR SQL dialect https://gdal.org/user/ogr_sql_dialect.html#list-operators.

The id2.json file has positive integers starting from 1 in the "id" property.

{
"type": "FeatureCollection",
"name": "layername_example",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "id": 1 }, "geometry": { "type": "Point", "coordinates": [ 0.0, 1.0 ] } },
{ "type": "Feature", "properties": { "id": 2 }, "geometry": { "type": "Point", "coordinates": [ 2.0, 3.0 ] } },
{ "type": "Feature", "properties": { "id": 3 }, "geometry": { "type": "Point", "coordinates": [ 4.0, 5.0 ] } }
]
}

A oneliner that generates id members and keeps the id properties might also be an option.

ogr2ogr -f GeoJSON -lco id_generate=yes id2.json id.json

Result:

{
"type": "FeatureCollection",
"name": "layername_example",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "id": 0, "properties": { "id": 12 }, "geometry": { "type": "Point", "coordinates": [ 0.0, 1.0 ] } },
{ "type": "Feature", "id": 1, "properties": { "id": 34 }, "geometry": { "type": "Point", "coordinates": [ 2.0, 3.0 ] } },
{ "type": "Feature", "id": 2, "properties": { "id": 56 }, "geometry": { "type": "Point", "coordinates": [ 4.0, 5.0 ] } }]
}

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.