QGIS Field Calculator Aggregation with a intersect query

Question

I have point (manholes) and line (sewer main) datasets and want attributes from the points at both ends of the line.

If I run the following after I install RefFunctions in field calc on the line

geomintersects('Sewer_MH_Test','RL_of_Lid')

I get the value I need from the start of the line (3.78). How do I get the value at the end (3.75) into a new column? Is there some aggregator to use?

---

Based on @Taras' answer I have been trying the following but it keeps crashing.

WITH start_data AS (
SELECT l.GID_New AS line_id_start, p.US_IL AS x
FROM points AS p
JOIN lines AS l ON st_intersects(start_point(l.geometry),p.geometry)
),

end_data AS (
SELECT l.GID_New AS line_id_end, p.DS_IL AS y
FROM points AS p
JOIN lines AS l ON st_intersects(end_point(l.geometry),p.geometry)
)

SELECT l.*, start_data.x, end_data.y
FROM lines AS l
JOIN start_data ON l.GID_New = line_id_start
JOIN end_data ON l.GID_New=line_id_end

This shows the output I want...

The test data is only about 20 lines and points - it can be downloaded from https://drive.google.com/file/d/12rUV_pAaevs9vATbIsgc9QJvvziRygGN/view?usp=sharing

Answer 1

you can user the aggregate expression for that, something like:

aggregate(
layer:= 'point_layer',
aggregate:='concatenate',
expression:=fieldname_pointlayer,
concatenator:=', ',
filter:=intersects($geometry,
geometry(@parent))
)

for labeling the line layer. If the point is not intersecting the polyline - like Taras pointed out in his comment - you can add a buffer around the point geometry: $geometry is the point geometry and geometry(@parent) is the polyline geometry.

Answer 2

Tested on QGIS 2.18 and QGIS 3.4

If you are not considering only functions in the Attribute Table I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume we have 10 features in "points" (lila), four in "lines" (green), accordingly, see image below.

With the following Query, it is possible to achieve the result

WITH start_data AS (
SELECT l.id AS line_id_start, p.x AS x
FROM points AS p
JOIN lines AS l ON st_intersects(start_point(l.geometry),p.geometry)
),

end_data AS (
SELECT l.id AS line_id_end, p.y AS y
FROM points AS p
JOIN lines AS l ON st_intersects(end_point(l.geometry),p.geometry)
)

SELECT l.*, start_data.x, end_data.y
FROM lines AS l
JOIN start_data ON l.id = line_id_start
JOIN end_data ON l.id=line_id_end

The output Virtual Layer will give lines where exists an intersection between lines and points, including attributes inherited from snapped points to the ending/starting points of lines.

---

Based on the data set provided by @GeorgeC, the query will look like

WITH start_data AS (
SELECT l."GID_New" AS line_id_start, p."RL_of_Lid" AS startd
FROM points AS p
JOIN lines AS l ON st_intersects(start_point(l.geometry),p.geometry)
),

end_data AS (
SELECT l."GID_New" AS line_id_end, p."RL_of_Lid" AS endd
FROM points AS p
JOIN lines AS l ON st_intersects(end_point(l.geometry),p.geometry)
)

SELECT l.*, start_data.startd, end_data.endd
FROM lines AS l
JOIN start_data ON l."GID_New" = line_id_start
JOIN end_data ON l."GID_New" = line_id_end