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


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

1 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 
    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?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.