How can I use ogr2ogr & sqlite to create a new GeoJSON file that contains only a specific set of rows while renaming two of the keys?

Question

I need help with a syntax error from a sqlite command in ogr2ogr.

I'm trying to run a query on a geojson file. The query should create a new file that contains only a specific set of rows while renaming two of the keys.

What I have tried so far:

ogr2ogr -f geojson -dialect sqlite -sql "SELECT ID as aid, min as amin, max as amax, FROM contour WHERE amax >4 and amax <10" contour-pt5-middle.geojson contour-pt5-min4-max10.geojson

It results in the following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT ID as aid, min as amin, max as amax, FROM contour WHERE amax >4 and amax <10):
   near "FROM": syntax error

Simply removing a comma before my FROM statement seems to get me a bit closer, but it doesn't carry over the geometry object of coordinates:

ogr2ogr -f geojson -dialect sqlite -sql "SELECT ID as aid, min as amin, max as amax FROM contour WHERE amax >4 and amax <10" contour-pt5-middle.geojson contour-pt5-min4-max10.geojson

creates:

{
"type": "FeatureCollection",
"name": "contour",
"features": [
{ "type": "Feature", "properties": { "aid": 6, "amin": 4.0, "amax": 4.5 }, "geometry": null },
{ "type": "Feature", "properties": { "aid": 7, "amin": 4.5, "amax": 5.0 }, "geometry": null },
... 

Here is a screenshot of how my data is structured in contour-pt5-min4-max10.geojson:

Expected output

Should be the same as the screenshot but only including features with "aid" from 6 to 16.

Answer 1

You can. You already found out most of the solution. You just need to ask for the geometry field e.g

ogr2ogr -f geojson -dialect sqlite -sql "SELECT ID as aid, min as amin, max as amax, geometry FROM contour WHERE amax >4 and amax <10" contour-pt5-middle.geojson contour-pt5-min4-max10.geojson

See the GDAL related doc https://gdal.org/user/sql_sqlite_dialect.html#geometry-field