4

I run an automated script that copies a number shapefiles into PostGIS. I have a problem with one dataset which fails during the export. As the roads.shp dataset is maintained by someone else, I automate making a copy of their shapefile into my PostGIS database. My error is;

ERROR 1: COPY STATEMENT FAILED
ERROR: numeric field overflow
DETAIL: A field with precision 13, scale 11 must round to an absolute value less than 10^2.
CONTEXT: COPY ccbc_roads, line 2, column length :"222.05900000000"

My code is as follows;

ogr2ogr.exe -f PostgreSQL PG:"dbname=postgis host=localhost user=postgres password=mypassword active_schema=ccbc_data" "roads.shp" -overwrite -a_srs "EPSG:27700" -lco GEOMETRY_NAME=geom -nln "ccbc_roads" -nlt PROMOTE_TO_MULTI --config PG_USE_COPY YES

The field that is causing the issue is the road length. Is there a way to change the scale from 11 to 2 in my code so that the script will work?

  • Can you change the definition for ccbc_roads in your database? The import has a value over a value of 99; unless you change your database, the value imported would be wrong otherwise. – Richard Morgan Apr 10 '15 at 10:37
  • I have changed the precision and scale to 13,2 on a copy of the shapefile and the ogr2ogr script worked. I had hoped there was a way to do this without changing the source shapefile, but I will suggest to the data owner that they amend their shapefile instead. – Lee Apr 10 '15 at 11:32
  • Out of interest, did you try with shp2pgsql? If this is for the new OS opendata (I make this assumption based on 27700) in which case, I've been able to import that AOK with shp2pgsql? – Schoenobates Apr 10 '15 at 11:54
  • No, I haven't used shp2pgsql. This is a dataset produced in house (not OS Open data). I use ogr2ogr to do a number of extract and transforms from various sources into PostGIS by a batch file overnight (because we use a QGIS/ PostGIS combo to accompany our corporate ESRI software). – Lee Apr 10 '15 at 13:06
  • slightly related but in my case I was still able to set the column types and using -lco precision=no solved the problem for me. gdal.org/drv_pg.html – RutgerH Dec 8 '16 at 19:39
2

There is more sustainable way for this which is using -unsetFieldWidth flag. With this setting you won't need to use sql and it applies all layers.

1

As the data owner cannot change the field types, I have used ogr2ogr and a -sql select statement to exclude the field that is causing problems. As the field, LENGTH, is derived data, then it shows up in QGIS anyway so I was ok to omit it.

My new code is as follows;

ogr2ogr.exe -f PostgreSQL PG:"dbname=postgis host=localhost user=username password=mypassword postgres active_schema=ccbc_data" "M:\data\roads.shp" -sql "select roads_id, form_of_wa, oneway, cat, class, route_numb, proper_nam, locality, place FROM roads" -overwrite -a_srs "EPSG:27700" -nln "ccbc_roads" -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES

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.