3 Replies Latest reply: Nov 14, 2012 12:48 AM by Simon Greener RSS

    how to get points of contact between points and polygons

    DAUM
      Hi~

      I'm
      working based on Oracle 11g and
      looking for the way to get points of contact between points and polygons.

      For instance, I have two data STORE (point)' and 'ROAD (polygon)', and each store should have a point of contact or nearest points of contact to road.

      I know what nearest road is using by SDO_NN, but I don't know hot to get the point of contact.

      Please let me know hot to do this~

      Thanks.
        • 1. Re: how to get points of contact between points and polygons
          Simon Greener
          If you have the point and its nearest road, you can find the point of contact (ie the store's nearest point on the road's linestring) using SDO_LRS function Project_Pt as follows:
          With route As (
            select SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.0,2.0,0.0, 2.0,4.0,3.218, 8.0,4.0,12.872, 12.0,4.0,19.308, 12.0,10.0,28.962, 8.0,10.0,35.398, 5.0,14.0,43.443)) as route,
                   SDO_GEOMETRY(2001,NULL,sdo_point_type(9,3,NULL),null,null) as store
            from dual
            ) 
            SELECT SDO_LRS.PROJECT_PT(route, store) as store_on_route
            FROM route ;
          -- Result
          --
          STORE_ON_ROUTE
          --------------------------------------------------------------------------------------------------------
          MDSYS.SDO_GEOMETRY(3301,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),MDSYS.SDO_ORDINATE_ARRAY(9,4,14.481))
          If you don't have a Spatial license, try using my LRS for Locator users at: http://www.spatialdbadvisor.com/source_code/214/linear-package-sdo_lrs-replacement

          If this is correct, please award points. (I work for Love ie Points not money!)

          regards
          Simon
          • 2. Re: how to get points of contact between points and polygons
            DAUM
            I got the solution with your help + SDO_LRS.CONVERT_TO_LRS_GEOM()!!!

            Thanks!!
            • 3. Re: how to get points of contact between points and polygons
              Simon Greener
              An excellent outcome.

              I could have told you about CONVERT_TO_LRS_GEOM but one should not spoon feed someone too much!

              Thanks for the points.

              regards
              Simon