Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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') 
share|improve this question

1 Answer 1

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.