Getting Geometry of Intersection of Road SQL and Inserting Into Table

Question

I need to find the intersection of various roads but each road is divided into multiple segments so there are many records for one road. I have this query which i can use to find the geometries but each shows up as null. I need to have one geometry so that i can insert that into one record.

SELECT SDO_GEOM.SDO_INTERSECTION(coll_a.GEOMETRY, coll_b.GEOMETRY, 0.05)
FROM OTTAWACOLLECTORS coll_a, OTTAWACOLLECTORS coll_b
WHERE coll_a.ROAD_NAME = 'KENT' AND coll_b.ROAD_NAME = 'METCALFE';

Answer 1

The most efficient approach for that sort of problem is to use the SDO_JOIN() filter. It is designed to efficiently match spatially many objects with many others using the spatial indexes.

I am assuming that your table looks like 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 is identified by a road identifier and a segment identifier.

The following creates the new table INTERSECTIONS that contain one row for each intersection, i.e. whenever two road segments interact. The intersection is computed as a geometric point. Each row contains the identifier of each segment (road identifier and segment identifier) as well as the name of each road.

create table intersections as
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
from  ottawacollectors a,
      ottawacollectors b,
      table (
        sdo_join(
          'OTTAWACOLLECTORS','GEOMETRY',
          'OTTAWACOLLECTORS','GEOMETRY',
          'MASK=ANYINTERACT'
        )
      ) j  
where a.rowid = j.rowid1
and   b.rowid = j.rowid2
and   j.rowid1 > j.rowid2;

Some explanations:

• SDO_JOIN() is a "table" function. It takes the names of two input tables (table name and the name of the geometry column) and a match criteria - here " ANYINTERACT", meaning any kind of interaction: segments may cross or just touch each other.
• It returns a VARRAY of elements, where each element contains a pair of rowids (physical identifiers of rows in a table) called ROWID1 and ROWID2, that point to couples of interacting road segments.
• The TABLE() constructor casts that array so as to make it look as a regular table, making it easy to embed it in a relational query. That "virtual" table is called J in the query.
• The query also reads the OTTAWACOLLECTORS table twice (like in your example). It joins them with the TABLE() result: J.ROWID1=A.ROWID AND J.ROWID2=B.ROWID
• The J.ROWID1>J.ROWID2 filter is there to eliminate unwanted results. Say road segments A and B intersect. SDO_JOIN will return 4 combinations: (A,B), but also (B,A) and also (A,A) and (B,B) since a segment obviously intersects itself! The purpose of comparing the rowids is to only retain one of (A,B) or (B,A).
• The select list includes the full identifiers of the two segments, their name and also their intersection point (computed using SDO_GEOM.SDO_INTERSECTION() like in your example).
• Finally the result is written into a table

Note that this query will not return results instantly: it may take minutes to complete, depending on the number of road segments you need to process, and of course on the hardware you run it on. If you run it on Oracle 12c (12.1.0.1 or 12.1.0.2) and you own the proper license for Oracle Spatial, then make sure you have the Vector Performance Accelerator option turned on.