Skip to main content
Question

Reading .gdb into PostGIS is very very very slow, takes weeks

  • December 11, 2019
  • 7 replies
  • 63 views

Hi,

I am using a reader to read the local .gdb file (around 11 GB) and write one of the feature classes into a Postgresql table. Reading is quite slow especially after processed 100,000 features, while the writing is at normal speed and is not an issue at all. So far, it already taken me around 10 days to read and write only 400,000 features into warehouse, still not finished.

I assume it is because memory shortage, so I also disabled the feature caching. Anything else I can do to speed up the work? have been stuck for 2 weeks.

7 replies

bwn
Evangelist
Forum|alt.badge.img+26
  • Evangelist
  • December 11, 2019
  1. Where is the local File Geodatabase? Some Windows/OS folders are not really local at all, but may have something like a symbolic link to a slower network storage.
  2. What types of Feature Classes are you trying to translate? Some ArcGIS classes translate horribly slowly Eg. Dimensions. once you start to go outside the usual Polygons, Polylines and Points.
  3. Have you tried instead writing to another local storage file Eg. To a SpatiaLite file. This helps eliminate the variable of how much of an impact writing to PostgreSQL is, which will have its own network/server overheads to deal with.

sigtill
Supporter
Forum|alt.badge.img+24
  • Supporter
  • December 11, 2019

1. Put the gdb file on local disk - SSD disk, not HDD

2. Do a GDB -> FFS conversion - see if this is faster


  • Author
  • December 11, 2019
sigtill wrote:

1. Put the gdb file on local disk - SSD disk, not HDD

2. Do a GDB -> FFS conversion - see if this is faster

I am working on MacOS and it is SSD. Why FFS conversion makes it faster?


  • Author
  • December 11, 2019
bwn wrote:
  1. Where is the local File Geodatabase? Some Windows/OS folders are not really local at all, but may have something like a symbolic link to a slower network storage.
  2. What types of Feature Classes are you trying to translate? Some ArcGIS classes translate horribly slowly Eg. Dimensions. once you start to go outside the usual Polygons, Polylines and Points.
  3. Have you tried instead writing to another local storage file Eg. To a SpatiaLite file. This helps eliminate the variable of how much of an impact writing to PostgreSQL is, which will have its own network/server overheads to deal with.

1. It is MacOS 2. Polygons 3. Not yet. Can try.


redgeographics
Celebrity
Forum|alt.badge.img+49

You say you're writing "one of the feature classes" to PostGIS, are you reading the other ones as well? If that's the case, don't do that.

 


sigtill
Supporter
Forum|alt.badge.img+24
  • Supporter
  • December 11, 2019
nuistzhou wrote:

I am working on MacOS and it is SSD. Why FFS conversion makes it faster?

It is just to see if the process is faster -> FFS than -> PostGIS. If it is significantly faster then I would suggest doing GDB -> CSV with WKT_Geometry and then import using bulk import in postgres


  • Author
  • December 11, 2019
redgeographics wrote:

You say you're writing "one of the feature classes" to PostGIS, are you reading the other ones as well? If that's the case, don't do that.

 

no, just reading that certain feature class.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings