This discussion is archived
7 Replies Latest reply: Feb 20, 2013 1:46 PM by Simon Greener RSS

Regd: SDO_UTIL.Bearing_Tilt_For_Points

user12035460 Newbie
Currently Being Moderated
Hi,

I have been trying to get a bearing using the SDO_UTIL.Bearing_Tilt_For_Points function. But when i execute this query it gives me an error.

Declare
Bearing Number;
Tilt    Number;

_Get the first point required by bearing function
**Cursor C1 Is*
Select Sdo_Lrs.Locate_Pt (
*(Select Sdo_Lrs.Convert_To_Lrs_Geom (R.Geometry, 26.61, 29.61) From Digit.Lrs_Current_Segments R Where R.Lrs_Current_Segment_Id = 10283273), 26.72)*
a1 From dual;

_Get the second point required by bearing function
Cursor C2 Is
Select Sdo_Lrs.Locate_Pt (
*(Select Sdo_Lrs.Convert_To_Lrs_Geom (R.Geometry, 26.61, 29.61) From Digit.Lrs_Current_Segments R Where R.Lrs_Current_Segment_Id = 10283273), 26.74)*
b1 From dual;

Begin
For X In C1 Loop
for y in c2 loop

Sdo_Util.Bearing_Tilt_For_Points
*(*
X.a1, -- start point
y.b1, -- end point
*0.05,* -- Tolerance
Bearing,
Tilt

*);*
End Loop;
end loop;

Dbms_Output.Put_Line('Bearing = ' || Bearing);
Dbms_Output.Put_Line('Tilt = ' || Tilt);
End;

Error report:
ORA-13364: layer dimensionality does not match geometry dimensions
ORA-06512: at "MDSYS.SDO_3GL";, line 2511
ORA-06512: at "MDSYS.SDO_UTIL";, line 1218
ORA-06512: at line 15
13364. 00000 - "layer dimensionality does not match geometry dimensions"
*Cause:    The spatial layer has a geometry with a different dimensions than the
dimensions specified for the layer.
*Action:   Make sure that all geometries in a layer have the same dimensions
and that they match the dimensions in the SDO_DIM_ARRAY object
for the layer in the USER_SDO_GEOM_METADATA view.

Not sure what the error is?
Any help is appreciated.

Thanks,
L.
  • 1. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    Simon Greener Journeyer
    Currently Being Moderated
    L.,

    The problem is that the LOCATE_PT returns a 3301 point and not a 2D point.

    You have to convert the point generated to a 2D point for feeding in to the bearing_tilt_for_points.

    Try something like this.
    Declare 
    Bearing Number; 
       Tilt Number; 
         v1 mdsys.vertex_type;
         v2 mdsys.vertex_type;
    
    --_Get the first point required by bearing function 
    Cursor C1 Is 
    SELECT SDO_LRS.LOCATE_PT(a.route_geometry, 26.72) a1
      FROM lrs_routes a 
      WHERE a.route_name = 'Route1';
    
    --_Get the second point required by bearing function 
    Cursor C2 Is 
    SELECT SDO_LRS.LOCATE_PT(a.route_geometry, 26.74) b1
      FROM lrs_routes a 
      WHERE a.route_name = 'Route1';
    
    Begin 
    For X In C1 Loop 
    for y in c2 loop 
    
    -- Extract single point from sdo_ordinate_array for each located points
    v1 := sdo_util.getVertices(X.a1)(1);
    v2 := sdo_util.getVertices(y.b1)(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 
    
    ); 
    End Loop; 
    end loop; 
    Dbms_Output.Put_Line('Bearing = ' || Bearing); 
    Dbms_Output.Put_Line('Tilt = ' || Tilt); 
    End;
    /
    -- Results
    --
    Bearing = 0
    Tilt = 
    Note that I am using a different data source which has NULL srid so I hard code 8307 as bearing_tilt works only for geodetic data. Replace the data source with your own and remove the 8307 and the comments around the sdo_srid references to revert to your own data.

    If correct, please award points.

    regards
    Simon
  • 2. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    user12035460 Newbie
    Currently Being Moderated
    Hi Simon,

    Thank you for the reply. I will be trying to use the get_vertices function to get the 2d points, then apply to bearing util and let you know how it goes. I will definetly award you points once I am done. However, I need some advice.
    The project I am working on, I have explained quite a bit in this thread below.

    Oracle Spatial function to find nearest line string based on lat/long

    Based on my project requirement:

    The routes table (LRS_CURRENT_SEGMENTS) that holds the linear LRS segments with geomtery, does not have LRS measure values in the geometry in otherwords geometry is not of LRS type.
    Every time, I had to convert the geometry to lrs type before I can apply any oracle spatial LRS functions. Would it be efficient to store the routes table with LRS measures or will it be efficient if I just convert them on the fly?
    We typically do this crash logging once a week and have about 350,000 crashes that will need to be logged in a week.

    Thanks,
    L.
  • 3. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    Simon Greener Journeyer
    Currently Being Moderated
    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
  • 4. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    user12035460 Newbie
    Currently Being Moderated
    Hi Simon,

    The lrs_Current_segments have lrs segments, its the line geomtery in that table thats does not have lrs measures.
    To process the 350,000 - we are planning to run a batch process (schedule it).

    The pl/sql so far that i have posted is just for trying out the functions, it is not in production yet. But sure will implement it as a function so that it can be called from other packages also. And moreover, I am the technical BA and programmer for this project and also responsible for the RI data we maintain. The pl/sql will be written by our DBA.
    I still havent had a chance to try the function as you said. But will keep you posted.

    Thank You,
    L.
  • 5. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    Simon Greener Journeyer
    Currently Being Moderated
    L.,
    The lrs_Current_segments have lrs segments, its the line geomtery in that table thats does not have lrs measures.
    I don't understand this.

    If the table's only geometry column is not measured, then how does lrs_current_segments hold LRS segments?

    Now, I can only assume that the table's structure (which you don't give us) must contain from_measure/to_measure type columns which
    are supplied to Sdo_Lrs.Convert_To_Lrs_Geom() as and when required.

    If this is the case, then why not convert the linestring geometry without measures to a measured geometry and make the semantic
    definition of the table clearer? You can use insert/update triggers on the table to construct lrs linestrings from changed Lrs attributes to keep
    all in sync. Or is the linestring created/updated by a non-LRS aware GIS application?

    Regardless, the function I created (or its like) should suffice for all your needs: batch through SQL DML and triggers.

    regards
    Simon
  • 6. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    user12035460 Newbie
    Currently Being Moderated
    Hi Simon,

    Thank you for your advice and help on the bearing_tilt_for_points. I was able to successfully implement the function.

    Btw, I have awarded points "correct" for the functions and "helpful" for your advice on the LRS_measures in the geometry.

    Thanks,
    L.
  • 7. Re: Regd: SDO_UTIL.Bearing_Tilt_For_Points
    Simon Greener Journeyer
    Currently Being Moderated
    L.,

    I am glad that my contributions helped.

    On behalf of myself and the others who take time (for it takes time) to answer questions, I thank you for awarding the points.

    In doing so it makes us want to come back and help some more.

    Good luck with your project and implementation.

    regards
    Simon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points