Find all ways which have a given node

I have a database of ways and nodes. A way is defined by two or more nodes. The list of node_id's that a way contains are stored in the way_nodes table.

I have a table of node joins, points where two or more ways join. A join is defined as two or more ways having the same node. I'm looking to find the ways that have joins. So trying something like this:

SELECT DISTINCT 
       way_id 
  FROM way_nodes wn 
  JOIN path_vectors pv ON pv.node_id = wn.node_id

The table path_vectors contains a list of all nodes which are joins between ways, it is precalculated.

I found it was too slow, maybe 5 ways per second, with my large ~10,000 way and ~40,000 node database. This is related to my previous question.

The goal with this information is to simplify road networks into a simple graph, which can be used with a pathfinder to plot an optimal route. I'm using Open Street Map data, hence the similar开发者_如何学Python terminology.

As I understand it, you are trying to build a list of all way_id values from the way_nodes table, where there is a corresponding node_id on the path_vectors table.

If all node_id values are stored on the path_vectors table, this can be simplified to:

select distinct way_id from way_nodes

If not all node_id values are stored on the path_vectors table, this may be the most efficient way to query the values:

select distinct way_id from way_nodes wn
where exists
(select null from path_vectors pv
 where wn.node_id = pv.node_id)

I suggest checking that there is an index on the node_id field on the path_vectors table.

Unless I'm misunderstanding something, way_nodes is a mapping between a way and the nodes that make up that way. You should be able to just query that one table to find all ways with a given node.

SELECT DISTINCT way_id FROM way_nodes WHERE node_id = XYZ;

Unless the question is how to do this for all nodes...

consider this...

select node_id, count(distinct node_id)
from way_nodes 
group by node_id
having count(distinct way_id) > 1

That will give you the ids of all nodes that have more than one way associated.

now just combine it with a simple query back in the other direction, to get all the way_ids:

SELECT way_id
FROM way_nodes JOIN (
    select node_id, count(distinct node_id)
    from way_nodes 
    group by node_id
    having count(distinct way_id) > 1
) as nn ON ( way_nodes.node_id = nn.node_id )
GROUP BY way_nodes.way_id

I don't have your db, so I can't test it... but something like that should work. If you can't get this to work, then post back, and we'll help you get it sorted out.

[edit]

"The table path_vectors contains a list of all nodes which are joins between ways, it is precalculated."

so... given that, you could just do:

SELECT way_nodes.way_id
FROM 
  way_nodes
  JOIN
  path_vectors
  ON (path_vectors.node_id = way_nodes.node_id)
GROUP BY way_nodes.way_id