# How to obtain the nearest line between two point layers with the constraint of a line distance layer

From three layers:

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

• Ok ... I did resolve this exact issue (or very close). What you need here is graph theory using your street and your building to generate a good routing graph. Then you compute your distance on the graph. Good tools are Pgrouting in PostgreSql and using dijkstra, NetworkX in pure python library also. But this can't be really solved only using geometries and Qgis Geoprocessing Jul 6 at 12:26
• Yes, I understand that this may not be a trivial problem to solve directly with only the geometric functions of QGIS Geometry Generator. However, perhaps there would be an intermediate solution (graphos theory). That is, do a topological integrity process that relates points to lines. I continue investigating and thanks for your comment. Jul 6 at 12:43
• Are you interested if I post some adapted code in SQL/Pgrouting that would solve your issue as described here ? Jul 6 at 13:01
• Of course. I will adapt your solution to my data, and I will try to change my initial approach to solve my goal Jul 6 at 13:07

Method with PostgreSql and Pgrouting

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,
)
;;

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

;;

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
;

alter table ___.waste_points
foreign key (vtx)
references ___.complete_network_noded_vertices_pgr(id)
;

alter table ___.house_points
;

alter table ___.house_points
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 :