0

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".

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
  • Okay this makes a lot of sense. Many thanks user30184 ! – Vlad Jun 14 '18 at 15:03

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.