I'd like to calculate the distance between one and multiple other points along a network of LineStrings. I'm aware of this question which calculates the distance on a substring of a LineString, but not over multiple ones.
Q How can I calculate the distances along the network from the Point SITE1
to the two Points WWTP1
and WWTP2
in the example data below? Is this something for pgRouting? Note, I'm not looking for the Euclidean distance.
Data
DROP TABLE IF EXISTS river;
CREATE TABLE river (gid varchar, geom geometry);
INSERT INTO river VALUES
('ID1', 'LINESTRING(1 1, 2 2, 2 3, 4 3)'),
('ID2', 'LINESTRING(4 3, 5 6, 6 8)'),
('ID3', 'LINESTRING(6 8, 8 4, 9 5)'),
('ID4', 'LINESTRING(2 3, 1 4)'),
('ID5', 'LINESTRING(1 4, 1 5, 0 4, -1 1)'),
('ID6', 'LINESTRING(-1 1, -3 2)');
DROP TABLE IF EXISTS site;
CREATE TABLE site (gid varchar, geom geometry);
INSERT INTO site VALUES
('SITE1', 'POINT(1 1)');
DROP TABLE IF EXISTS wwtp;
CREATE TABLE wwtp (gid varchar, geom geometry);
INSERT INTO wwtp VALUES
('WWTP1', 'POINT(0 4)'),
('WWTP2', 'POINT(8 4)');
Best Answer
As you already pointed out, this indeed can be solved with
pgRouting
and will have to be added to your database as an extension. You will have to do some preparations with the data to get it going.First of all you will have to break the linestrings into individual segments in order to build a sound topology:
pgRouting
requiressource
andtarget
columns to compute the toplogy.The function
pgr_createTopology
will add a new table namedriver_segments_vertices_pgr
holding your graph nodes. Now you have everything you need to compute shortest paths between 2 points. The following common table expression selects a start and your destination point. These don't have to sit directly on the topology. When we invokepgr_dijkstra
we tell PostGIS to find the nearest node as we limit it to 1. We do this for the source and destination point. Afterwards we join the shortest path results again with the river_segments table to obtain the geometries of the route. We stitch these together withST_Collect
and voila.