1

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? enter image description here


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... enter image description here

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

  • 1
    Is your point dataset snapped to the line dataset or it is somewhere nearby? – Taras Jul 18 at 4:44
  • It's snapped to the points. – GeorgeC Jul 19 at 0:11
3

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.

  • I tried but get an error <<Eval Error: Could not calculate aggregate for: RL_of_Lid>> when using <<aggregate( layer:= 'Sewer_MH_Test', aggregate:='concatenate', expression:=RL_of_Lid, concatenator:=', ', filter:=intersects($geometry, geometry(@parent)) )>> . I want to either create one field in the lines with RL values delimited or multiple fields with the RL values in each of them. – GeorgeC Jul 19 at 0:59
  • 1
    I just needed to make the value a string {{aggregate( layer:= 'Point_Clip', aggregate:='concatenate', expression:=to_string(RL), concatenator:=', ', filter:=intersects($geometry, geometry(@parent)) )}} -works but doesn't have the start/end value consistently. See gis.stackexchange.com/questions/329482/… – GeorgeC Jul 22 at 2:19
  • thanks to complete my answer with your comment! – eurojam Jul 22 at 19:09
3

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.

input

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

output

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
  • thanks - I have tried but am having some issues. Please see updated question. – GeorgeC Jul 19 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.