L,
Why is the table called LRS_CURRENT_SEGMENTS if it doesn't contain LRS segments?
How are the 350,000 crashes processed each week? Is it a batch process or an interactive process?
Since you are learning and building the solution on the fly I suggest that, because you have a license for SDO_LRS,
that you should just use it to get what you want using "convert on the fly" and then look at how to improve its efficiency.
It may well be that the process is functional, effective and not so slow as to cause any issues. If so, stay with it.
If not, look to making changes.
Also, talking about efficiency, your anonymous pl/SQL is quite inefficient because you convert the one road centreline twice
and use cursors that only each ever produce one row. Here are my suggested changes.
set serveroutput on size unlimited
Declare
Bearing Number;
Tilt Number;
v1 mdsys.vertex_type;
v2 mdsys.vertex_type;
road mdsys.sdo_geometry;
Begin
--_Get and conver the centreline
SELECT Sdo_Lrs.Convert_To_Lrs_Geom (R.Geometry, 26.61, 29.61)
INTO road
FROM Digit.Lrs_Current_Segments R
WHERE R.Lrs_Current_Segment_Id = 10283273;
v1 := sdo_util.getVertices(Sdo_Lrs.Locate_Pt(road, 26.72))(1);
v2 := sdo_util.getVertices(Sdo_Lrs.Locate_Pt(road, 26.74))(1);
Sdo_Util.Bearing_Tilt_For_Points (
sdo_geometry(2001,8307/*X.a1.sdo_srid*/,sdo_point_type(v1.x, v1.y, null),null,null), -- start point
sdo_geometry(2001,8307/*Y.b1.sdo_srid*/,sdo_point_type(v2.x, v2.y, null),null,null), -- end point
0.05, -- Tolerance
Bearing,
Tilt
);
Dbms_Output.Put_Line('Bearing = ' || Bearing);
Dbms_Output.Put_Line('Tilt = ' || Tilt);
End;
/
If you don't want to use an anonymous block, convert the code into a function ...
create or replace getBearing(p_road in mdsys.sdo_geometry,
p_measure1 in number,
p_measure2 in number,
p_offset1 in number,
p_offset2 in number,
p_tolerance in number default 0.05)
return number deterministic
Bearing Number;
Tilt Number;
v1 mdsys.vertex_type;
v2 mdsys.vertex_type;
road mdsys.sdo_geometry;
Begin
--_Get and conver the centreline
road := Sdo_Lrs.Convert_To_Lrs_Geom (R.Geometry, p_measure1, p_measure2)
v1 := sdo_util.getVertices(Sdo_Lrs.Locate_Pt(road, p_offset1))(1);
v2 := sdo_util.getVertices(Sdo_Lrs.Locate_Pt(road, p_offset2))(1);
Sdo_Util.Bearing_Tilt_For_Points (
sdo_geometry(2001,p_road.sdo_srid,sdo_point_type(v1.x, v1.y, null),null,null), -- start point
sdo_geometry(2001,p_road.sdo_srid,sdo_point_type(v2.x, v2.y, null),null,null), -- end point
p_tolerance, -- Tolerance
Bearing,
Tilt
);
return bearing;
End getBearing;
/
-- Example use (could be as part of insert, update...)
--
SELECT getBearing (R.geometry,26.61, 29.61, 26.72.26.74,0.05) as bearing
FROM Digit.Lrs_Current_Segments R
WHERE R.Lrs_Current_Segment_Id = 10283273; -- or multiple via IN (.....)
regards
Simon
Edited by: Simon Greener on Feb 16, 2013 9:33 AM