GDAL – SQL Functions Supported by ogr2ogr and sqlite

gdalogr2ogrsqlsqlite

I have a GeoJSON, test.geojson:

{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 12.34, 56.78 ] } }
]
}

I would like to swap the coordinates using ogr2ogr. After Googling a bit, I arrive to the ST_FlipCoordinates function:

ogr2ogr -dialect sqlite -sql "SELECT ST_FlipCoordinates(geometry) as geometry FROM test" test_swapped_coords.geojson test.geojson

resulting in the error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT
ST_FlipCoordinates(geometry) as geometry FROM test): no such
function: ST_FlipCoordinates

After more trial and error, I realize I can solve my problem via:

ogr2ogr -dialect sqlite -sql "SELECT SwapCoordinates(geometry) as geometry FROM test" test_swapped_coords.geojson test.geojson

giving me test_swapped_coords.geojson:

{
"type": "FeatureCollection",
"name": "SELECT",
"features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "Point", "coordinates": [ 56.78, 12.34 ] } }
]
}

SwapCoordinates works, ST_FlipCoordinates does not work. This leads me to the question:

How can I know which SQL functions are supported by ogr2ogr?

and which -dialect I need to be in if I want to use them. If there is a list somewhere, or I need to look at a particular place within the source code, that would work. I would like to know where to look.

Best Answer

The doc says:

The syntax of the SQL statements is fully the one of the SQLite SQL engine.

and

Spatialite SQL functions

When GDAL/OGR is build with support for the Spatialite library, a lot of extra SQL functions, in particular spatial functions, can be used in results column fields, WHERE clauses, etc….

and it includes a link to the spatialite functions

Related Question