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
    Commented May 18, 2017 at 14:25
  • @Matte Still the same.
    – Newwt
    Commented May 19, 2017 at 6:44

1 Answer 1


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);  

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? Commented May 18, 2017 at 17:30
  • 1
    Shape_length is usually a technical attribute that is computed by ESRI software.
    – user30184
    Commented May 18, 2017 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
    Commented May 19, 2017 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
    Commented May 19, 2017 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
    Commented May 19, 2017 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.