Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#6529 closed defect (fixed)

ogr2ogr failing to convert JSON 'esriFieldTypeString' type data in to correct PostgreSQL type

Reported by: basteed Owned by: warmerdam
Priority: normal Milestone: 1.11.5
Component: OGR_SF Version: unspecified
Severity: normal Keywords: ogr2ogr postgres varchar esri
Cc:

Description

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.

{"name":"Add1","type":"esriFieldTypeString","alias":"Add1","length":2147483647}

Thanks in advance for looking at this.

Attachments (1)

gazetteer.json (2.7 KB ) - added by basteed 8 years ago.

Download all attachments as: .zip

Change History (7)

by basteed, 8 years ago

Attachment: gazetteer.json added

comment:1 by Even Rouault, 8 years ago

Resolution: fixed
Status: newclosed

In 34297:

ESRI Json reader: do not set field width of 2147483647 (closes #6529)

comment:2 by Even Rouault, 8 years ago

In 34298:

ESRI Json reader: do not set field width of 2147483647 (closes #6529)

comment:3 by Even Rouault, 8 years ago

In 34299:

ESRI Json reader: do not set field width of 2147483647 (closes #6529)

comment:4 by Even Rouault, 8 years ago

In 34300:

ESRI Json reader: do not set field width of 2147483647 (closes #6529)

comment:5 by Even Rouault, 8 years ago

Component: defaultOGR_SF
Milestone: 1.11.5

comment:6 by basteed, 8 years ago

Thanks for the instant fix!

Note: See TracTickets for help on using tickets.