I have a table of POINTs that represent barriers. I would like to be able to find the closest edge id on the edge graph so that I can have the routing algorithm ignore that edge (thus route around it).
My attempt looks something like this:
SELECT id FROM edge_table ORDER BY (st_distance(edge_table.geom), restrictions_table.points), false) ASC;
The problem is that st_distance requires two geometries and I am trying to pass in all of the barrier points. I can get this to work for one point but I need this whole table of points, returning multiple edge ids.
The only way I can think to do this would be to use a loop and go through all of the points in the restrictions table and then find the edge id they are closest to one by one.
I know loops are usually performance heavy and not recommended in SQL so I was wondering if anyone had a better approach to this problem.
The routing algorithm I am using is pgr_trsp().