From three layers:
'POINTS': Points of house facades
'CONTROL': Waste collection points
'STREETS': Street axes
When you apply an standard expression like the following to find and draw the nearest line between two layers of points you get the following result:
Expression
collect_geometries (
array_foreach (
overlay_nearest('CONTROL', $geometry, limit:=1),
make_line (
$geometry,
@element
)
)
)
Standard Result
However, this analysis has the problem that the expression does not take into consideration the constructions constraints. As a consequence of this problem, in some cases, the result is distorted, since in reality the shortest distance is conditioned by streets path. I try to show with a screenshot this problem, showing the virtual correct result handmade:
I think it could be solved by taking into consideration a layer with polyline geometry representing the street axes. The objective would be to draw the nearest line between the two point layers doing an expression that segments the street axis between the points and calculates the distance. Once the linear distance of the street axis between two points is obtained, draw the line from the point of origin ('POINTS') to the point of destination ('CONTROL') with the shortest distance.
The objective I am looking for is to adapt the initial idea of the standard expression taking into account the restriction of the street axes.
Best Answer
Method with PostgreSql and Pgrouting
Install PostgreSql here for windows : https://www.postgresql.org/download/windows/
you can then connect whatever table as datasource in Qgis to check the data
Explanation of the method :
In order to get real travel distance beetween your points, we need to set up a graph that materialize what you accept as deplacement
so here :
we add to the graph the nearest line from the point house facade to complete the graph and we do the same for your waste point collect (but they seems to be on the street already)
the Sql code will look like this :
With this data structure you can also create SQL function that will return the nearest waste point for each house point.
to go further , multiple access to houses ? how many houses use a waste points ?
I tried to produce the easiest "working" sample, but I didn't really check if everything really works , it may have some misprint in the code. feel free to correct it
docs :