Getting Geometry of Intersection of Road SQL and Inserting Into Table -

i need find intersection of various roads each road divided multiple segments there many records 1 road. have query can use find geometries each shows null. need have 1 geometry can insert 1 record.

select sdo_geom.sdo_intersection(coll_a.geometry, coll_b.geometry, 0.05) ottawacollectors coll_a, ottawacollectors coll_b coll_a.road_name = 'kent' , coll_b.road_name = 'metcalfe'; 

the efficient approach sort of problem use sdo_join() filter. designed efficiently match spatially many objects many others using spatial indexes.

i assuming table looks this:

create table ottawacollectors (   road_id number,   segment_id number,   road_name varchar2(30),   geometry sdo_geometry,   primary key (road_id, segment_id) ); 

it contains road segments. each road segment identified road identifier , segment identifier.

the following creates new table intersections contain 1 row each intersection, i.e. whenever 2 road segments interact. intersection computed geometric point. each row contains identifier of each segment (road identifier , segment identifier) name of each road.

create table intersections select a.road_id road_id_1, a.segment_id segment_id_1, a.road_name road_name_1,        b.road_id road_id_2, b.segment_id segment_id_2, b.road_name road_name_2,               sdo_geom.sdo_intersection (          a.geometry, b.geometry, 0.05        ) intersection_point  ottawacollectors a,       ottawacollectors b,       table (         sdo_join(           'ottawacollectors','geometry',           'ottawacollectors','geometry',           'mask=anyinteract'         )       ) j   a.rowid = j.rowid1 ,   b.rowid = j.rowid2 ,   j.rowid1 > j.rowid2; 

some explanations:

• sdo_join() "table" function. takes names of 2 input tables (table name , name of geometry column) , match criteria - here " anyinteract", meaning kind of interaction: segments may cross or touch each other.
• it returns varray of elements, each element contains pair of rowids (physical identifiers of rows in table) called rowid1 , rowid2, point couples of interacting road segments.
• the table() constructor casts array make regular table, making easy embed in relational query. "virtual" table called j in query.
• the query reads ottawacollectors table twice (like in example). joins them table() result: j.rowid1=a.rowid , j.rowid2=b.rowid
• the j.rowid1>j.rowid2 filter there eliminate unwanted results. road segments , b intersect. sdo_join return 4 combinations: (a,b), (b,a) , (a,a) , (b,b) since segment intersects itself! purpose of comparing rowids retain 1 of (a,b) or (b,a).
• the select list includes full identifiers of 2 segments, name , intersection point (computed using sdo_geom.sdo_intersection() in example).
• finally result written table

note query not return results instantly: may take minutes complete, depending on number of road segments need process, , of course on hardware run on. if run on oracle 12c (12.1.0.1 or 12.1.0.2) , own proper license oracle spatial, make sure have vector performance accelerator option turned on.