distanceNetworkqgisroad
I have 2 different point layers that I want to use as origins (Company offices) and a separate layer with 500+ points in it (Client Stores) I also have a road network layer.
For each of the 500+ stores I would like to have a field in the attribute table showing the distance along the road network back to each of the Offices.
I currently use QGIS.
Read point data for closest road in PostGIS:
SELECT cp.*, r.the_geom FROM roads r, (SELECT p.* ,r.id from point p, roads ro ORDER BY ST_Distance(p.the_geom, ro.the_geom) ASC LIMIT 1) as cp WHERE cp.id = r.id
cp alias should have one closest road for every point, WHERE ST_Dwithin(geom,geom) < 1 should make search faster
Little closer answer is (in PostGIS)
SELECT r.*, p.* FROM roads r, points p WHERE ST_Intersects(r.geom,p.geom) IS TRUE
That returns roads rows several times if there are more than one intersection on road
Solution A
SELECT r.the_geom, r.gid , p.name INTO roads_with_names WHERE ST_Intersects(r.geom,p.geom) IS TRUE GROUP BY r.gid, p.name
Return only one row per road (i'm not 100% how crosstab works)
select * from crosstab('select r.the_geom, 'name' , r.name from roads_with_names r ORDER 1,2) AS ct(the_geom geometry, name text, name2 text , name3 text , name 4 text );
Or use LEFT join on road id. There is several ways to do it
Should create new table which has road geoms, gid and name from point table. There should not be any dublicate r.gid + p.name rows. In QGIS you can define label field from database (assuming that you have PostGIS source) from Layers properties.
You may need to use first answer if your data does not intersect.
Closest point on road in PostGIS is
SELECT ST_ClosestPoint(r.geom, po.geom) as closest_point, r.id FROM roads r, (SELECT p.* ,r.id from point p, roads ro ORDER BY ST_Distance(p.the_geom, ro.the_geom) ASC LIMIT 1) as po where r.id = po.id
Above should return point geometry on road which is closest to point on point table which is closest to road.
I first thought this question was basically same as another recent question Points layer distance from the start of line layer in QGIS.
However, there is an added complexity in this one, that OP requires distance between (not a distance from the start point).
So I needed QGIS 3.0 get_feature_by_id() function to calculate the difference between records.
(1) Calculate the distance of each point along the line, from the start point of the line.
Please see Points layer distance from the start of line layer in QGIS
(2) Calculate distance between points, based on the distance taken from previous step (1).
Again, open the attribute table of Points layer, which has distance field.
Create a new field interval by the below expression:
attribute(get_feature_by_id('Points', $id+1), 'distance')
- attribute(get_feature_by_id('Points', $id), 'distance')
This expression calculates the difference between points by (value at next row minus value at current row).
NB: The $id is unrelated to id field shown in the picture. Sorry if it is confusing.
Best Answer
There are a large number of potential answers to this question, but for a QGIS only solution, I would look at using the net functions in Grass, which should be accessible if you install Grass 7 (you may already have it). Please note you may encounter a bug where the path to your Grass 7 files is not properly defined - to fix this just go to Processing->Options->Providers->Grass GIS 7 commands and then set the folder correctly.
Have a look here - you can get to this function by looking for v.net.path in the processing toolbox (Ctrl-Alt-T if it's not on).
Then define your road network (you will need to have a point layer for nodes and a line layer split at these nodes) and tolerances (how far each link can be away from the node to be considered part of the network). There will be some work to do depending on how your dataset is organised, but you could also look at v.net to see if that can help.
Then you can define the routes you want to calculate in a json file which apparently has to be in the format
id start_point_x start_point_y end_point_x end_point_y. You then pass this to "Name of file containing start and end points" parameter in the Grass tool and hopefully you will have some results. More likely you will have problems along the way, but at least this might point you in the right direction.Other options not involving QGIS include pgRouting (based on postgis / postgres), spatialite_network or graphopper. There are of course commercial offerings as well, which will most likely be slightly easier to use for you.