QGIS Labeling – How to Avoid Duplicate Attributes and Condition by Two Fields

expressionlabelingqgis

In a layer with point geometry, I have the house number label. Each geometry has the HOUSENUM, PARCEL and STREET fields. In some cases the geometry is duplicated (equal geometry), in some cases the house number attribute could be also duplicated.

I am using the following expression to get the labels avoiding duplicates. But I need to be more precise and take into consideration the groupings by PARCEL and STREET. At the same time this function is not working well as it seems to duplicate labels in some cases.

array_to_string(string_to_array(aggregate(
  layer:='HOUSE',
  aggregate:='concatenate_unique',
  expression:="HOUSENUM",
  concatenator:='\n',
  filter:=intersects($geometry, geometry(@parent))
)),',')

I am sending a screenshot of the result I am getting at the moment with the display of the attribute table for three different PARCELS, so that you can see the structure of the table and the duplicate values:

PARCEL 00001 and STREET A and B:

PARCEL 00002 and STREET A:

PARCEL 00004 and STREET A and B and C:

And I send screenshot of the expected result. I am interested in solving the conflict of duplicated geometries and filtering without duplicates the HOUSENUM texts according to the PARCEL and the STREET to which they belong. Regarding the Placement expected, just show the position to understand the problem.

Best Answer

To prevent the duplicated labels, you can try this to render the label only once per PARCEL|STREET group.

case
    -- render the label for only the first feature of the group
    when $id = array_min(array_agg($id, group_by:="PARCEL"||'|'||"STREET"))
    then
        array_to_string(string_to_array(aggregate(
            layer:='HOUSE',
            aggregate:='concatenate_unique',
            expression:="HOUSENUM",
            concatenator:='\n',
            filter:=intersects($geometry, geometry(@parent))
        )),',')
end

Related Solutions

QGIS – How to Label Duplicate Geometry Features Without Text Overlapping

In the label placement settings, increase the distance of the label so that all labels have enough place.

The larger the distance, the farther labels are away form the feature and the more labels are shown. You might use callout lines to connect the labels to the features.

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 – How to Label Duplicate Geometry Features Without Text Overlapping

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

Related Question