SQL Inserting new geometry into a new table that is calculated using spatial functions for intersections and difference

Question

I'm trying to use two cursors to find 2 different intersections and then use these two intersections to find the difference between them as a geometry object to insert into a new table. I'm not sure I'm using these cursors correctly but I want to do this in the most efficient way possible.

CREATE OR REPLACE PROCEDURE outputint
is
CURSOR findint1 IS
select sdo_geom.sdo_intersection 
    (a.geometry, b.geom, 0.05)
from  OTTAWACOLLECTOR_NAD a,
      OTTAWAPROVRDS_LRS b      
where a.road_name = 'KENT' 
AND b.rdnumber = '417';

CURSOR findint2 IS
select sdo_geom.sdo_intersection 
    (a.geometry, b.geom, 0.05)
from  OTTAWACOLLECTOR_NAD a,
      OTTAWAPROVRDS_LRS b      
where a.road_name = 'METCALFE' 
AND b.rdnumber = '417';

begin

UPDATE closedrds 
set GEOMETRY = SDO_GEOM.SDO_DIFFERENCE(
    findint1, findint2)
where rd_id = 1;

end outputint;

Answer 1

That syntax is definitely incorrect. You cannot use cursors that way. The functions in SDO_GEOM are standard functions that take scalar input and produce scalar results. In other words, SDO_GEOM.SDO_DIFFERENCE() takes two geometry objects as input and returns a single geometry object (the geometric difference of the two input objects). It does not take a cursor as input.

Then again, what exactly is it that you are trying to do ? I am assuming that both tables (OTTAWACOLLECTOR_NAD and OTTAWAPROVRDS_LRS) are lines (and the second contains LRS geometries). What do you expect from intersecting them ? Lines ? Points ? And what do you expect from computing the difference between those results ?

As an aside, it would be a good idea to add some consistency to your data model. Things like: use the same name for your geometry columns (GEOMETRY vs GEOM). More important: use the same name for attributes in the same domain, i.e. that represent the same information: RD_ID vs RDNUMBER - assuming this is the case.