This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 5, 2013 11:08 AM by user12035460 Go to original post RSS
  • 15. Re: Oracle Spatial function to find nearest line string based on lat/long
    user12035460 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points