8 Replies Latest reply on Mar 11, 2013 9:32 PM by Bkazar-Oracle

    Closest linestring to a point

      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

          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*/
                 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!).

          1 person found this helpful
          • 2. Re: Closest linestring to a point
            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)*/
                   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

              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?

              • 4. Re: Closest linestring to a point
                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?

                • 5. Re: Closest linestring to a point
                  Simon Greener

                  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

                  • 6. Re: Closest linestring to a point
                    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
                      Oh, sorry 11.02 EE with the kitchen sink.
                      • 8. Re: Closest linestring to a point
                        Have you tried the following?
                             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);
                        Computes the minimum distance between two geometries and the points (one on each geometry) that are the minimum distance apart.