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