I need to find all self-intersecting linestrings in table. SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT finds only self-intersecting polygons, because self-intersecting linestrings are allowed. Any ideas?
It takes a bit of work, but it's doable.
Since Oracle(11.2) failed to provide, the only option we have is to brake the line into segments and use RELATE on the segments' pairs.
Following is my own implementation (used in production code for over 3 years, over millions of geometries). I chose the pipelined approach to cover for overly big or complex geometries.
Prerequisit 1, database type:
CREATE OR REPLACE TYPE ElemGeom as object
(eid integer, egeom mdsys.sdo_geometry, egtype integer, eelemnum integer, evertnum integer, earea number, elength number);
CREATE OR REPLACE TYPE ElemGeomTbl as table of ElemGeom;
Prerequisit 2, splitting function:
create or replace FUNCTION LineSegments (igeom in mdsys.sdo_geometry)
RETURN ElemGeomTbl pipelined
is
seg ElemGeom := ElemGeom(null,null,null,null,null,null,null);
cursor c is select T.id, T.X ,T.Y from table(SDO_UTIL.GETVERTICES(iGEOM)) T order by 1;
type ctbl is table of c%rowtype;
carr ctbl;
seg_geom mdsys.sdo_geometry;
cnt integer:=0;
segid integer; x1 number; y1 number; x2 number; y2 number;
begin
--if igeom.sdo_gtype not in (2002,2006)
--then... if you need to catch non-linears here...
--end if;
open c;
loop
fetch c
bulk collect into carr ;
for i in carr.first .. carr.last -1
loop cnt:=cnt+1;
segid := cnt;
x1 := carr(i).X; y1 := carr(i).Y;
x2 := carr(i+1).X; y2 := carr(i+1).Y;
seg_geom:= (mdsys.sdo_geometry(2002,2100,null
,mdsys.sdo_elem_info_array(1,2,1)
,mdsys.sdo_ordinate_array(x1,y1, x2,y2)));
seg.eid:=segid;
seg.egeom:=seg_geom;
seg.egtype:=seg_geom.sdo_gtype;
pipe row(seg);
end loop;
exit when c%notfound;
end loop;
close c;
end LineSegments;
You can test its output with something like (my GEOMs are SRID 2100):
with t1 as (
select
SDO_GEOMETRY(2002,2100,NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(290161.697,4206385.413, 290161.901,4206388.095, 290162.684,4206385.188, 290163.188,4206388.041,
290163.51,4206385.22, 290164.357,4206388.159, 290166.879,4206387.108, 290161.397,4206387.366,
290166.331,4206386.067, 290165.763,4206388.052))
as G from DUAL
)
select * from t1,table(LineSegments(g));
And the main function:
create or replace FUNCTION validate_Line
(igeom in mdsys.sdo_geometry, itol in number default null)
RETURN varchar2
is
vtol number:= nvl(itol, 1/power(10,6));
verd1 varchar2(256); verd2 varchar2(256); v varchar2(256);
begin
verd1:= sdo_geom.validate_geometry_with_context(igeom,vtol);
for r1 in ( select a.eid seg1, a.egeom geom1, b.eid seg2, b.egeom geom2
from table(LineSegments(igeom)) a, table(LineSegments(igeom)) b
where a.eid < b.eid
order by a.eid, b.eid )
loop
--I hate outputting long words, so:
v:= replace(replace(sdo_geom.relate(r1.geom1,'determine',r1.geom2, vtol)
,'OVERLAPBDYDISJOINT','OVR-BDIS'),'OVERLAPBDYINTERSECT','OVR-BINT');
if instr('EQUAL,TOUCH,DISJOINT',v) = 0 then
verd2:= verd2|| case when verd2 is not null
then ', '||r1.seg1||'-'||r1.seg2||'='||v
else r1.seg1||'-'||r1.seg2||'='||v end;
end if;
end loop;
verd1:= nvl(verd1,'NULL')
|| case when verd1 ='TRUE' and verd2 is null then null
when verd1 ='TRUE' and verd2 is not null then ' *+: '||verd2
end;
return verd1;
end validate_Line;
And its test:
with t1 as (
select
SDO_GEOMETRY(2002,2100,NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(290161.697,4206385.413, 290161.901,4206388.095, 290162.684,4206385.188, 290163.188,4206388.041,
290163.51,4206385.22, 290164.357,4206388.159, 290166.879,4206387.108, 290161.397,4206387.366,
290166.331,4206386.067, 290165.763,4206388.052))
as G from DUAL
)
select t1.*,validate_Line(g) from t1;
This returns:
*TRUE *+: 1-7=OVR-BDIS, 1-8=OVR-BDIS, 2-7=OVR-BDIS, 2-8=OVR-BDIS, 3-7=OVR-BDIS, 3-8=OVR-BDIS, 4-7=OVR-BDIS, 4-8=OVR-BDIS, 5-7=OVR-BDIS, 5-8=OVR-BDIS, 6-9=OVR-BDIS, 7-9=OVR-BDIS*
Of course, you can modify the output to be just a flag or anything else - this is just what suited my own needs.
HTH