Creating bar chart from point layer using QGIS Geometry Generator

Question

From a layer with point geometry, I store the following fields:

"CATEG" which can be A or B
"ORDER" which is an autonumeric field sequence without repetitions
"VALUE" which is a value

Points are distributed/digitalized in the space according to where the phenomenon occurs. The attribute table design is shown in the following screenshot:

My goal is to transform/translate this point cloud layer into a pseudo-bar chart. The points should be translated in a straight line that would be the X axis with the values from the minimum value of the "ORDER" field (1) to the maximum value of the "ORDER" field (12).

Then I would like to draw an arrow representing the value of the "VALUE" field.

Here is a screenshot made manually with the idea I'm looking for:

The solution presented by user Babel is exactly what I was looking for. I am speechless!

To refine the solution a little more. I am trying to color the arrows with the field "CATEG" which is a string, and I use the following expression, but I can't get it to work:

CASE
  WHEN "CATEG" = 'A' THEN '#b2f0cf'
  WHEN "CATEG" = 'B' THEN '#46b2fa'
  ELSE '#000000'
END

I have also tried to create a numeric field (with values 1 and 2) and use the Categoritzed symbol renderer and I have not been able to color either:

I can't manage the arrow colors using "CATEG" field. I don't know what I'm doing wrong. I add an animated screenshot showing my workflow. Could you help me to find out what I am doing wrong?

Will a standard QGIS's Bar plot tool satisfy your demand? It has both category and value fields. — Taras, Nov 26 '23 at 17:49
I have tried the Bar plot algorithm from the Processing toolbox, and also the Data Plotly plugin. Both options allow to achieve the objective. But in my project they have the problem that you have to pre-generate the calculation and also generates an HTML file that is not very suitable to integrate in my print layout. My main requirement is that the transformation can be done on-the-fly with the geometry generator and that the translation in straight line is done in the same space where there are the points — Ingrid Ingravida, Nov 26 '23 at 18:08
Just interesting, when you project your all points on the X-axis, how should they be placed, with a standard increment e.g. 50m, or appropriated to old projected locations, but swapped according to the "ORDER" field ? — Taras, Nov 26 '23 at 18:29
Is a combination of QGIS and Excel using PowerQuery an option? — Bernd Loigge, Nov 26 '23 at 18:34
Have you tried using symbology and the geometry generator/make_point()? — Erik, Nov 26 '23 at 18:40
Taras, the idea would be, for example, to have an X-axis of fixed length and that the values (1 to 12, in this case) are distributed equidistantly. The option you propose (fixed increment of 50m) would also be good. — Ingrid Ingravida, Nov 26 '23 at 18:44
Bernd, Yes, it is a good idea this combination, but as in the case of Data Plotly and Bar chart, my requirement is that the transformation can be done on-the-fly, so that it can be easily integrated in my print layout — Ingrid Ingravida, Nov 26 '23 at 18:47
Erik, not yet. I'm still stuck on what approach would be the most appropriate to take — Ingrid Ingravida, Nov 26 '23 at 18:49
If anyone interested - I have updated my answer with a solution using Chart.JS in an HTML frame within the print composer. — Bernd Loigge, Nov 28 '23 at 11:19

Taras · Answer 1 · 2023-11-27T11:56:50.353

Let's assume there is an input point layer called 'points_in_polygon' with its attribute table, see the image below.

Attributes Imitatation :

"city" → "CATEG"
"month" → "ORDER"
"temp" → "VALUE"

Solution 1 : Single Symbol

Stage 1 : Geometry Generator

Geometry Type : Point/MultiPoint

Expression Dialog:

geometry_n(
    order_parts(
        nodes_to_points(
            densify_by_count(
                make_line(
                    make_point(
                            x_min(layer_property(@layer, 'extent')),
                            y_min(layer_property(@layer, 'extent'))
                            ),
                    make_point(
                            x_max(layer_property(@layer, 'extent')),
                            y_min(layer_property(@layer, 'extent'))
                            )
                    ),
                (layer_property(@layer, 'feature_count') - 2)
                )
            ),
        orderby:="month"
        ),
    array_find(array_agg($id, order_by:="month"), $id) + 1
    )

Stage 2 : Filled Marker

Type : Arrow marker
Size : Expression String Builder

scale_linear("temp", 0, 10, minimum("temp"), maximum("temp"))

Stage 3 : Simple fill

Color : Expression String Builder

case
    when attribute('city') = 'PARIS' then '#fb5607'
    when attribute('city') = 'LONDON' then '#8338ec'
    else '#344e41'
end

And get the following result:

Solution 2 : Single Symbol

Stage 1 : Geometry Generator

Geometry Type : LineString/MultiLineString

Expression Dialog:

