I am trying to calculate the closest edge and its offset for a relation of points. If using only one point that approach works fine, but if I have several points in the relation I do not know how to do that, because it always return all the edges within the range i specify (here 100). By setting the limit this does not give me the correct answer. What i need is the following: join each tuple of the vertex relation with each tuple of edge relation and return the closest edge to the current vertex.
Any idea how to solve this problem?
thanks for any suggestion.
SELECT L.ID, ST_Line_Locate_Point(L.EDGE_GEO,L.POINT_GEO)*L.LENGTH AS OFFSET, l.node_id FROM ( SELECT E.ID, E.GEOMETRY EDGE_GEO, E.LENGTH, N.GEOMETRY POINT_GEO, n.id node_id FROM BZ_EDGES E, BZ_VERTEX N WHERE E.EDGE_MODE=0 AND st_dwithin(E.GEOMETRY,N.GEOMETRY, 100) ORDER BY node_id, st_distance(E.GEOMETRY,N.GEOMETRY)) L
In Oracle Spatial this approach works fine:
SELECT id, dist, offset, node_id FROM ( SELECT sdo_nn_distance(1) dist, SDO_LRS.get_measure(SDO_LRS.PROJECT_PT( SDO_LRS.CONVERT_TO_LRS_GEOM(e.geometry), v.geometry)) offset, e.*, v.id node_id FROM BZ_EDGES E,BZ_VERTEX V WHERE sdo_nn(v.geometry,e.geometry, 'SDO_NUM_RES=1', 1 )='TRUE')