Relate two tables based on geom

Question

I have a problem, which is, I have a table ways (like in the Workshop of pgRouting) and I also have a different table street_name, where it contains the name of the street (among other information). Both have a column geom and I want to connect a given edge of the pgr_dijkstra calculated route with the name of the nearest point in street_name. I'm already using a version of this function to convert lat/long in source/target values. Is it possible?

Answer 1

From an example into the blue, because no routing code and not very fast O(N^2)! I have a configuration of windmills (point data) in the table windmills and surrounding objects in the table objects (polygon data). Create a view or a FROM-Statement as follows.

CREATE VIEW dist_mill_object AS
SELECT DISTINCT ON (a.gid) 
   a.gid AS obj_id, 
   b.gid AS mill_id,   
   a.cell_name,
   b.mill_number, 
   st_shortestline(st_centroid(a.geom), b.geom) AS geom
 FROM objects as a, 
      windmills as  b
 ORDER BY 
   a.gid, 
   st_distance(st_centroid(a.geom), b.geom);

So you get the shortest a.gid, b.gid pair.