4

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
  • Is it possible to you read the structure of the dbf file (dbfdump or xbase perl dbf_stat)? – huckfinn Sep 7 '17 at 23:02
  • Is it possible that both tools are reading only a segment of the data and guessing the type and length of the columns it creates, which are not accounting for the rest of the data that now won't fit into those columns? – DPSSpatial Sep 7 '17 at 23:12
  • I think this is the output of one of the shapelib tools. What says dbfdump --info for the structure (field definitions) of the dbf. I assume it uses N - numeric instead of I - integer for the tiger line ID, beause of it's "text nature" (..see dbf specs). – huckfinn Sep 8 '17 at 7:25
9

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
0

Note the OGR error is: DETAIL: A field with precision 19, scale 11 You said The TLID field in question shows as double real 18 11

It is NOT of precision 18,11 it is of precision 19,11. - 10^8 (19-11) = 100,000,000 - 10^7 (18-11) = 10,000,000 - Your value is 142,691,303

The best solution would be to change the Postgres data type to double precision (I'm assuming it's NUMERIC(18,11) right now?) as that is a more flexible datatype and generally accepted as a safer choice. You could also change it to at least NUMERIC(19,11) or NUMERIC(18,10) if you don't care about losing one number after the decimal.

By the way, those numbers- 19 and 11- refer to the total number of digits in the number and the number of digits after the decimal point.

NUMERIC(19,11) ^--A number with 11 digits after the decimal point ^-----A number with a total of 19 digits Which leaves 19 - 11 = 8 digits to the left of the decimal point, which explains why it is screaming over a value that is > 99,999,999

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.