This content has been marked as final. Show 8 replies
You should look at SDO_NN as it will do what you want.
*Where MyLines is your table and it contains an indexed SDO_GEOMETRY column called GEOM.
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'
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!).
Ok, so I tried your query as:
The source table "pems_vd_stations" are fixed objects and of course I am trying to determine the closest link. So the query returns:
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';
So given the units and distance parameters I added, then that would make closest_distance meters correct?
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
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?
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?
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.
This will return the position of the (projected) point on the line.
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)
Then you can convert this back to a point via:
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....
SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(your linestring,0.0,SDO_GEOM.SDO_LENGTH(your linestring,your tolerance),v_measure);
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
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.