2

I am stuck for several days trying to get array values link together. I spent hours without getting serious results.

I have a bus stop points table with arrays containing the bus routes numbers serving the stop:

ID transfers
1  (1,2,3)
2  (2,4,A)
3  (1,3,C)
Etc

I have another table with individual values containing the route number and the color

ID    Route     Color
1       1       #CD5C5C
2       2       #FFA500
3       3       #00FF00
4       4       #6A5ACD 
5       A       #1E90FF
6       B       #A52A2A
7       C       #008B8B
Etc

I am trying to link them together to get the routes and colors values for each stops as follow:

For example with the stop 3:

Route 1 #CD5C5C to "destination1"
Route 3 #00FF00 to "destination3"
Route C #00FF00 to "destination7"

I have been able to link the colors, but I can't manage getting the route number as well next to the color.

This way I can already get the colors, but I can't link routes for each ones, the best I had was all the routes repeated every time for each color.

array_to_string(

-- Define the variable for transfers Arrays
with_variable('transfers',string_to_array("transfers",','),

array_foreach( 
-- Define variables to be used
-- Define the variable for Array index value result
with_variable('index',

-- Define the variable for routes Arrays
with_variable('routes',
aggregate(
layer:= 'Bus_routes',
aggregate:= 'array_agg',
expression:=  "route" ),
-- Find position in list for each route
array_foreach( @transfers, array_find( @routes, @element ) ) ),

-- Get routes color value from array index
with_variable('fill',
aggregate(
layer:= 'Bus_routes',
aggregate:= 'array_agg',
expression:=  "color" ),

array_foreach( @index, array_get( @fill, @element ) ) ) ), @element ) ), ' ' )

EDIT

I partially succeeded using maps arrays. But there's something weird. If I enter this expression, I won't get the map array, just an empty value.

with_variable('transfers',string_to_array("transfers",','),

with_variable('routes',
aggregate(
layer:= 'Bus_routes',
aggregate:= 'array_agg',
expression:= '''' || "route" || '''' || ':' || '''' || "hex" || '''' ),

map( replace( replace( replace( trim(array_to_string( @routes ) ), ',', ''), '''''', ''':'''), ':', ',') )) )

But if I remove the map command and copy the result from the attributes table and pate it in the code and create a map over it, it works!

For example, this won't work because it gives an empty map result.

with_variable('transfers',string_to_array("transfers",','),

with_variable('routes',
aggregate(
layer:= 'Bus_routes',
aggregate:= 'array_agg',
expression:= '''' || "route" || '''' || ':' || '''' || "hex" || '''' ),

array_foreach( @transfers, map_get(
map ( replace( replace( replace( trim(array_to_string( @routes ) ), ',', ''), '''''', ''':'''), ':', ',') )
@element )  || ' ' || @element) ))

But when I take the values gotten from the replace command in the attributes table and apply a map array over, it will work! That's crazy.

with_variable('transfers',string_to_array("transfers",','),

with_variable('routes',
aggregate(
layer:= 'Bus_routes',
aggregate:= 'array_agg',
expression:= '''' || "route" || '''' || ':' || '''' || "hex" || '''' ),

array_foreach( @transfers, map_get(
map ( '1','#00A440','2','#EA001B','3','#006DB1','4','#FDCA00','5','#00B8E4' ),
@element )  || ' ' || @element) ))

Result: [ '#00A440 1', '#006DB1 3', '#FDCA00 4' ]

2
  • What is your data stored in, ie is your array some array format type or just a string - looks like a string? Can you add headers to your three data sample listed, matching your SQL.
    – Jakob
    Commented Jul 7, 2020 at 8:45
  • Stops are stored in GPKG and colors in a CSV. Currently in the result I get only the color code matching to each route number four in the array.
    – M-Rick
    Commented Jul 7, 2020 at 14:19

1 Answer 1

2

Actually it does work if I add manually the datas for the map array.

This is how the code should look like. This code will render HTML from the datas available in the database.

Instead of all the datas entry, I should be able to use this:

map( replace( replace( replace( trim(array_to_string( @routes ) ), ',', ''), '''''', ''':'''), ':', ',') )

The code I had to use:

with_variable('svg1',
'<svg width="24" height="24">',

with_variable('svg2',
'</svg>',

with_variable('fill1',
'<rect width="24" height="24" style="fill:#',

with_variable('fill2',
'";stroke-width:0;stroke:rgb(0,0,0)" />',

with_variable('text1',
'<text x="50%" y="55%" textLength="20" lengthAdjust="spacingAndGlyphs" dominant-baseline="middle" text-anchor="middle" fill="white" font-size="20" font-weight="bold" font-family="arial, sans-serif">',

with_variable('text2',
'</text>',

replace(

array_to_string(
with_variable('transfers',string_to_array("transfers",','),

with_variable('routes',
aggregate(
layer:= 'Lignes',
aggregate:= 'array_agg',
expression:= '''' || "route" || '''' || ':' || '''' || "hex" || '''' ),

array_foreach( @transfers, map_get(
map ( '1','#00A440','2','#EA001B','3','#006DB1','4','#FDCA00','5','#00B8E4','10','#FFFA00','11','#9C62A5','12','#7ED2EB','20','#FFFA00','21','#00B8E4','22','#9C62A5','23','#00B8E4','24','#AF8CBC','25','#FFFA00','26','#00A440','27','#7ED2EB','28','#7ED2EB','29','#F79EC2','30','#FFFA00','32','#F25B48','33','#F79EC2','36','#2BC2B6','37','#00AE38','39','#F79EC2','42','#ACDB00','47','#AF8CBC','48','#00AE38','50','#FFFA00','51','#00A440','52','#C89544','54','#ACDB00','56','#F79E00','58','#F25B48','59','#F79EC2','60','#FFFA00','66','#006DB1','67','#006DB1','68','#006DB1','69','#C89544','70','#FFFA00','71','#ACDB00' ),
@element )  || @fill2 || @text1 || @element || @text2 || @svg2 ) )), '\n' ), '#', @svg1 || @fill1 )))))) )

After several attempts with simple tests, it seems like map() is having a bug. It doesn't create a map array from calculated datas. I have reported the bug.

https://github.com/qgis/QGIS/issues/37658

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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