ogr2ogr missing geometry when selecting from one sqlite db into another

Question

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.

Answer 1

Read carefully the GDAL SQLite driver document https://gdal.org/drivers/vector/sqlite.html. There are different ways to store spatial data into SQLite database and your command

ogr2ogr -f SQLite bar.sqlite foo.geojson

creates a FDO style database. Important thing to know about this is that the geometries get stored as WKB blobs. That could be controlled with a layer creation option:

FORMAT=WKB/WKT/SPATIALITE: Controls the format used for the geometry column. By default WKB (Well Known Binary) is used.

SQL dialect that is used against SQLite databases supports basic SQLite functions and SpatiaLite functions https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html. The SpatiaLite functions work on SpatiaLite geometries but there are also supporting functions that can change geometry encodings between FDO, SpatiaLite, GeoPackage, and some other formats.

What happens to you is that SpatiaLite function ST_Buffer is getting a WKB geometry as an input but the function requires SpatiaLite geometries. With WKB the function returns NULL and the geometry is lost.

There are two ways to resolve the problem:

1. Cast the WKB geometry into SpatiaLite geometry.

   ogrinfo bar.sqlite -sql "select ST_Buffer(GeomFromWKB(geometry),1) as geometry, ogc_fid, value from foo"

2. Create the SQLite database a SpatiaLite database

   ogr2ogr -f SQLite -dsco spatialite=yes bar.sqlite foo.geojson

I recommend the latter if you do not especially need FDO interoperability for some reason. Working with some old program versions from Autodesk of FME could be such a reason.