1

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?

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

2 Answers 2

Reset to default
4

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
Improve this answer
13
  • 3
    Dll error means that there is something wrong with your GDAL installation.
    – user30184
    Commented May 26, 2016 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
    Commented May 27, 2016 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
    Commented May 27, 2016 at 11:29
  • 1
    Before new trial drop the table or run ogr2ogr with -overwrite.
    – user30184
    Commented May 27, 2016 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
    Commented May 27, 2016 at 12:04
0

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.

Improve this answer
4
  • 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
    Commented May 26, 2016 at 16:19
  • Can you successfully load into PostGIS a small subset (say, 1,000 records) of the shapefile to begin with?
    – Simbamangu
    Commented May 26, 2016 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
    – simpleuser001
    Commented May 27, 2016 at 6:39
  • @Simbamangu The small shapefile I have with only 150Mb loaded fine, the problem are all the other ones with >10Gb
    – Luffydude
    Commented May 27, 2016 at 8:56

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.