[OSM-dev] PostGIS: algorithm to find the nearest way segment to a mouse click?

[Jochen Topf]

How about something like this:

You have the tables: nodes, way_nodes, and ways just like in MySQL (and
as created by Osmosis --write-pgsql task). In addition a geometry column
on the 'nodes' and 'ways' tables. Then

Find the closest way and closest point on this way as you have done.
Find all nodes in this way through the way_nodes table and calculate
their location along the way to a temp table:

CREATE TEMP TABLE ...

INSERT INTO nodes_tmp (node_id, location)
    SELECT n.id, ST_line_locate_point(w.geom, n.geom)
        FROM nodes n, way_nodes x, way w
            WHERE n.id=x.node_id AND x.way_id=w.id AND w.id=your_found_nearest_way;

SELECT a.node_id, b.node_id FROM nodes_tmp a, nodes_tmp b
    WHERE your_found_nearest_location = BETWEEN(a.location, b.location);

Something like this. Hope it was understandable.

You might be able to do this without a temp table. And you have to make
sure it works in case its not between two nodes, but directly on one
node.

Jochen

On Fri, Aug 01, 2008 at 01:39:34PM +0100, Nick Whitelegg wrote:
> As part of a walking routes feature I want to add to Freemap, I want the 
> user to be able to select the closest way segment to a mouse click.
> By "segment" I don't mean the old-style pre-0.5 OSM segment, but rather a 
> series of nodes within a way (call it way A) which lie between two other 
> ways which intersect the way (call them B and C). The way segment would be 
> defined by way ID and start and end node index within the way, e.g.
> 
> Way 303, start node index 2, end node index 6
> 
> By reading the postgis reference manual I've come up with a reasonable way 
> of doing this, but it might not be the most efficient. So can any postgis 
> gurus offer a better way? The main problem, necessitating a rather 
> cumbersome algorithm, is that postgis does not appear to have a function 
> to find the index of a given node (point) within a way.
> 
> The algorithm is as follows:
> 
> 1. Find the nearest way to a given position (this would be a mouse click), 
> and the distance (between 0 and 1) of the position along the way.
> 2. Store all the coordinates of all the nodes (points) making up that way.
> 3. Find the intersections of the selected way, and their distances (0 to 
> 1) along the way using the query:
> 
> SELECT t2.highway,t2.osm_id as wayid,
>                 line_locate_point(t1.way,intersection(t1.way,t2.way)) as 
> posn,
>                 astext(intersection(t1.way,t2.way)) as intn 
>                 from planet_osm_line t1, planet_osm_line t2 
>                 where intersects(t1.way,t2.way) and t1.osm_id != t2.osm_id 
> 
>                 and t1.highway!='' and t2.highway!='' and 
>                 t1.osm_id=$wayid
> 
> where $wayid is the ID of the way we're interested in.
> 
> 4. Loop through the result set to find which intersections bracket the 
> selected point, and (this is where it's a bit cumbersome) find the indices 
> of the nodes of intersection within the way by comparing the intersection 
> node with each node of the way in turn (php code).
> 
> 5. We now have the indices of the nodes of intersection, so extract the 
> relevant nodes from the way.
> 6. Serialise the extracted nodes as XML.
> 
> So is this the best way to do it, or are there any better ways?
> 
> Thanks,
> Nick
> 
> 
> 
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
> 

-- 
Jochen Topf  jochen at remote.org  http://www.remote.org/jochen/  +49-721-388298