I'm looking to select a number of shortest paths from one point to each of a set of points. For example, I would like to generate paths from my house to every coffee shop in the metro area.

Conceptually, it seems like this should be a simple problem, but I'm running into issues when I try to construct a query to actually do it. I managed to get as far as getting a nearest-network-node ID for each of my coffee shops into a table by themselves.

From that point, I get a little lost. As far as I can tell, I have three tables to join together somehow: The network edges table, the table with the destination node IDs, and the table that results from a shortest path query. It's simple enough to join the network edge table geometry to the result of a single path query. But how do I pass a variable from my node IDs table to the end-point parameter of a shortest-path function?

When I tried something like:

SELECT
....
FROM destination_nodes, shortest_path(..., network_nodes.id ,...)

JOIN network_edges ON ....

I get an error saying I can't pass a variable from the same query level to the function.

Can someone please help me understand how I need to structure this query to get the geometry of the paths associated with information from my destination_nodes table?

On Fri, Jul 19, 2013 at 2:45 AM, Nate Wessel <bike756@gmail.com> wrote:

I'm looking to select a number of shortest paths from one point to each of
a set of points. For example, I would like to generate paths from my house
to every coffee shop in the metro area.

Conceptually, it seems like this should be a simple problem, but I'm
running into issues when I try to construct a query to actually do it. I
managed to get as far as getting a nearest-network-node ID for each of my
coffee shops into a table by themselves.

From that point, I get a little lost. As far as I can tell, I have three
tables to join together somehow: The network edges table, the table with
the destination node IDs, and the table that results from a shortest path
query. It's simple enough to join the network edge table geometry to the
result of a single path query. But how do I pass a variable from my node
IDs table to the end-point parameter of a shortest-path function?

When I tried something like:

SELECT
....
FROM destination_nodes, shortest_path(..., network_nodes.id ,...)

JOIN network_edges ON ....

I get an error saying I can't pass a variable from the same query level to
the function.

Can someone please help me understand how I need to structure this query
to get the geometry of the paths associated with information from my
destination_nodes table?