[GIS] How to use shp2pgsql to import a very large shapefile into PostGIS

postgisshp2pgsql

I need to import very large datasets into the Postgis server so I need to use the -D option in the prompt. Problem is that I'm getting an error no matter what I do.

The command lines I'm using are: 

shp2pgsql -D "C:\Documents\Work\Data\AddressBase\AB4.shp" AB | psql -h serverip -p 5432 -U name

The DBF has over 14GB, 1,000,000 rows and 17 columns

I'm getting the following

enter image description here

I have the latest pgadmin and postgis. Is my command line wrong in some way?

Also am I missing a password input?

Best Answer

The shapefile specification states that the files are limited to two gigabytes (2^31-1 for .dbf -- 2^31-2 for .shp [because the records are counted in short integer chunks)]). Some open source utilities can handle one overflow (2-4Gb). 14Gb is far too large for any reader that uses the offsets stored in .shx.

Your choices:

  1. Break the original data source into ten 1.4Gb shapefile chunks (or seven 2.0Gb chunks)
  2. Regenerate the shapefile with a reduced file width of 1900-2000 bytes/record (this assumes the .shp geometry file isn't also over 2Gb)
  3. Use a different format (file geodatabase, ASCII,..)
Related Question