Generating nodes where edges meet, PostGIS, pgRouting

Question

Recently I was doing some research on how to assign nodes (as POINT type geometry) to existing edges of a topology network and store them in a different table. Call it nodes table.

I have all the edges, they have source and target fields among some other fields as well. My main idea is, that say, I want to input a location - POINT(lat, lng) - into my routing app. Then what I want my app to do is to find the point closest to my given location in the nodes table in order to start the routing process.

In my research, I examined the functions ST_StartPoint() and ST_EndPoint() as possible functions that could come handy while implementing this. Ideally, I would loop thru the edges and get and record both the start- and the endpoints of each, but that raises another issue of having multiple identical points in the nodes table.

Am I on the right track of implementing this right or could you advise a better solution? Any feedbacks or ideas are welcome!

Thanks in advance

Tamas

p.s.: The proximity search feature is fine, I can do that. The only issue is the node generation.

Answer 1

All information you need is already in your road network table if it contains source, target and geometry information. With this information you can for example create a VIEW that contains all nodes with point geometry:

CREATE VIEW nodes_from_ways AS 
    SELECT source AS node, ST_StartPoint(the_geom) FROM ways 
  UNION 
    SELECT target AS node, ST_EndPoint(the_geom) FROM ways;

With UNION you combine source and target and only unique node ID's should remain.

Does this work for you?