ogr2ogr failing to convert JSON 'esriFieldTypeString' type data in to correct PostgreSQL type
|Reported by:||basteed||Owned by:||warmerdam|
|Severity:||normal||Keywords:||ogr2ogr postgres varchar esri|
I am executing the following command to import GeoJSON from an ESRI source into a PostgreSQL database named 'jersey' hosted locally on my machine (Mac OS X 10.11.5) I use GDAL 1.11.3 (from Homebrew) and PostgreSQL v9.5.1.
ogr2ogr -overwrite -f "PostgreSQL" PG:"host=localhost port=5432 dbname=jersey user=postgres" "gazetteer.json" -nln _gazetteer -a_srs EPSG:3109 -nlt POINT
I get a series of errors of the following type:
ERROR 1: ALTER TABLE "_gazetteer" ADD COLUMN "add1" VARCHAR(2147483647) ERROR: length for type varchar cannot exceed 10485760 LINE 1: ALTER TABLE "_gazetteer" ADD COLUMN "add1" VARCHAR(214748364...
All such columns are excluded from the import - the remaining columns import OK.
The error seems to stem from the fact that GDAL is trying to convert data of type 'esriFieldTypeString' to Postgres type VARCHAR. In this case it should be using Postgres type TEXT - as as the error says VARCHAR has a size limit less than the value specified by ESRI in this case.
For reference it appears to be similar to the error here https://github.com/evolute-pt/dbtransfer/issues/13
The only workaround I can think of it to manually edit the JSON to change the size parameter to within VARCHAR limits - not ideal!
Here is a sample of the relevant JSON. I have tried to attach the whole JSON file.
Thanks in advance for looking at this.