3

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?

  • 2
    Try ogr2ogr gis.stackexchange.com/a/74818/7086 – Dmitri Chubarov May 26 '16 at 10:23
  • 1
    Please make sure that the error message is included in the question body as text. The error message indicates a file corruption issue, so it's likely not a password issue. It's extremely difficult to use shapefiles for "very large" data, since both the dBase and .shp files are limited to two gigabytes. Your message is consistent with a file overflow. Please edit the question ro specify the file sizes and number of records, with an indication of the width of each dBase record (sum of field widths). – Vince May 26 '16 at 10:30
  • 1
    Your question does not mention the use of ArcGIS for Desktop and/or QGIS so I am removing those tags. – PolyGeo May 26 '16 at 10:44
  • 1
    Any shapefile set where the .dbf exceeds 2^31-2 bytes is not valid. You need to use a different format. – Vince May 26 '16 at 11:21
  • 1
    Can you please specify the PostGIS version? Saying "the latest" is not useful, because this question may be found years in the future. – Lee Hachadoorian May 26 '16 at 19:21
7

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,..)
  • About your options, would the 1st one be quicker in QGIS?? by selecting 10% of features and exporting them to a new file and then finally importing everything to Postgis. And about the 3rd option, would a CSV be better and would not make me lose my spatial coordinates? – Luffydude May 26 '16 at 12:14
  • I doubt there's a performance difference, though you should make sure you avoid building indexes until all the data is loaded. I've loaded hundreds of gigabytes of tab- and pipe-delimited ASCII using Well-Known Text for geometry (steer away from CSV because the commas conflict with WKT) – Vince May 26 '16 at 21:27
  • Actually my QGIS froze whenever I tried selecting more than 10k features. I've tried using the import command on pgadmin and I got errors using a csv so I'm not sure how to solve my problem – Luffydude May 27 '16 at 9:00
  • The shapefile is not valid. Selection within it is not possible (the overfowed offsets make it impossible to do selection). You need to generate the fractional shapefiles from whatever was the source of the oversize shapefile. – Vince May 27 '16 at 10:27
  • How can I generate fractional shapefiles from a CSV? – Luffydude May 27 '16 at 10:36

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.