make_line(
    geometry_n(
            order_parts(
                nodes_to_points(
                    densify_by_count(
                        make_line(
                            make_point(
                                    x_min(layer_property(@layer, 'extent')),
                                    y_min(layer_property(@layer, 'extent'))
                                    ),
                            make_point(
                                    x_max(layer_property(@layer, 'extent')),
                                    y_min(layer_property(@layer, 'extent'))
                                    )
                            )
                        ,
                        (layer_property(@layer, 'feature_count') - 2)
                        )
                    ),
                orderby:="month"
                ),
            array_find(array_agg($id, order_by:="month"), $id) + 1),
    project(
        geometry_n(
            order_parts(
                nodes_to_points(
                    densify_by_count(
                        make_line(
                            make_point(
                                    x_min(layer_property(@layer, 'extent')),
                                    y_min(layer_property(@layer, 'extent'))
                                    ),
                            make_point(
                                    x_max(layer_property(@layer, 'extent')),
                                    y_min(layer_property(@layer, 'extent'))
                                    )
                            )
                        ,
                        (layer_property(@layer, 'feature_count') - 2)
                        )
                    ),
                orderby:="month"
                ),
            array_find(array_agg($id, order_by:="month"), $id) + 1),
        "temp",
        0
        )
    )

Stage 2 : Simple Line

Color : Expression String Builder

case
    when attribute('city') = 'PARIS' then '#fb5607'
    when attribute('city') = 'LONDON' then '#8338ec'
    else '#344e41'
end

And get the following result:

P.S. LineString object can be converted into Arrow.

Both solutions possess the same label configurations:

Value :

'id: ' || "id" || 
'\n' ||
'city: ' ||"city" || 
'\n' ||
'month: ' || "month" || 
'\n' ||
'temp: ' || "temp"

Mode : Offset from Point
Quadrant : Below
Offset Y : 3,00 mm
Placement : Geometry Generator

geometry_n(
    order_parts(
        nodes_to_points(
            densify_by_count(
                make_line(
                    make_point(
                            x_min(layer_property(@layer, 'extent')),
                            y_min(layer_property(@layer, 'extent'))
                            ),
                    make_point(
                            x_max(layer_property(@layer, 'extent')),
                            y_min(layer_property(@layer, 'extent'))
                            )
                    )
                ,
                (layer_property(@layer, 'feature_count') - 2)
                )
            ),
        orderby:="month"
        ),
    array_find(array_agg($id, order_by:="month"), $id) + 1)

Geometry Type : Point/MultiPoint

References:

+1. However, I'm not sure if the size of the arrow marker reliably reflects proportionally attribute vales in the style of a bar chart. Temp= 9 and Temp=29 look almost the same size. As well, height and width are scaled based on the attribute value and this is difficult to interpret. — Babel, Nov 26 '23 at 21:25
You are right! One can always adjust that thing, it is simply a matter of time to find that balance :) — Taras, Nov 26 '23 at 21:35
The SVG symbology can be also used, it is pretty useful because it has width and height at the same time, not only the size — Taras, Nov 26 '23 at 21:36
Both Taras' first and second solutions work perfectly. Could you share the Label Placement expression for Solution 2 please? — Ingrid Ingravida, Nov 27 '23 at 11:45

Babel · Accepted Answer · 2023-11-27T21:33:42.573

Use the following expression in Geoemtry Generator, style the resulting line as arrow and assign the color corresponding to the category values (see below for that). The expression creates a bar chart, ordered based on the field order, with the length of the field value and using the extent of the point layer.

This works for whatever CRS and layer extent you use - it will automatically adapt: if you add/delete or move points or if you change attribute values, the bars will change accordingly.

Initial points (yellow) and bar chart, created inside the extent of the point layer (black dotted line) and based on the order and value attributes of the points; bars/arrow heads are again labeled in the corresponding color from attribute categ:

with_variable(
    'ext',
    layer_property( @layer, 'extent'),
with_variable(
    'ln',
    translate (
        make_point (x_min(@ext),y_min(@ext)),
        (order-0.5) * (x_max(@ext)-x_min(@ext)) / count ($id),
        0
    ),
make_line (
    @ln,
    project(
        @ln,
        attribute (get_feature(@layer, 'order', "order"),'value') * (y_max(@ext)-y_min(@ext)) / maximum(value),
        0
    )
)))

Details for styling

To color the bars depending on their categ values, use this expression:

case
when categ = 'A' then 'red'
else 'blue'
end

The extent of the point layer (black dotted line on my screenshot) is easy to create with Geometry Generator: layer_property (@layer, 'extent')

To place the label at the top of the bar (arrow head), in the label placement tab, activate Geoemtry Generator and paste the following expression (based on the one above to create the bars): it gets the end-point of the bar-lines. Then set placement mode to Offset from Point and the Quadrant to the center top:

with_variable(
    'ext',
    layer_property( @layer, 'extent'),
with_variable(
    'ln',
    translate (
            make_point (x_min(@ext),y_min(@ext)),
            (order-0.5) * (x_max(@ext)-x_min(@ext)) / count ($id),
            0
        ),
end_point (
    make_line (
        @ln,
        project(
            @ln,
            value * (y_max(@ext)-y_min(@ext)) /maximum(value),
            0
        )
    )
)))

