3 Replies Latest reply on Apr 4, 2014 7:06 PM by navaneet

    No perpendicular within end points of the line

    navaneet

      All,

      There is no perpendicular within end points of the line and we need to calculate the perpendicular distance from a point to a line.

               o
               |
               | Perpendicular distance
               |
               |

      ...............................-----------------------

       

      Best

      Navaneet

        • 1. Re: No perpendicular within end points of the line
          _jum

          This should be the SDO_DISTANCE, f.e.:

           

          SELECT sdo_geom.sdo_distance(

            SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(  0,  0,  0), NULL,NULL),

            SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(  0,-1, 1,0)),

            0.001) geom_dist,

            SQRT(2)/2 calc_dist

          FROM dual;

           

           

          GEOM_DIST   CALC_DIST

          ----------------------------------------------

          ,707106781 ,707106781

          1 row selected.


          Or do you mean the distance to the (possible) extension of the line ?

          • 2. Re: No perpendicular within end points of the line
            _jum

            For this problem there is an analytical solution, here with POINT(1,1) and LINE(2,0, 4,0) as in Your example:

             

            SELECT t.pg.SDO_POINT.X px, t.pg.SDO_POINT.Y py, p1.X ax, p1.Y ay, p2.X ex, p2.Y ey,

                   ((p2.X-p1.X)*(t.pg.SDO_POINT.Y-p1.Y)-(p2.Y-p1.Y)*(t.pg.SDO_POINT.X-p1.X)) / SQRT((p2.X-p1.X)*(p2.X-p1.X)+(p2.Y-p1.Y)*(p2.Y-p1.Y)) dist

              FROM

            (SELECT SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE( 1,1,0), NULL,NULL) pg, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(  2,0, 4,0)) ln FROM dual) t,

               TABLE(sdo_util.getvertices(ln)) p1, TABLE(sdo_util.getvertices(ln)) p2

               WHERE p1.id=1

                 AND p2.id=2;


            It uses the simple mathematical calculation of perpenddicular distance (lot).

            • 3. Re: No perpendicular within end points of the line
              navaneet

              Hello _Jum,

               

              Thanks for your help. It worked. I am surprised that there is no generic spatial function exists. For polylines I intended to modify your sql.

              .

              Best

              Navaneet

               

              Message was edited by: navaneet