3 Replies Latest reply: Apr 4, 2014 2:06 PM by navaneet RSS

    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