I can't manage the symbology arrows by colors according to the "CATEG" field. I add an animated screenshot showing my workflow. Could you help me on the steps to follow to categorize the arrows by color? — Ingrid Ingravida, Nov 26 '23 at 22:23
I realize it works your expression for solution color categories. However, in my project I cannot use it. It is unviable because I have more than 100 features — Ingrid Ingravida, Nov 26 '23 at 22:46
Sorry, my expression was a bit too complex for the task... I found an easier version that has the big advantage that it is very easy to color the bars as you want. See updated answer. Sorry for the initial confusion! — Babel, Nov 27 '23 at 01:32
Thank you. Now, this expression works. If possible, I would like to ask for two more things, if you could share:

The expression to draw the extent of the point layer (black dotted line)

The expression to control the placement of the label above the arrow — Ingrid Ingravida, Nov 27 '23 at 07:35
I added this at the end of my solution and I revised the initial expression to generate the bars once again to make it shorter. — Babel, Nov 27 '23 at 08:23

Bernd Loigge · Answer 3 · 2023-11-28T11:47:54.363

Solution using Excel

Using PowerQuery within Excel (only works for Office on Windows) you can access your local data (GeoJSON for example) or even remote data (PostGres DB etc.). So after setting your workflow up you just need to press refresh.

Store your data as GeoJSON
Load your data within Excel using PowerQuery (Data --> Get Data --> From File --> JSON). Now the GeoJSON is loaded but needs to be extracted.

Extract features --> Click on List next to features

Now we need to convert the list into a column of records by clicking Convert to Table

We only want the properties so we uncheck the geometry and type column.

By clicking the two arrays in the column header the properties get exploded.
Finally we change the data-types of the ORDER and VALUE column to decimal.

Now the data can be loaded into an Excel Table.
Create a pivot stacked bar-chart from your data where the ORDER column is set as the row, the CATEG column is set as column and VALUE is set as values.

Store your chart as a png after each update.

Now you only need to click refresh in Excel and update the png. The png can be imported as an image to your print report.

Here is a sample PowerQuery (M formula language) Code. You can import it as an empty query. Just replace the GeoJSON Path

let
    Quelle = Json.Document(File.Contents(<GeoJSON Path>)),
    features = Quelle[features],
    #"In Tabelle konvertiert" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"properties"}, {"properties"}),
    #"Erweiterte properties" = Table.ExpandRecordColumn(#"Erweiterte Column1", "properties", {"CATEG", "ORDER", "VALUE"}, {"CATEG", "ORDER", "VALUE"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte properties",{{"ORDER", type number}, {"VALUE", type number}})
in
    #"Geänderter Typ"

Solution using Chart.JS within Print Layout

Another option would be to use Chart.JS library within an HTML frame in the printer composer. Using the aggregate expression like [[%aggregate( layer:='data',aggregate:='concatenate',expression:=if(CATEG='A', VALUE||'', '0'), concatenator:=',')%]] you can directly create the needed JavaScript config for a Chart.JS stacked bar chart.

<html>
   <head>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.3/Chart.bundle.min.js" integrity="sha512-vBmx0N/uQOXznm/Nbkp7h0P1RfLSj0HQrFSzV8m7rOGyj30fYAOKHYvCNez+yM8IrfnW0TCodDEjRqf6fodf/Q==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
      <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.3/Chart.css" integrity="sha512-SUJFImtiT87gVCOXl3aGC00zfDl6ggYAw5+oheJvRJ8KBXZrr/TMISSdVJ5bBarbQDRC2pR5Kto3xTR0kpZInA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
   </head>
   <body>
      <div>
         <canvas id="myChart"></canvas>
      </div>
      <script>
         const ctx = document.getElementById('myChart');
     new Chart(ctx, {
       type: 'bar',
     data: {
     labels: [[%aggregate( layer:='data',aggregate:='concatenate',expression:=ORDER||'', concatenator:=',')%]],
     datasets: [
        {
            label: &quot;Category A&quot;,
            data: [[%aggregate( layer:='data',aggregate:='concatenate',expression:=if(CATEG='A', VALUE||'', '0'), concatenator:=',')%]],
            borderWidth: 1,
            backgroundColor: '#67c1e9'
        },
        {
            label: &quot;Category B&quot;,
            data: [[%aggregate( layer:='data',aggregate:='concatenate',expression:=if(CATEG='B', VALUE||'', '0'), concatenator:=',')%]],
            borderWidth: 1,
            backgroundColor: '#ca6fb3'
        }
     ]
     },
     options: {
     animation: {
        duration: 0
     },
     scales: {
               yAxes: [
            {
                stacked: true,
                ticks: {
                    beginAtZero: true
                }
            }
        ],
        xAxes: [
            {
                stacked: true,              
            }
        ]
     }
     }
     });
  &lt;/script&gt;

</body>
</html>

Creating bar chart from point layer using QGIS Geometry Generator

3 Answers3

Solution 1 : Single Symbol

Stage 1 : Geometry Generator

Stage 2 : Filled Marker

Stage 3 : Simple fill

Solution 2 : Single Symbol

Stage 1 : Geometry Generator

Stage 2 : Simple Line

Details for styling

Solution using Excel

Solution using Chart.JS within Print Layout

Linked