3

I have a file geodatabase created with ArcMap 10.1 that contains one feature class with 650 million points. The feature class contains the shape field and an identifier. The .gdb is approximately 26GB.

I'm using ogr2ogr to import the .gdb into a PostgreSQL database. I started the process and the features are being inserted into the database. I verified this with a SELECT COUNT(*) FROM <TABLE> and the number of rows is increasing.

Based on my rudimentary approach to timing - watch the PC clock and use the above SELECT - I estimate the import is progressing at 7 million features per hour. Some quick mental math and I'm looking at 90+ hours to import the .gdb - if the rate remains the same and doesn't degrade over time. This is roughly 2000 features per second.

The database is an out of the box PostgreSQL 9.4.5 / PostGIS 2.2 installation on a Windows 10 PC with a SSD (400 GB free / 512GB total) and 16GB RAM. No PostgreSQL configuration changes have been made yet as I'm not sure what, if anything, I should do.

Is there anything I can do to increase performance of the .gdb import? Perhaps a recommendation that maximizes writes and then I can rollback the changes after the imports? I could let this process run until completion but there will be other imports I need to perform and I'd rather not wait four days for each one. I have full control of this PC so I'll cautiously say I'm open to any suggestions.

Update: I'm using the following command and options. I don't know how to check if this is the open or ESRI library. The version of GDAL is 1.12 (95% sure) and was installed with QGIS 2.12 Lyon (100% sure of this).

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=spatial_playground user=postgres password=********" c:\gis_data\pointdata\points.gdb 

The database and .gdb are on the same hard drive so contention is probably impacting this. I considered throwing the .gdb on a USB 3.0 hard drive. The OGR command is running on the same machine. Everything is being done on the same machine; No network involved yet.

8
  • 1
    Do you use COPY gis.stackexchange.com/questions/109564/…? – user30184 Dec 4 '15 at 19:06
  • @user30184 - Nope but I will be trying that later today. Thanks for the link. I'll post when I have some results... – DenaliHardtail Dec 4 '15 at 19:16
  • 1
    I've had colleagues use my asc2sde loader to import ~650M rows into Oracle, SQL-Server, and PostgreSQL databases (3 different boxes) from ASCII files in ~20 hours (and that was ~4 years ago), so faster should be possible. SSD is not perfect for database use, because increased write frequency is associated with drive failure. Please edit the question to specify which OGR access library is used on the .gdb (Open or Esri) and whether the FGDB is local or networked (relative to the ogr2ogr), and if the OGR session is local to the database host. – Vince Dec 4 '15 at 19:19
  • If it's all one host, are you using localhost, and are there multiple disks to eliminate contention between the FGDB read and the database write? – Vince Dec 4 '15 at 19:22
  • @Vince - updated question with more detail – DenaliHardtail Dec 4 '15 at 19:39
2

To emphasize @user30184's remark, try applying the OGR PostGRES driver config option to your ogr2ogr instruction: --config PG_USE_COPY YES

Additionally, it might help to disable the AUTOVACUUM setting in the PostGRES conf, I know I've seen that mentioned in some OSM tips and tricks for improving import performance.

And over in this thread, I found a few additional conf settings that might be worth a shot. In that thread, the OP's system had just 1 GB RAM, so you may be able to expand these values if your system is more robust:

max_connections =20 shared_buffers =30000
work_mem = 8192
maintenance_work_mem = 32768
checkpoint_segments = 12

1
  • 2
    PI would also test with -lco SPATIAL_INDEX=OFF. It may be faster to create it afterwards but with SSD drive the difference may not be so big. With old GDAL versions the -gt default was 200 which is all too low. Now the default is 20000. – user30184 Dec 5 '15 at 21:19

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.