7 Replies Latest reply: Feb 20, 2013 3:46 PM by Simon Greener RSS

    Regd: SDO_UTIL.Bearing_Tilt_For_Points

    user12035460
      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
          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
            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
              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
                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
                  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
                    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
                      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