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.
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.
If I import the data into Spatialite and run
select RoadMntnc, group_concat(Locality) from clip_DSC_Roads group by RoadMntnc
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.