Opened 12 months ago

Closed 12 months ago

Last modified 12 months 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 12 months ago.

Download all attachments as: .zip

Change History (7)

Changed 12 months ago by basteed

Attachment: gazetteer.json added

comment:1 Changed 12 months ago by Even Rouault

Resolution: fixed
Status: newclosed

In 34297:

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

comment:2 Changed 12 months ago by Even Rouault

In 34298:

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

comment:3 Changed 12 months ago by Even Rouault

In 34299:

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

comment:4 Changed 12 months ago by Even Rouault

In 34300:

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

comment:5 Changed 12 months ago by Even Rouault

Component: defaultOGR_SF
Milestone: 1.11.5

comment:6 Changed 12 months ago by basteed

Thanks for the instant fix!

Note: See TracTickets for help on using tickets.