2

I have a .shp file from here, and I want to dump it into an .sql file, or a postgres/postgis database, but everytime I use shp2pgsql I get this:

shp2pgsql -s 4326 -a Europe_coastline.shp > test.sql
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
Segmentation fault

What am I doing wrong, or how can I fix it?

  • The data is in EPSG:3035 not 4326. If you want to transform it you need to use -s 3035:4326 or leave the -s operator. – Matte May 18 '17 at 14:25
  • @Matte Still the same. – Newwt May 19 '17 at 6:44
2

The error happens because in this shapefile the "shape_leng" attribute is defined in the .dbf file as REAL(19.11) but the value 275736334.13200003000 is too big to fit into such numeric.

shp2pgsql obviously takes the field definition from the .dbf file and creates the field into PostGIS as "shape_leng" NUMERIC(19,11)" and insertion of data will fail. GDAL is doing the same by default but the GDAL PostGIS driver http://www.gdal.org/drv_pg.html has a layer creation option PRECISION that is YES by default but can be turned to NO. With -lco PRECISION=NO the field will be created as "shape_leng" FLOAT8" and insert will success.

ogr2ogr -f Postgresql PG:"host=localhost  port=5432  dbname=db  user=user password=password"  -nln coastline -nlt promote_to_multi  Europe_coastline.shp -lco precision=no

If you definitely want to use shp2pgsql you must make the data valid first. One option is to open the shapefile with OpenJUMP and save a copy. That will change the field definition into REAL(33.16)

EDIT How to confirm that the error comes from PostgreSQL and is caused by too large numeric value to fit the columnn.

CREATE TABLE foo (test NUMERIC(19,11)); 
insert into foo values(275727286.55400002003);  

Result:
ERROR:  numeric field overflow
DETAIL:  A field with precision 19, scale 11 must round to an absolute value less than 10^8.
  • A scale of 11. What are they going to the micron? 0.00001 mm? – Evan Carroll May 18 '17 at 17:30
  • 1
    Shape_length is usually a technical attribute that is computed by ESRI software. – user30184 May 18 '17 at 17:37
  • Ouch. That seems like a pain in the arse, to be honest. I just downloaded OpenJUMP, but can't find the "save layer" option (doesn't help it's not in my language, even though I set it to english). Mind telling me where it is? Also, thank you very much for the help so far, I was going crazy thinking I was alone! – Newwt May 19 '17 at 8:03
  • Right click on top of the layer name in the layer list opens a pop-up menu. Use Save Dataset As. – user30184 May 19 '17 at 9:46
  • Edited because it was just too silly. I forgot to add "LINESTRING" after -nlt. I now get the same DETAIL error as you do and Command: INSERT INTO "coastline" ("shape_leng") VALUES (275727286.55400002003) RETURNING "ogc_fid". Should I try with -skipfailures? – Newwt May 19 '17 at 10:03

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.