1

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:

structure of data 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.

3

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

1
  • This solved it perfectly! Thank you so much. – bkleeman Jun 18 '20 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.