25

I have to import large Shapefiles (> 1 million records) into PostGIS, and I have been wondering about the best way to do it.

enter image description here

In my question I used the word "hack", instead of tool, on purpose because I think this is not so much a matter of which tool, but which set of steps, or configuration settings to use. So far, I have tried the SPIT plugin (QGIS), the shp2pgsql Postgis tool and the GDAL ogr2ogr tool. You can view my full review on this post. So far, I find all of them really unresponsive, when dealing with a large dataset. I was wondering if someone experienced a similar issue, and if you could share something about the approach.

Improve this question

2 Answers 2

Reset to default
22

I made a test for you:

  • PostgreSQL 9.3
  • PostGIS 2.1
  • Windows 7
  • i7 3770@3.4 GHz processor
  • GDAL 2.0-dev 64-bit
  • shapefile of 1.14 million polygons, file size 748 MB

Ogr2ogr command:

ogr2ogr -f PostgreSQL PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" test.shp --config PG_USE_COPY YES -nlt MULTIPOLYGON

Total time:1 minute 30 sec

Improve this answer
6
  • Thanks for your answer! It seems really fast; I think it may have not worked for me because I did not use the --config PG_USE_COPY YES flag; I just managed to import it quickly using: psql target-db -U <admin user> -p <port> -h <DB instance name> -c "\copy source-table from 'source-table.csv' with DELIMITER ','" (and then reconstructing the geometry), which I guess is a similar approach.
    – doublebyte
    Aug 6, 2014 at 12:51
  • COPY is faster and will be the default in GDAL 2.0 when data are written to new tables. When inserts are used, the default size of transactions (controlled with -gt parameter) was only 200 features before GDAL version 1.11 when it was increased to 20000 features. Bigger transactions mean less transactions and that can yield a huge speedup.
    – user30184
    Aug 6, 2014 at 12:59
  • 5
    Using COPY is key, and you'll probably get an even faster translation with shp2pgsql and the -D flag. shp2pgsql -D test.shp | psql testdb
    – Paul Ramsey
    Aug 6, 2014 at 14:43
  • Paul, is shp2pgsql -D the same as COPY? Not clear from the docs which say this uses "dump" format, but I'm not sure what that even means for an upload (as opposed to a backup/restore) operation. I notice that shp2pgsql-gui has an option "Load data using COPY rather than INSERT", but no "dump format" option, so am I correct in assuming these are the same?
    – Lee Hachadoorian
    Aug 12, 2014 at 19:14
  • Yes, -D is same as using COPY.
    – Darrell Fuhriman
    Aug 19, 2014 at 22:33
12

After the suggestions of user30184, Paul Ramsey and my own experiments. I decided to answer this question.

I failed to mention in this question that I am importing data to a remote server. (although it is described in the blog post I refer to). Operations such as inserts, over the internet are subject to a network latency. Perhaps it is not irrelevant to mention that this server is on Amazon RDS, which prevents me from ssh to the machine and run operations locally.

Having this in mind, I re-engineered my approach, using the "\copy" directive to promote a dump of the data into a new table. I think this strategy is an essential key, which was also referred on the comments/answers to this question.

psql database -U user -h host.eu-west-1.rds.amazonaws.com -c "\copy newt_table from 'data.csv' with DELIMITER ','"

This operation was incredibly fast. Since I imported a csv, I then had all the work of populating the geometry, adding a spatial index, etc. It was still remarkably fast, since I was then running queries on the server.

I decided to benchmark also the suggestions from user30184, Paul Ramsey. My data file was a point shapefile with 3035369 records, and 82 MB.

The ogr2ogr approach (using the PG_USE_COPY directive) finished in 1:03:00 m, which is still *much better than before.

The shp2pgsql approach (using the -D directive) finished in only 00:01:04 m.

It is worth to say that ogr2ogr created a spatial index during the operation, while shp2pgsql did not. I find out that it is much more efficient to create the index after doing the import, rather than bloating the import operation with this type of request.

The conclusion is: shp2pgsql, when properly parameterized, is extremely well suited to perform large imports, namely those to be accomodated whithin Amazon Web Services.

Spatial table with more than 3 million records, imported using shp2pgsql

You can read a more detailed description of these conclusions, on the update of this post.

Improve this answer
5
  • Before you accuse GDAL too much, have a look at the documentation. Ogr2ogr is not involved, it is rather the GDAL PostGIS driver and it does have an option for disabling spatial index gdal.org/drv_pg.html. Usage with ogr2ogr is to add -lco SPATIAL_INDEX=NO. GDAL has also another driver for PGDump which might suit your use case better gdal.org/drv_pgdump.html. Perhaps you will mention also these things in your blog.
    – user30184
    Aug 13, 2014 at 6:54
  • 1
    Speed difference 1:03:00 and 00:01:04 between ogr2ogr and shp2pgsql is huge. I am sure that it is real but the result can't be generalised. If you test with a local PostGIS database the difference will be much less. Your result means that something goes very bad for ogr2ogr. Which GDAL version did you use? If it is older than v. 1.11 did you have a try by increasing the size of transactions by with adding something like -gt 60000?
    – user30184
    Aug 13, 2014 at 7:05
  • 1
    It's no additional bloat to create in the index in the import than it is to do it afterwards. The command issued is exactly the same and the time it takes exactly the same. Also, if you want shp2pgsql to add the index, you just need to add the '-I' option.
    – Darrell Fuhriman
    Aug 19, 2014 at 22:31
  • Thanks for your comments. My case study was an import to a Postgres running on AWS, so it was important for me that the transaction performed well over the network. I did use the PG_USE_COPY flag on ogr2ogr, but I did no try the PGDump driver, which from the manpage looks promising. My version of GDAL is 1.7. I should benchmark everything in equality of conditions (with or without the index), but from what Daniel tells me this is not the problem, since I create the index pretty quickly in the database...
    – doublebyte
    Aug 20, 2014 at 6:43
  • 1
    Yes, case studies are OK if they have been written so that readers do not get a feeling that results can be generalized over to what they really represent. For example it would be good to mention that you did the test with 5 years old GDAL version and that some development may, or may not, happened since that. Your version for sure needs a bigger -gt value for performing well but anyway it does not make much sense to test with any older GDAL version than 1.10.
    – user30184
    Aug 25, 2014 at 7:38

Your Answer

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

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