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
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?
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...
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.
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?
This thread gives a number of tips & tricks for database writing. Perhaps you can find some usefull tips there.
