[GIS] Generating nodes where edges meet, PostGIS, pgRouting

pgroutingpostgis

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.

Best Answer

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?

Related Question