ogr2ogr -f SQLite bar.sqlite foo.geojson

ogr2ogr -f SQLite -dialect sqlite -sql "select st_buffer(geometry, -1) as geometry, ogc_fid, value from foo" baz.sqlite bar.sqlite -nln baz

ogr2ogr -f SQLite bar.sqlite foo.geojson

ogr2ogr -f SQLite -dialect sqlite -sql "select st_buffer(geometry, -1) as geometry, ogc_fid, value from foo" baz.sqlite bar.sqlite -nln baz

I have a more complicated dataset in real life but here I'll include a simplified case to illustrate what is happening. I'm almost certain it due to my lack of knowledge on how to use ogr2ogr properly for my use-case.

So to start I have a geojson file, call it foo.geojson:

{
    "type": "FeatureCollection",
    "features": [{
        "type": "Feature",
        "properties": {
            "value": 0
        },
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [-77, 40],
                    [-77, 41],
                    [-76, 41],
                    [-76, 42],
                    [-75, 42],
                    [-75, 41],
                    [-76, 41],
                    [-76, 40],
                    [-77, 40]
                ]
            ]
        }
    }]
}

I have converted that to a sqlite database with the following command:

 ogr2ogr -f SQLite bar.sqlite foo.geojson

So far so good, the data in the sqlite file looks good in qgis. Finally I want to use a sql query on the data to transform it in some way. For example:

ogr2ogr -f SQLite -dialect sqlite -sql "select st_buffer(geometry, -1) as geometry, ogc_fid, value from foo" baz.sqlite bar.sqlite -nln baz

The command completes successfully but it will not load into qgis. So I take a look at what is inside of it. And yes it has my one feature's properties, but there is no geometry attached:

sqlite> select * from baz; 1||0 sqlite> select * from geometry_columns; sqlite>

If I change my sql to select * everything works as expected but it seems I can't do anything interesting to the geometry and have that be the result in my new db/table.

I have a more complicated dataset in real life but here I'll include a simplified case to illustrate what is happening. I'm almost certain it due to my lack of knowledge on how to use ogr2ogr properly for my use-case.

So to start I have a geojson file, call it foo.geojson:

{
    "type": "FeatureCollection",
    "features": [{
        "type": "Feature",
        "properties": {
            "value": 0
        },
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [-77, 40],
                    [-77, 41],
                    [-76, 41],
                    [-76, 42],
                    [-75, 42],
                    [-75, 41],
                    [-76, 41],
                    [-76, 40],
                    [-77, 40]
                ]
            ]
        }
    }]
}

I have converted that to a sqlite database with the following command:

 ogr2ogr -f SQLite bar.sqlite foo.geojson

So far so good, the data in the sqlite file looks good in qgis. Finally I want to use a sql query on the data to transform it in some way. For example:

ogr2ogr -f SQLite -dialect sqlite -sql "select st_buffer(geometry, -1) as geometry, ogc_fid, value from foo" baz.sqlite bar.sqlite -nln baz

The command completes successfully but it will not load into qgis. So I take a look at what is inside of it. And yes it has my one feature's properties, but there is no geometry attached:

sqlite> select * from baz;
1||0
sqlite> select * from geometry_columns;
sqlite>

If I change my sql to select * everything works as expected but it seems I can't do anything interesting to the geometry and have that be the result in my new db/table.