Tuesday, 6 October 2015

ogr2ogr error importing shapefile into PostGIS : "numeric field overflow"


I'm trying to import a shapefile into a PostGIS database using ogr2ogr as follows:


ogr2ogr -lco GEOMETRY_NAME=geom -lco LAUNDER=NO -a_srs "EPSG:4326" -f "PostgreSQL" PG:"dbname=db" test.shp


This is after trying the import with shp2pgsql and encountering an issue OGR integer type to PostgreSQL BIGINT?. The shapefile was a Tiger/Line file circa 2000 if I remember correctly. But I am getting the following error:


ERROR 1: COPY statement failed.
ERROR: numeric field overflow
DETAIL: A field with precision 19, scale 11 must round to an absolute value less than 10^8.
CONTEXT: COPY test, line 1, column TLID: "142691303.00000000000"

The TLID field in question shows as double real 18 11 for type, type name, length and precision in QGIS 3/2.99. I did some quick search, and TLID seems to be TIGER/Line ID. I don't why it is shown as a double since it's an ID, or why is it causing this error for ogr2ogr.


This is tested under Ubuntu 16.04, and ogr2ogr --version shows:


GDAL 2.2.1, released 2017/06/23


Does anyone know what went wrong and how to fix this to allow importing into PostGIS?


-- EDIT --


The dbfdump -m output for the file is:


Record: 0
STATEFP: 06
COUNTYFP: 037
COUNTYNS: (NULL)
TLID: 142691303.00000000000
TFIDL: 219999921.00000000000

TFIDR: 219999382.00000000000
MTFCC: S1400
FULLNAME: Fashion Ave
SMID: 2361
LFROMADD: (NULL)
LTOADD: (NULL)
RFROMADD: (NULL)
RTOADD: (NULL)
ZIPL: (NULL)
ZIPR: (NULL)

FEATCAT: (NULL)
HYDROFLG: N
RAILFLG: N
ROADFLG: Y
OLFFLG: N
PASSFLG: (NULL)
DIVROAD: N
EXTTYP: N
TTYP: (NULL)
DECKEDROAD: N

ARTPATH: (NULL)
Shape_Leng: 42.48449176210

Record: 1
STATEFP: 06
COUNTYFP: 037
COUNTYNS: (NULL)
TLID: 141692965.00000000000
TFIDL: 219999899.00000000000
TFIDR: 219999383.00000000000

MTFCC: S1630
FULLNAME: (NULL)
SMID: 2361

Answer



For making the conversion to success read the manual page http://www.gdal.org/drv_pg.html from section Layer Creation Options



PRECISION: This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if available using NUMERIC(width,precision) or CHAR(width) types. If "NO" then the types FLOAT8, INTEGER and VARCHAR will be used instead. The default is "YES".



In your shapefile the field definition and data do not match but you should be able to workaround that by unsetting the "precision" in your org2ogr command


-lco precision=NO

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...