Geometry to wkt curve polygon
From WickyWiki
In Oracle Spatial the conversion of arc's to WKT seems not supported as it is not part of the simple feature specification. On this page you will find:
- a spatial function to stroke the arc's
- a custom function to output polygons with arc's
SQL arc stroking with Oracle Spatial:
select SDO_UTIL.TO_WKTGEOMETRY( SDO_GEOM.SDO_ARC_DENSIFY(geom, 0.0005, 'arc_tolerance=0.01'))
from (select MDSYS.SDO_GEOMETRY(2003, 90112, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,3,1,2,1,3,2,2,7,2,1)
, MDSYS.SDO_ORDINATE_ARRAY(161312.26, 383399.949, 161312.42, 383399.78
, 161315.461, 383401.401, 161317.344, 383404.288, 161317.296, 383404.342
, 161312.26, 383399.949)) as geom from dual)
;
Result:
POLYGON ((161312.26 383399.949, 161312.42 383399.78, 161313.087535512 383399.978123147 , 161313.731792268 383400.242300498, 161314.346252635 383400.569859498 , 161314.924700414 383400.957486391, 161315.461283728 383401.401259743 , 161315.950574223 383401.896690114, 161316.387621984 383402.438765474 , 161316.76800561 383403.022001908, 161317.087876943 383403.640499096 , 161317.344 383404.288, 161317.296 383404.342, 161312.26 383399.949))
SQL curve WKT with custom function:
select GEOMETRY_TO_CURVEPOLYGON_WKT(geom)
from (select MDSYS.SDO_GEOMETRY(2003, 90112, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,3,1,2,1,3,2,2,7,2,1)
, MDSYS.SDO_ORDINATE_ARRAY(161312.26, 383399.949, 161312.42, 383399.78
, 161315.461, 383401.401, 161317.344, 383404.288, 161317.296, 383404.342
, 161312.26, 383399.949)) as geom from dual)
;
Result:
CURVEPOLYGON(COMPOUNDCURVE((161312.26 383399.949,161312.42 383399.78) ,CIRCULARSTRING(161312.42 383399.78,161315.461 383401.401,161317.344 383404.288) ,(161317.344 383404.288,161317.296 383404.342,161312.26 383399.949)))
PL/SQL curve polygon function:
create or replace FUNCTION GEOMETRY_TO_CURVEPOLYGON_WKT( geom IN mdsys.Sdo_geometry ) RETURN CLOB is
-- FUNCTION geometry --> curvepolygon wkt
-- (c) 2012-01 Synaxion Urbidata
-- exception voor foutieve of niet-ondersteunde geometrie, ondersteund zijn:
-- - (curve)polygon, optioneel met 1 of meer gaten
elem_info_i PLS_INTEGER;
start_index PLS_INTEGER;
until_index PLS_INTEGER;
geometry_type VARCHAR2(12) := 'POLYGON';
element_type PLS_INTEGER;
element_interpretation PLS_INTEGER;
subelements_num PLS_INTEGER;
subelements_type PLS_INTEGER;
ring CLOB;
exterior CLOB;
interiors CLOB;
function num_to_char(n number) return varchar2 is
begin
if trunc(n)=n then
return trim(to_char(n,'9999999999.0'));
else
return to_char(n);
end if;
end;
function read_elem_info(i PLS_INTEGER) return PLS_INTEGER is
begin
IF i+2 > geom.sdo_elem_info.COUNT THEN
raise_application_error(-20002, 'The elemtinfo count ('||to_char(geom.sdo_elem_info.COUNT)||') is not a multitude of 3');
END IF;
start_index:=geom.sdo_elem_info( i );
element_type := geom.sdo_elem_info( i+1 );
element_interpretation:=geom.sdo_elem_info( i+2 );
-- next start index: 'until_index'
IF i+3 <= geom.sdo_elem_info.COUNT THEN
until_index:=geom.sdo_elem_info(i+3);
ELSE
until_index:=geom.Sdo_ordinates.COUNT+1;
END IF;
RETURN i+3;
end;
function read_ring(start_index PLS_INTEGER, until_index PLS_INTEGER) return clob is
begin
declare
j PLS_INTEGER;
r clob;
begin
r:=null;
-- check index, create ring
IF start_index<=geom.Sdo_ordinates.COUNT
and start_index > 0
and until_index+1-2<=geom.Sdo_ordinates.COUNT
and until_index-2>0
and start_index<until_index-1
THEN
j:=start_index;
WHILE j < until_index LOOP
IF not r is NULL THEN
r := r||',';
END IF;
r := r || num_to_char( geom.sdo_ordinates(j+0) ) || ' ' || num_to_char( geom.sdo_ordinates(j+1) );
j:=j+2;
END LOOP;
ELSE
raise_application_error(-20003, 'GEOMETRY_TO_CURVEPOLYGON_WKT: the offset does not point to any coordinates');
END IF;
return r;
end;
end;
BEGIN
IF geom is NULL THEN
RETURN NULL;
ELSIF NOT geom.sdo_elem_info IS NULL THEN
IF geom.sdo_gtype = 2003 THEN
exterior:=NULL;
interiors:=NULL;
elem_info_i:=1;
WHILE elem_info_i <= geom.sdo_elem_info.COUNT LOOP
elem_info_i:=read_elem_info(elem_info_i);
-- curve polygons
IF element_type in (1005,2005) THEN
geometry_type:='CURVEPOLYGON';
subelements_num:=element_interpretation;
subelements_type:=element_type;
ring:=NULL;
WHILE subelements_num>0 LOOP
subelements_num:=subelements_num-1;
elem_info_i:=read_elem_info(elem_info_i);
IF NOT ring is NULL THEN
ring:=ring||',';
END IF;
-- if not last segment then add first point of next segment
IF subelements_num>0 AND until_index<geom.sdo_ordinates.COUNT+1 THEN
until_index:=until_index+2;
END IF;
IF element_interpretation=1 THEN
ring:=ring||'('||read_ring(start_index,until_index)||')';
ELSIF element_interpretation=2 THEN
ring:=ring||'CIRCULARSTRING('||read_ring(start_index,until_index)||')';
ELSE
raise_application_error(-20003, 'GEOMETRY_TO_CURVEPOLYGON_WKT: the interpretation of subelement ('||to_char(element_interpretation)||') should be 1 (straight) or 2 (curve)');
END IF;
END LOOP;
IF subelements_type=1005 AND exterior is NULL THEN
exterior:='COMPOUNDCURVE('||ring||')';
ELSIF subelements_type=2005 THEN
interiors:=interiors || ',' || 'COMPOUNDCURVE('||ring||')';
ELSE
raise_application_error(-20004, 'GEOMETRY_TO_CURVEPOLYGON_WKT: unsupported coumpound geometry');
END IF;
ELSIF element_type=1003 AND exterior is NULL THEN
exterior:='(' || read_ring(start_index,until_index) || ')';
ELSIF element_type=2003 THEN
interiors:=interiors || ',' || '(' || read_ring(start_index,until_index) || ')';
ELSE
raise_application_error(-20005, 'GEOMETRY_TO_CURVEPOLYGON_WKT: unsupported geometry');
END IF;
END LOOP;
RETURN geometry_type || '(' || exterior || interiors || ')';
ELSE
raise_application_error(-20006, 'GEOMETRY_TO_CURVEPOLYGON_WKT: unsupported geometry');
END IF; -- geom.sdo_gtype = 2003
ELSE
raise_application_error(-20006, 'GEOMETRY_TO_CURVEPOLYGON_WKT: unsupported geometry');
END IF; -- NOT geom.sdo_elem_info IS NULL
EXCEPTION WHEN OTHERS THEN
--RETURN NULL;
RETURN DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || SQLERRM;
END;
/
