5

I have a segmented line (road) dataset that potentially runs through multiple area polygons (localities). I want the road dataset that is grouped by the unique field (RoadMntnc) to have a concatenated list of the localities it passes through in the direction of flow of the road.

The following partially works

select RoadMntnc, lower(Locality) from clip_DSC_Roads group by RoadMntnc, lower(Locality) order by RoadMntnc

But it lists every locality each segment is in and doesn't follow the direction of travel of the road.

enter image description here

I need the result as

Miallo_Bamboo_Creek_Rd | Miallo to Whyanbeel to Bamboo
Kingston_Rd | Whyanbeel

So if it is multiple localities (pink below) it should be in the direction that the road is.

To clarify a bit -there is a field with locality that changes in the dataset when a segment of road is inside different localities. It doesn't come from an external dataset -however if it has to then this won't be a problem either. This is why there is no 'intersect' in the SQL.

A QGIS or Mapinfo sql process would be fine as well.

enter image description here

UPDATE

Based on Concatenate or group by values using spatial join in QGIS?

If I import the data into Spatialite and run

select RoadMntnc, group_concat(Locality) from clip_DSC_Roads group by RoadMntnc

I get enter image description here

But how do get just the unique values and also check if it's in the right order?

I have tried

select RoadMntnc, distinct group_concat(Locality) from clip_DSC_Roads group by RoadMntnc

but get a syntax error.

Improve this question
8
  • If you are willing to use QGIS expressions, aggregate() and the concatenate function within it now have parameters to allow for extracting unique values and concatenating as of QGIS 3.8 (see this link: changelog.qgis.org/en/qgis/version/3.8/…) I would recommend standardising the type case of your input field before concatenating too so as to filter out duplicates more easily.
    – she_weeds
    Sep 4, 2019 at 6:07
  • Thanks -that's how I got the list in the first place but the problem is it generates the list that I am now trying to clean up and show just the uniques. !--Concatenate( "Locality", group_by="RoadMntnc", concatenator=',')--!
    – GeorgeC
    Sep 4, 2019 at 6:16
  • @GeorgeC Did you try using distinctinside the group_concat expression?
    – NettaB
    Sep 4, 2019 at 18:11
  • @NettaB thanks. That works but the main issue still remains - it needs to be in the direction of flow of the road and not just random. !-- select RoadMntnc, group_concat(distinct lower(Locality)), count(RoadMntnc) as Count from clip_DSC_Roads_SL group by RoadMntnc --!
    – GeorgeC
    Sep 5, 2019 at 0:23
  • @GeorgeC add an 'order by' clause
    – NettaB
    Sep 5, 2019 at 1:28

1 Answer 1

Reset to default
6

From the answers in the comments:
1. In order to concatenate unique values with group by clause: group_concat(distinct Locality)

Order by:
2. Data ordering (by the data sample link from comment): From your data, it looks like that the start/End chainage fields are connected with the direction & order so this looks the most promising sql (see image):

select RoadMntnc, group_concat(distinct lower(Locality)) from clip_DSC_Roads group by RoadMntnc order by RoadMntnc, Start_Chainage

enter image description here

Improve this answer
1
  • Works well since I had the start_chainage field. If anyone needs the full process - I had to run Dissolve by RoadMntnc first, then 'Points along geometry' (at 10m in my case). Finally on the segmented road dataset (not the dissolved one) I ran the following in field calc to get the start chainage for each Road segment - aggregate(layer:='DSC_Roads_10m_Chainage.tab', aggregate:='max', expression:="distance" * (attribute(@parent, 'RoadMntnc') = attribute($currentfeature, 'RoadMntnc')),filter:= within(start_point(geometry(@parent)),buffer($geometry,1,10)))
    – GeorgeC
    Sep 6, 2019 at 1:34

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.