Circular Arcs in Geodetic Polygons
A customer presented me with an interesting problem.
For some reason, a geodetic polygon object (4283) had been created within their database. (Probably an external application that supports circular arcs in geodetic polygons – SQL Server 2012 does.)
The problem then was that the polygon needed to be transformed (projected) to a planar SRID. Validating the geometry gives “ORA-13035: Invalid data (arcs in geodetic data) in the SDO_GEOMETRY object”.
Trying to use SDO_CS.TRANSFORM on it gives “ORA-13285: Geometry coordinate transformation error”.
OK, so what is one to do? Here is a trick that dynamically forces the polygon geometry to look like a multi-point object, which is then transformed, and changed back to being a polygon.
WITH all_polygon AS ( SELECT SDO_GEOMETRY(2005,4283,NULL, SDO_ELEM_INFO_ARRAY(1,1,sdo_util.getNumVertices(a.coordinates)), a.coordinates.sdo_ordinates) AS coordinates, a.coordinates.sdo_elem_info AS orig_sdo_elem_info FROM (SELECT SDO_GEOMETRY(2003,4283,NULL, SDO_ELEM_INFO_ARRAY(1,1005,13,1,2,1,19,2,2,23,2,1,27,2,2,31,2,2,35,2,1,41,2,2,45,2,2,49,2,2,53,2,1,55,2,2,59,2,2,63,2,1), SDO_ORDINATE_ARRAY(145.08349848442,-37.5992485827078,145.083269614856,-37.5992214210212,145.083258517023,-37.5992887890424,145.083184742712,-37.5992802807329,145.082972436335,-37.5992557954067,145.082379418586,-37.5991874004812,145.081851999991,-37.5991290000234,145.081501375695,-37.6012733183676,145.081706887708,-37.6012945909566,145.081721911566,-37.6012953358196,145.082012984687,-37.6012670583403,145.082305912872,-37.6012777267067,145.082619307206,-37.6013101638929,145.082974597008,-37.6013469363398,145.083062797668,-37.6013528747372,145.083151312257,-37.6013524909645,145.083253304218,-37.6013441489213,145.08335365429,-37.601327418929,145.083761999991,-37.6012410000235,145.084008138753,-37.6011896695751,145.084165649058,-37.6011565177552,145.084304780176,-37.6011338954257,145.084446243243,-37.6011240183254,145.084628634343,-37.6011302380669,145.084807924443,-37.6011576000265,145.084886163273,-37.6011765618937,145.084962669629,-37.601199567608,145.085210469288,-37.60128157495,145.085434612069,-37.6013782335118,145.08562932837,-37.6015091734516,145.085788911726,-37.6016720201186,145.085902621327,-37.6018576411126,145.08594651,-37.6019535543532,145.085955811468,-37.6019738815231,145.08647763371,-37.6018227298023,145.086509174701,-37.6018135935022,145.08647820577,-37.601798591791,145.086337985869,-37.6014921625712,145.086325425213,-37.6014577482449,145.086399691507,-37.601465432503,145.086456649306,-37.6011169634221,145.086804326188,-37.6011529365099,145.086815091091,-37.6010329304636,145.086842393704,-37.6008870156033,145.086853933382,-37.6008253432727,145.086918394449,-37.6004808372223,145.086686056604,-37.6004375897427,145.086807837059,-37.5997870367294,145.086834561279,-37.5996444478356,145.085072851134,-37.5994338519832,145.08349848442,-37.5992485827078)) AS coordinates FROM dual ) a ) SELECT SDO_GEOM.SDO_CENTROID(coordinates , 0.005) AS centroid, SDO_GEOM.SDO_AREA(coordinates , 0.005,'unit=SQ_KM') AS area, SDO_GEOM.VALIDATE_GEOMETRY(coordinates, 0.005) AS vgeom FROM (SELECT sdo_geometry(2003,3112,NULL, b.orig_sdo_elem_info, b.coordinates.sdo_ordinates) AS coordinates FROM (SELECT sdo_cs.transform(a.coordinates,3112) AS coordinates, a.orig_sdo_elem_info FROM all_polygon a ) b ) c; -- Result -- CENTROID AREA VGEOM ----------------------------------------------------------------------------------------- ------------ ----- SDO_GEOMETRY(2001,3112,SDO_POINT_TYPE(982414.334997869,-4271810.35191714,NULL),NULL,NULL) 0.1005564627 TRUE
I hope this helps someone out there.