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