# ST_Line_Locate_Point between two spatial relations with PostGIS

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.

Markus

``````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')
``````
-

I found out what was missing: I needed a third relation that identifies the nearest line to each vertex:

``````SELECT E.id, n.id, ST_Line_Locate_Point(E.geometry,V.geometry)*E.LENGTH AS OFFSET
from bz_edges E, BZ_VERTEX V,
(
select id as point_id,
(select id
from bz_edges
order by st_distance(bz_edges.geometry, BZ_VERTEX.geometry)
limit 1 ) as edge_id
from BZ_VERTEX
) as t3
where E.id = t3.edge_id
and   V.id =  t3.point_id
order by V.id
``````
-