1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

list closest point not on the same coordinate point.

Discussion in 'General' started by fbastian, Jan 19, 2011.

  1. fbastian

    fbastian Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi there,
    I am querying for the closest streetlight to a pole which is not mounted on the pole.

    This light has to have a coordinate location slightly offset from the pole coordinate location.

    Below is the query that I wrote. I want to know how I can specify that the distance has to be greater than zero.

    select sdo_nn_distance(1) dist, s.STREETLIGHT_IDENTIFIER, p.POLE_IDENTIFIER

    from pole p, streetlight s

    where s.construction_status = 'as constructed'

    and p.construction_status = 'as constructed'

    --and p.LOCATION_PT <> s.LOCATION_PT

    and sdo_nn(p.LOCATION_PT,s.LOCATION_PT,'sdo_num_res=1',1) = 'TRUE'

    order by dist;

    I hope this is clear. Can anyone help?

    thank you!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Presuming your query returns results this is one way:

    Code (SQL):
     
    SELECT x.dist, x.streetlight_identifier, x.pole_identifier
    FROM
    (SELECT sdo_nn_distance(1) dist, s.STREETLIGHT_IDENTIFIER, p.POLE_IDENTIFIER
    FROM pole p, streetlight s
    WHERE s.construction_status = 'as constructed'
    AND p.construction_status = 'as constructed'
    --and p.LOCATION_PT <> s.LOCATION_PT
    AND sdo_nn(p.LOCATION_PT,s.LOCATION_PT,'sdo_num_res=1' ,1) = 'TRUE'
    ) x
    WHERE x,dist > 0
    ORDER BY x.dist;

     
    Another way might be:

    Code (SQL):
     
    SELECT sdo_nn_distance(1) dist, s.STREETLIGHT_IDENTIFIER, p.POLE_IDENTIFIER
    FROM pole p, streetlight s
    WHERE s.construction_status = 'as constructed'
    AND p.construction_status = 'as constructed'
    --and p.LOCATION_PT <> s.LOCATION_PT
    AND sdo_nn(p.LOCATION_PT,s.LOCATION_PT,'sdo_num_res=1' ,1) = 'TRUE'
    WHERE sdo_nn_distance(1) > 0
    ORDER BY 1;

     
     
    fbastian likes this.
  3. fbastian

    fbastian Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi David,
    thanks very much for replying.
    I tried both the queries and the first one works perfectly. The second one doesnt work. I guess its because the second part of the query is not a sub query of the first part.

    thank you for your help!
    cheers. :)