I need to split my shapefiles into much smaller pieces so that I can upload them onto the Postgis server. Shapefiles have DBF files with 14 Go (1,000,000 rows) and I need them to be under 2 Go.

I've tried selecting 100,000 rows to then try and save the selected features as a different file but QGIS just instantly freezes.

Is there a better way to do this?

  • 1
    How did you manage to generate a DBF of that size, I didn't think that was possible? – Steven Kay May 26 '16 at 22:03
  • it was generated from a CSV – Luffydude May 27 '16 at 9:26

Instead of playing with workarounds try if GDAL can do the job for you directly. If command

ogrinfo -al -so your_big_shapefile.shp

seems successful you have good chance to have luck with ogr2ogr as well. Read http://www.gdal.org/ogr2ogr.html and http://www.gdal.org/drv_pg.html and try

ogr2ogr -f PG PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" your_big_shapefile.shp
  • 3
    Dll error means that there is something wrong with your GDAL installation. – user30184 May 26 '16 at 14:49
  • 1
    It seems that the file name gets truncated at the space character in your path C:\users\Pedro Santos\... I am not sure how to make your FOR command to work with spaces. Myself I would not even try but use a path without spaces. – user30184 May 27 '16 at 10:37
  • 1
    The error may mean that GDAL does can't interpret the native projection of your shapefile and it tries to insert some dummy projection into PostGIS. Or then it finds a projection but PostGIS is missing just that projection. Your user name does not have rights to do inserts into spatial_ref_system which leads to an error. Find out the EPSG code of your source data and add the -a_srs parameter into your ogr2ogr command like -a_srs epsg:4326. – user30184 May 27 '16 at 11:29
  • 1
    Before new trial drop the table or run ogr2ogr with -overwrite. – user30184 May 27 '16 at 11:49
  • 1
    So drop the index with pgAdmin. I would have thought that -overwrite takes care of dropping the index as well but for some reason it did not happen for you. – user30184 May 27 '16 at 12:04

Avoid loading the shapefile in QGIS first - use the import layer/file button in the DB Manager dialog.

enter image description here

Select the file, not a loaded layer, with the button to the right of the Input box. You'll need to specify CRS and have the option to reproject in the same step:

Tested this with 200,000 features - ~ 1 minute to import.

  • Not sure if you have a super futuristic computer with NASA level internet connection but for me even without the layer being loaded it only imported 1,400 features out of the 1 million in one minute – Luffydude May 26 '16 at 16:19
  • Can you successfully load into PostGIS a small subset (say, 1,000 records) of the shapefile to begin with? – Simbamangu May 26 '16 at 18:27
  • you can allways use shp2psql bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg . For speed remove indexes on insert recreate them after you have imported data – simplexio May 27 '16 at 6:39
  • @Simbamangu The small shapefile I have with only 150Mb loaded fine, the problem are all the other ones with >10Gb – Luffydude May 27 '16 at 8:56

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.