*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'
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
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)
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);
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.