ogr2ogr -sql command when appending to PostgreSQL database: what functions are available?

Question

I am trying to append a set of polygons from a shapefile to an existing table in a PostgreSQL/PostGIS database. The destination table only has a geography column and an 'attributes' column with jsonb data type, in which I would like to store all outstanding fields of the shapefile as a JSON. I am attempting something like this:

ogr2ogr -f PostgreSQL PG:dbname=destination_db input_polygons.shp \
 -nln destination_table -update -append -t_srs "EPSG:4326" \
 -sql "SELECT *, row_to_json(input_polygons) as attributes from input_polygons" 

and am getting:

ERROR 1: Undefined function 'row_to_json' used.

Should I assume that row_to_json() is not usable within the scope of the ogr2ogr -sql command? From the documentation files here and here, my understanding is that the -sql command passes SQL statements directly to PostgreSQL, and that "anything possible in SQL can then be accomplished".

Answer 1

PostgreSQL dialect is the default if you read data from PostGIS but now you read data from shapefile. Then the default dialect if OGR SQL http://www.gdal.org/ogr_sql.html and the only alternative dialect that you can use is SQLite/SpatiaLite SQL dialect.http://www.gdal.org/ogr_sql_sqlite.html.

SpatiaLite does have some support for JSON and GeoJSON http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html but equivalent for row_to_json is missing. I suggest to import the shapefile as a new table into PostGIS and append data to the destination table with SQL.

You can test that row_to_json works for reading from PostGIS:

ogrinfo PG:"host=localhost port=5432 dbname=test user=test password=test" -sql "select *, row_to_json(states) from states limit 1"

Beginning of the result:

Layer name: sql_statement
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
row_to_json: String (0.0)
OGRFeature(sql_statement):0
 row_to_json (String) = {"ogc_fid":1,"state_name":"Illinois","state_fips":"17","sub_region":"E N Ce
n","state_abbr":"IL","land_km":143986.610000000,"water_km":1993.335000000,"persons":11430602.0000000

• Vlad: Okay this makes a lot of sense. Many thanks user30184 !