QGIS – How to Extract and Connect Two Closest Points with Matching IDs

pointproximityqgisqgis-expression

In QGIS, a have point vector Layer_A with a unique ID field and point vector Layer_B with multiple points for each Layer_A ID. For each point on Layer_A I want I need to create a line that connects the two closest points on Layer_B that have the same ID as the current Layer_A feature.

How can this be solved using the expression builder in the Model Designer?

My current approach for the Geometry by expression tool using Layer_A as input layer is:

make_line(
    overlay_nearest(
         @Layer_B_OUTPUT,
         $geometry,
         limit:=2
    )[1],
    overlay_nearest(
         @Layer_B_OUTPUT,
         $geometry,          
         limit:=2
    )[0]
)

Here's an example screenshot with Layer_A points in yellow and Layer_B points in grey:

As expected, I am still missing a filter for overlay_nearest() to select only the features with a matching ID. And I don't know if limiting it to the first two occurrences actually returns the two closest features.

I tried to add a filter like this, but it does not seem to work:

with_variable(
    'current_id',
    "ID",
    make_line(
        overlay_nearest(
             @Layer_B_OUTPUT,
             $geometry,
             filter:= ID = @current_id, --This does not seem to work
             limit:=2
        )[1],
        overlay_nearest(
             @Layer_B_OUTPUT,
             $geometry,
             filter:= ID = @current_id, --This does not seem to work
             limit:=2
        )[0]
    )
)

Maybe it's related to the problem described here?

Best Answer

In theory you should be able to adapt your expression to include a filter as follows:

with_variable('layer_a_id',$id,
    make_line(
        overlay_nearest(
             layer:='Layer_B',
             expression:=$geometry,
             filter:="id"=@layer_a_id,
             limit:=2
        )[1],
        overlay_nearest(
             layer:='Layer_B',
             expression:=$geometry,          
             filter:="id"=@layer_a_id,
             limit:=2
        )[0]
    )
)

The problem is that there is a bug (probably) where variables in filter expressions appear to get ignored. See: Variable is not recognize in QGIS expression using overlay_nearest function for details.

So the workaround is - as noted in the above question - to wrap the overlay_nearest calls in an eval. It's very clunky and hopefully it will be fixed in QGIS at some point.

with_variable('layer_a_id',attribute('id'),
    make_line(
        eval(
            'overlay_nearest(
                 layer:=\'Layer_B\',
                 expression:=$geometry,
                 filter:="id"='|| @layer_a_id ||',
                 limit:=2
            )[1]'),
        eval(
            'overlay_nearest(
                 layer:=\'Layer_B\',
                 expression:=$geometry,
                 filter:="id"='|| @layer_a_id ||',
                 limit:=2
            )[0]')
    )
)

Related Solutions

QGIS Proximity – Connecting Points to the Two Closest Highest Values Grouped by Category

Use this expression. On line 3, it creates the 2nd largest value per category as variable @max: get an array of all pob values for the current CATEG value, sort it in descending order and get the 2nd value (index [1]); in the filter part later, we use only pob values larger or equal to this value. Then use overlay_nearest() function:

with_variable(
    'max',
    array_sort (array_agg (pob,group_by:=CATEG),0)[1],  
with_variable(
    'cat',
    CATEG,
make_line(
    $geometry,
    eval('
        overlay_nearest(
            @layer,
            $geometry,
            filter:=pob >= ' || @max || ' and CATEG = ''' || @cat || '''
        )
    ')[0]
)))

The expression working on your dataset: blue=Category A, red=category B; black dotted line: mid-line between the two largest values per category:

Edit:

Challenges in this expression is how to include a filter condition so that we can compare the attribute value of the feature currently evaluated inside the overaly_nearest() function not to a fixed value, but a dynamic expression, which is based, as here, on aggregate functions. So the challenge is to including the parent feature or other features (when aggregating). You can't include this directly in the filter, so you have to use a trick and concatenate the whole overlay_nearest() function as a text string and then evaluating it with eval() - see here: gis.stackexchange.com/a/415248/88814.

Especially tricky is that the dynamically calculated part (referring to the parent/aggregated features) has to remain outside the string so that it will be calculated correctly (on the parent feature or any other features when using aggregate functions) and to return the desired value(s). So for clarity, the value is created as variables @max and @cat outside the overlay_nearest() function and the variable is then inserted in between the string parts by concatenating the different parts with pipes ||.

On top of this, the value stored in the @cat variable has be be passed as a string to be concatenated, so you have to use not less than three single quotes ''' one after the other (as two single quotes are used inside a string to introduce a quote and thus prevent the string being ended).

An alternative, equivalent expression to the above one, avoiding the variable and including everything in the string concatenation part, is:

make_line(
    $geometry,
    eval('
        overlay_nearest(
            @layer,
            $geometry,
            filter:= pob >= ' || to_string(array_sort (array_agg (pob,group_by:=CATEG),0)[1])|| 
            ' and CATEG = ''' || CATEG || '''
        )
    ')[0]
)

QGIS Proximity Analysis – Obtaining the Nearest Line Between Two Point Layers with Constraint of Line Distance Layer

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 :

Best Answer

Related Solutions

QGIS Proximity – Connecting Points to the Two Closest Highest Values Grouped by Category

QGIS Proximity Analysis – Obtaining the Nearest Line Between Two Point Layers with Constraint of Line Distance Layer

Related Question