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 :
create extension if not exists postgis
;
create extension if not exists pgrouting
;
create schema ___
;
-- create the table street , (you can also import your data from Qgis with drag&drop)
create table ___.street (
geom geometry(Linestring, $SRID),
id serial primary key,
name varchar,
circulation varchar,
source integer,
target integer
)
;;
create table ___.complete_network (
geom geometry(Linestring, $SRID),
id serial primary key,
name varchar,
circulation varchar,
source integer,
target integer
)
;;
create table ___.house_points (
geom geometry(Point, $SRID),
id serial primary key,
name varchar,
comment varchar,
adresse float
)
;;
create table ___.waste_points(
geom geometry(Point, $SRID),
id serial primary key,
name varchar,
comment varchar
)
;;
-- we add line house-street to the graph
insert into ___.complete_network(geom)
select St_Makeline(St_closestPoint((select St_collect(geom) as geom from ___.street),hp.geom), hp.geom) as geom
from ___.house_points as hp
;;
-- we add line waste-street to the graph
insert into ___.complete_network(geom)
select St_Makeline(St_closestPoint((select St_collect(geom) as geom from ___.street),hp.geom), hp.geom) as geom
from ___.waste_points as hp
;;
-- we add street
insert into ___.complete_network(geom, id , name, source, target)
select geom, id , name, source , target from ___.street
;;
-- we use pg_routing function to generate a correted topology noded for graph
perform pgr_nodenetwork('___.complete_network', 0.001, 'id', 'geom', 'noded')
;;
-- we create topology for graph
perform pgr_createTopology('___.complete_network_noded', 0.001, 'geom', 'id', 'source', 'target')
;;
-- we use a reference to the node in the graph to our raw data information (bonus but useful)
alter table ___.waste_points
add vtx integer
;
alter table ___.waste_points
add constraint vtx_foreign_key_pei
foreign key (vtx)
references ___.complete_network_noded_vertices_pgr(id)
;
alter table ___.house_points
add vtx integer
;
alter table ___.house_points
add constraint vtx_foreign_key_pei
foreign key (vtx)
references ___.complete_network_noded_vertices_pgr(id)
;
-- exemple of shortest path query for node 7 to 12 (change the number to compute path for another set of nodes)
-- here you will get the distance in the return cost of this function and if you use a waste node and house node, you get what you were looking for
SELECT seq, id1 AS node, id2 AS edge, cost
FROM pgr_dijkstra(
'SELECT id, source, target, st_length(geom) FROM ___.complete_network_noded',
7, 12, false, false
);
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 :
- https://postgis.net/docs
- https://docs.pgrouting.org/2.0/en/src/dijkstra/doc/index.html
There are several things regarding your expression:
- This is the most critical point, that has to do with the
overlay_nearest()
function. It will also consider the same feature as a closest taken from the same layer. See this thread for more details: QGIS expression: when using overlay_nearest sometimes a feature considers itself its nearest neighbour. It also refers to a QGIS discussion on the GitHub
- For each point, you are attempting to calculate the centroid for the 5 points nearest to it. However, in your first expression, you are calculating the centroid for five lines instead, init?
- Keep in mind, that for a vast data set, one should think of a better approach, even with a spatial index
- Number of input points: N, number of centroids: N, number of lines N*10
Let's assume there is a point layer called 'points_in_polygon' (N=10), see the image below:
After using this expression:
collect_geometries(
array_foreach(
array_remove_at(
overlay_nearest(
layer:='points_in_polygon', -- change accordingly
expression:=centroid(
collect_geometries(
array_remove_at(
overlay_nearest(
layer:='points_in_polygon', -- change accordingly
expression:=$geometry,
limit:=6 -- change correspondingly
),
0)
)
),
limit:=11 -- change correspondingly
),
0),
make_line(
@element,
centroid(
collect_geometries(
array_remove_at(
overlay_nearest(
layer:='points_in_polygon', -- change accordingly
expression:=$geometry,
limit:=6 -- change correspondingly
),
0)
)
)
)
)
)
The above expression is sure can be optimized with the with_variable()
to avoid double writings.
Afterwards, apply the "Multipart to singleparts" geoalgorithm.
And get the output like this (N=120):
Usage of the "Delete duplicate geometries" can also be feasible.
Best Answer
Use the expression below.
The expression in action:
Explanation how the expression works:
The expression creates a wedge buffer using function
wedge_buffer()
with a buffer width (in degrees) that depends on the distance of the current point from the center.You must define a maximum distance (in my example: 2000 m), corresponding e.g. to the point furthest away from the center, as being 100%, than calculate the percentage of the distance the current point is away from the center (like e.g. 0.7 for 70% of the max. distance) and multiply this with 360.
As this means that the point furthest away would result in a closed circle, but you want only a part of it (arcs), add a coefficient, (in my case 0.1) that you use to divide the angle of the resulting wedge buffer width. So in my case, I have (pseudocode):
360 degrees * [distance from center to current point] / (2000 * 0.1)
. Feel free to change this value as you like so that it outputs the result you expect.The wedge-buffer is a polygon, but you want a line (arcs). Create a circle from the center with the distance to each of the points, then get the circle's bondary (line). The intersection of this line with the wedge buffer is the result.
The first six lines of the expression are used to create two variables that are used several times afterwards: 1) get the center from where the distance to each point is measured from the layer
center
(change layer name if necessary) and 2) the distance from this center point to the current point.Edit: there is a variant to to make the arc length inversely proportional to the distance (your comment). For this, in the expression above replace
360*@distance/20000
with1/@distance^2*10000000
, where, again,10000000
is a coefficient that you should change to adapt your expression.