I have encapsulated my needs into some functions that are included below. The ones returning BOOLEAN are there for use in PL/SQL programming as the BOOLEAN data type is not supported in SQL for Oracle.
To test, we will use the Arc/Circle/Compound sdo_geometry objects in 2.5 Geometry Examples of the Oracle Spatial documentation (that I have written to a table called OracleTestGeometries and which ships with my free PL/SQL packages):
Thanks this helped me quite a lot today. Although I implemented the function differently I used your logic so I decided to share it but I don’t know the textile for source code, but in short I loop over the (sdo_elem_info varray)
create or replace function has_compound_curves ( geom_in in sdo_geometry)
return integer as
etype number;
interpret number;
begin
for i in geom_in.sdo_elem_info.first .. geom_in.sdo_elem_info.last loop
case
when mod(i,3) = 1 then continue;
when mod(i,3) = 2 then etype := geom_in.sdo_elem_info(i);
when mod(i,3) = 0
then
interpret := geom_in.sdo_elem_info(i);
if((etype = 2 and interpret = 2) or (etype in (1003,2003) and interpret in (2,4))) then
return 1;
end if;
end case;
end loop;
return 0;
end has_compound_curves;
This runs about 20-30 times faster and answers the same question.
I have no idea if your query up there is what you use in practice or if you simply stated it here for clarity, either way it helped me and since you share I’ll do it as well :)
— Sveinn R. Joelsson · 16 December 2009, 23:55 · #
Sveinn,
I updated the article to replace the SQL based original function with non-SQL based ones similar to your own.
Comment [2]
Thanks this helped me quite a lot today. Although I implemented the function differently I used your logic so I decided to share it but I don’t know the textile for source code, but in short I loop over the (sdo_elem_info varray)
create or replace function has_compound_curves ( geom_in in sdo_geometry)
return integer as
etype number;
interpret number;
begin
for i in geom_in.sdo_elem_info.first .. geom_in.sdo_elem_info.last loop
case
when mod(i,3) = 1 then continue;
when mod(i,3) = 2 then etype := geom_in.sdo_elem_info(i);
when mod(i,3) = 0
then
interpret := geom_in.sdo_elem_info(i);
if((etype = 2 and interpret = 2) or (etype in (1003,2003) and interpret in (2,4))) then
return 1;
end if;
end case;
end loop;
return 0;
end has_compound_curves;
This runs about 20-30 times faster and answers the same question.
I have no idea if your query up there is what you use in practice or if you simply stated it here for clarity, either way it helped me and since you share I’ll do it as well :)
— Sveinn R. Joelsson · 16 December 2009, 23:55 · #
Sveinn,
I updated the article to replace the SQL based original function with non-SQL based ones similar to your own.
regards
Simon
— Simon Greener · 29 December 2011, 12:28 · #