This discussion is archived
8 Replies Latest reply: Mar 11, 2013 2:32 PM by bkazar RSS

Closest linestring to a point

FlyingGuy Explorer
Currently Being Moderated
Oracle EE 11g .2 release

Ok so I have a table of recods that have and SDO_GEOMETRY column and they are linestrings that represent roads. They are srid 8307.

So what I am trying to solve is to determine which records geometry is closest to a point type.

Since linestrings are maid up of several points how does one go about that. Logically each linestring could have points that were miles apart and my single point could be closer to a given point then many of the others in the linestring.

Do I have to unravel the points in the linestring or is there a function that will allow me to create an sdo_geometry that is of type point2D and then get the record that has the closest point on the linestring to my point2D?

Thanks in advance.
  • 1. Re: Closest linestring to a point
    Simon Greener Journeyer
    Currently Being Moderated
    FlyingGuy,

    You should look at SDO_NN as it will do what you want.
    WITH myPoint AS (
      SELECT sdo_geometry(2001,8307,sdo_point_type(-100.0,32.5,null),null,null) as point
        FROM dual
    )
    SELECT /*+ORDERED*/
           l.*,
           sdo_nn_distance(1) as closest_distance
      FROM myPoint p,
           myLines l
     WHERE SDO_NN(l.geom,p.point,'sdo_num_res=1',1) = 'TRUE'
    *Where MyLines is your table and it contains an indexed SDO_GEOMETRY column called GEOM.

    This will return the nearest single line to the point. (sdo_num_res=1)

    For more examples, see the documentation or: http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/73/using-oracles-sdonn-operator-some-examples

    If useful, please award points (we do it for love not money!).

    regards
    Simon
  • 2. Re: Closest linestring to a point
    FlyingGuy Explorer
    Currently Being Moderated
    Hey Simon,

    Ok, so I tried your query as:
    WITH myPoint AS (
      SELECT geom as point
        FROM pems_vd_stations where rownum <= 1
    )
    SELECT /*+ORDERED index(L sidx_links_geom)*/
           l.id,
           l.geom,
          p.point,
           sdo_nn_distance(1) as closest_distance
      FROM myPoint p,
           links l
     WHERE SDO_NN(l.geom,p.point,'sdo_num_res=1,distance=1 unit=METER',1) = 'TRUE';
    The source table "pems_vd_stations" are fixed objects and of course I am trying to determine the closest link. So the query returns:
    l.ID = 781538473
    l.geom =
    01) -122.44539, 37.72674, 
    02) -122.44507, 37.72704, 
    03) -122.44481, 37.72727, 
    04) -122.44454, 37.7275, 
    05) -122.44419, 37.72776, 
    06) -122.4438, 37.72803, 
    07) -122.44336, 37.72829, 
    08) -122.44297, 37.72849, 
    09) -122.44237, 37.72878, 
    10) -122.44174, 37.72906, 
    11) -122.44111, 37.72932
    p.point = 
    01) -122.44502, 37.727079
    closest_distance = .45324739
    So given the units and distance parameters I added, then that would make closest_distance meters correct?
  • 3. Re: Closest linestring to a point
    Simon Greener Journeyer
    Currently Being Moderated
    FlyingGuy,

    Yes. However, please note that by supplying distance=1 you are limiting the query to 1 meter from your search point. If nothing lies within 1 meter then you will get nothing. In your case, if the geometry hadn't been at .45324739 meters but had been at 1.45324739 then you would have gotten nothing.

    However, if you remove distance=1 then you will get back the nearest single geometry and sdo_nn_distance(1) will tell you the actual distance. In your case, that will still be your geometry at 0.45324739 meters. If another lay at 1.45324739 meters it would not be returned.

    So, yes, now you have a "formula" for finding the nearest object to a search object.

    If you want the nearest 2 then set sdo_num_res=2. Note, if you start wanting to include attribute clauses in your search then you will have to consider using sdo_batch_size.

    See documentation or my website for examples.

    So, your question is answered non? Points?

    regards
    Simon
  • 4. Re: Closest linestring to a point
    FlyingGuy Explorer
    Currently Being Moderated
    Hey Simon,

    Sorry I clicked helpful by mistake. This spatial stuff is rather new to me so I rely a lot of the kindness of others.

    So now I am tracking down how to figure out just exactly where the point of distance is on the link so I can measure from the end of the link ( either direction ) to that point. I would imagine it is someplace within NN and distance_nn but I can't find it in the docs.

    It would seem that the DNN function would have to mathematically work the LS to find the point of distance <ponder> and then know where that point is since the LS's points could lay out a curve. Is there a function like DNN that is a helper function of NN that returns the point at which it determined the distance?

    Best
  • 5. Re: Closest linestring to a point
    Simon Greener Journeyer
    Currently Being Moderated
    FlyingGuy,

    We're all learning. If not, we must be dead!

    OK, what version of the Oracle database are you on?

    Are you licensed for Spatial or just Locator?

    If you are licensed for Spatial, and you are looking at lines, then have a look at the SDO_LRS package's SDO_LRS.LOCATE_PT and SDO_LRS.FIND_MEASURE functions. If you lines are not measured, first make them measured so you can use the function, SDO_LRS.CONVERT_TO_LRS_GEOM, and pump it into the former.
    v_measure = SDO_LRS.FIND_MEASURE(SDO_LRS.CONVERT_TO_LRS_GEOM(your linestring,0.0,SDO_GEOM.SDO_LENGTH(your linestring,your tolerance),
                         your nearest point)
    This will return the position of the (projected) point on the line.

    Then you can convert this back to a point via:
    SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(your linestring,0.0,SDO_GEOM.SDO_LENGTH(your linestring,your tolerance),v_measure);
    The above are pointers to a solution so you can do some reading. If you can provide some data then a sample SQL statement can be created....

    If you don't have a Spatial license, have a look on my website for some code that can do a lot of this for Locator users. Or email me privately on simon at spatialdbadvisor dot com

    regards
    Simon
  • 6. Re: Closest linestring to a point
    FlyingGuy Explorer
    Currently Being Moderated
    Hey Simon,

    Thanks for the hints. I will give it a look and see if I can skull it out ( I don't expect to be spoon fed ) and if I really get stuck I will get back to you.
  • 7. Re: Closest linestring to a point
    FlyingGuy Explorer
    Currently Being Moderated
    Oh, sorry 11.02 EE with the kitchen sink.
  • 8. Re: Closest linestring to a point
    bkazar Journeyer
    Currently Being Moderated
    Have you tried the following?
    SDO_GEOM.SDO_CLOSEST_POINTS
    Format
    
    SDO_GEOM.SDO_CLOSEST_POINTS(
    
         geom1 IN SDO_GEOMETRY,
    
         geom2 IN SDO_GEOMETRY,
    
         tolerance IN NUMBER,
    
         unit IN VARCHAR2,
    
         dist OUT NUMBER,
    
         geoma OUT SDO_GEOMETRY,
    
         geomb OUT SDO_GEOMETRY);
    
    Description
    
    Computes the minimum distance between two geometries and the points (one on each geometry) that are the minimum distance apart.
    Thanks

Legend

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