laurenz
commented
6 years ago Thanks for the information! It would be great if you could add something about that to the oracle_fdw Wiki.
I personally do not have the resources (or knowledge) to extend the GIS part substantially, but if you can come up with a patch, I'd be willing to add it as I think that would be desirable functionality.
The GIS code does not attempt to cover all bases, but wherever an improvement can be made with little code churn, it is welcome.
Hi Laurenz,
Here is another spatial item for consideration or the wiki. As always these items involve nuances between PostGIS and Oracle Spatial and its a toss up whether Oracle FDW should try to fix the issues or just pass it through for the user to deal with. It may boil down to whether one views Oracle FDW as a live database link or as stand-alone ETL process that needs post-processing steps.
Polygons in both PostGIS and Oracle Spatial are modeled as a series of rings with one as the outer ring of the polygon and zero to many inner rings as holes. PostGIS uses the OGC WKT specification to model these rings and WKT rings have no particular orientation. Oracle conversely demands that outer rings are wound counter clockwise and inner rings are wound clockwise. Any deviation on the Oracle side makes the geometry invalid (as ORA-13367). Oracle has long ago stated they are uninterested in the topic and it's the user's responsibility to sort it out. See https://community.oracle.com/message/3223747#3223747
So when PostGIS polygons are transferred to Oracle via Oracle FDW, it's a bit of a crap shoot as to whether they will be valid or not on the Oracle side. It seems like PostGIS largely just ignores the windings of polygons, so if your polygons come from Oracle originally or via some Esri workflow, often they walk and talk just fine as needed by Oracle Spatial. However I have found more recently that my polygons pushed through by Oracle FDW all end up invalid in Oracle. The solution is not overly onerous, it just involves a separate step running SDO_MIGRATE.TO_CURRENT on your Oracle geometries. But if the expectation is that Oracle FDW should take a valid PostGIS polygon and produce a valid Oracle Spatial geometry in one step, that is not necessarily happening currently. I am not certain this is a problem for the Oracle FDW project or not.
If Oracle FDW were to consider a fix, you could crib logic from here https://github.com/pauldzy/DZ_SDO/blob/master/Packages/DZ_SDO_UTIL.pkb#L1120 When converting from PostGIS to Oracle the logic would be something along the lines for each straight-line PostGIS polygon ring encountered, check the outer ring orientation is counter clockwise and inner rings are clockwise. If not then reverse the rings as needed before writing to Oracle. When converting from Oracle to PostGIS there is no additional logic needed.
Cheers, Paul