1 2 Previous Next 16 Replies Latest reply: Feb 5, 2013 1:08 PM by user12035460 Go to original post RSS
      • 15. Re: Oracle Spatial function to find nearest line string based on lat/long
        user12035460
        Hi Stefan,

        Thank you. The information is very useful. In order for me to convert my tables into LRS type, I would have to wait on my DBA.
        I will let you know once they are converted and how the query goes.

        Thank you again. I appriciate all ur help.

        Thanks,
        L.
        • 16. Re: Oracle Spatial function to find nearest line string based on lat/long
          user12035460
          Hi Stefan,

          I was able to acheive as you said.. This is what i used ..I used a SDO_NN statement to find the closest segment and used its segment_ID from that query for the following query..

          SELECT
          R.LRS_CURRENT_SEGMENT_ID, r.NLF_ID, r.CTL_BEGIN_NBR, r.CTL_END_NBR, r.GEOMETRY, SDO_NN_DISTANCE(1)
          FROM
          DIGIT.LRS_CURRENT_SEGMENTS r
          WHERE SDO_NN(r.GEOMETRY,
          SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-83.11904,39.976629, NULL), NULL, NULL),
          'sdo_num_res=10 distance=0.02 unit=mile',1) = 'TRUE'
          and r.TRANS_ROUTE_CD = 'RA'
          ;

          SELECT SDO_LRS.GET_MEASURE(SDO_LRS.PROJECT_PT
          (
          (select SDO_LRS.CONVERT_TO_LRS_GEOM (r.geometry, 0.33, 0.47) from LRS_CURRENT_SEGMENTS r where r.LRS_CURRENT_SEGMENT_ID = 10281216),
          (Select SDO_CS.TRANSFORM(SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-83.11904,39.976629, NULL), NULL, NULL), 41104) From DUAL)
          )
          ) as PROJECTED_CTL
          from LRS_CURRENT_SEGMENTS
          where LRS_CURRENT_SEGMENT_ID = 10281216
          ;

          Worked with the expected results. Thank you for your advice.
          I had to do it in 2 separate queries, since the first query can result in multiple answers. Logic needs to be built to select the closest line string.

          Thanks,
          L.

          Edited by: user12035460 on Feb 5, 2013 2:07 PM

          Edited by: user12035460 on Feb 5, 2013 2:08 PM
          1 2 Previous Next