Skip to main content
Question

Oracle Spatial to PostGIS conversion speed issue

H
Forum|alt.badge.img

We are attempting to convert Oracle Spatial databases to PostGIS, and find it is taking a very long time to convert large 4269 SRID lines and polygons at roughly 1-2 per second, with smaller ones at around 10-20 a second. I am also very puzzled while for very long periods it will seemlingly do nothing but burn CPU (no new rows). I've tried both 9.6/2.3 (Azure for PostgreSQL PaaS) and 10.3/2.4 (on-prem) with the same results.

For instance, it took 2 hours 54 minutes to convert 2136 rows, or about 12.3 a second.

Is the overhead for converting the SDO_GEOMETRY number based geometries to the geography binary type really that high? Any suggestions for getting better conversion speed? Currently using 2017.1 - will a newer version run significantly quicker?

6 replies

H
Forum|alt.badge.img
  • hallkbrdz
  • Author
  • June 7, 2018

This may be the problem - FME is for some reason changing the SRID from the source - 4269, with this as it creates the table and columns:

ALTER TABLE "GISCHK"."WATERSHED_02" ADD COLUMN "geom" GEOGRAPHY(GEOMETRY, 900914)

This is using dynamic mode. I am currently converting this table that has a total of 13 polygons and 5 multipolygons, with a total count of 2838132 verticies. It fairly quickly converted 2 rows - and now it's been stuck there for almost an hour. Here is the breakdown on those geometries:

SDO_UTIL.GETNUMVERTICES(A.SHAPE) SHAPE.SDO_SRID SHAPE.SDO_GTYPE

 

153989 4269 2003

 

156528 4269 2003

 

125412 4269 2003

 

165646 4269 2003

 

110962 4269 2007

 

178262 4269 2003

 

127861 4269 2003

 

182909 4269 2003

 

476521 4269 2003

 

109531 4269 2003

 

48461 4269 2003

 

105052 4269 2003

 

122725 4269 2007

 

257800 4269 2007

 

131682 4269 2003

 

209175 4269 2007

 

87250 4269 2003

 

88366 4269 2007

 

H
Forum|alt.badge.img
  • hallkbrdz
  • Author
  • June 8, 2018
I know the problem, and no 2018 doesn't help. It's the multipolygon shapes. It processes polygons reasonably quickly, but then hits a multipolygon and stalls out - not using 100% CPU of a core. I'm not sure what it is doing other than taking forever to convert it from a SDO_GEOMETRY to geography data type.

 

 

Is this to be expected with PostGIS?

 

 

H
Forum|alt.badge.img
  • hallkbrdz
  • Author
  • June 8, 2018
I think something (FME or PostGIS) REALLY doesn't like the large number of (large) polygons in the multipolygon shape. The first problem child shape log reports:

 

 

Area Number: 1238 Geometry Type: IFMEPolygon

 

Boundary:

 

Geometry Type: IFMELine

 

Number of Coordinates: 15021 -- Coordinate Dimension: 2

 

 

I looked at this particular shape, and it actually makes sense as a large watershed area with lots of detailed boundary polygons. Now I just have to find a way to load these in a reasonable amount of time...

 

redgeographics
Celebrity
Forum|alt.badge.img+48
hallkbrdz wrote:
I think something (FME or PostGIS) REALLY doesn't like the large number of (large) polygons in the multipolygon shape. The first problem child shape log reports:

 

 

Area Number: 1238 Geometry Type: IFMEPolygon

 

Boundary:

 

Geometry Type: IFMELine

 

Number of Coordinates: 15021 -- Coordinate Dimension: 2

 

 

I looked at this particular shape, and it actually makes sense as a large watershed area with lots of detailed boundary polygons. Now I just have to find a way to load these in a reasonable amount of time...

 

So just to clarify: you have a multipolygon with 1238+ children and at least one of them has 15000+ vertices? How big is that multipolygon alltogether?

 

 

You could consider using the Chopper to reduce the complexity of the polygons without reducing their detail level. It will cut up polygons in smaller ones with a maximum number of vertices and can also deaggregate the multipolygons into individual ones. But the question is whether you want that as it will change the number of features.

 

 

lars_de_vries
Forum|alt.badge.img+10
hallkbrdz wrote:
I know the problem, and no 2018 doesn't help. It's the multipolygon shapes. It processes polygons reasonably quickly, but then hits a multipolygon and stalls out - not using 100% CPU of a core. I'm not sure what it is doing other than taking forever to convert it from a SDO_GEOMETRY to geography data type.

 

 

Is this to be expected with PostGIS?

 

 

Have you considered to deaggregate the polygon and aggregate them after reprojecting them? And I am not sure where you are doing the reprojection, perhaps the Reproject-transformer is quicker than doing it during writing?

 

 

lars_de_vries
Forum|alt.badge.img+10

This thread gives a number of tips & tricks for database writing. Perhaps you can find some usefull tips there.

Reply


Helpful Members This Week

Community Stats

30,921
Posts
117,181
Replies
38,725
Members

Latest FME

Terms & ConditionsCookie settings

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
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.