Get line length using SQL in QGIS

Question

I have a line dataset that has road names and different surface types along different segments of the road. I want to calculate the total length of each surface type for each road.

I've been able to get the groups and counts as I need using virtual layers / SQL

Select Road, SURF_TYPE, count(SURF_TYPE), geometry from road_extract group by Road,SURF_TYPE

Get's me

enter image description here

Rather than geometry I want to get the total length of each surface type.

I've found one workaround where if I have a column with $length in the the source table I can include sum(length) in the SQL to get the total length for each surface type but is it possible without having this column?

Also is it possible to get this table formatted with the just the lengths like...

Road, Sealed, Unsealed, Floodway, Total
Airport, 15 , 0 , 0 , 15
Balonne, 20 , 5 , 0 , 25
Burke, 40 , 20 , 2 , 62

I have tried the following with no luck

select
 Road, SURF_TYPE,
 sum(length)
from
 road_extract
 Group by
 Grouping sets (
 (Road, SURF_TYPE, sum(length)),
 (Road),
 (SURF_TYPE),
 ()
 );

• Kazuhito: sum(st_length(geometry))

Answer 1

SQL is not so good at pivoting row values into columns. If you know the column names you want you can write a very specific SQL statement, like this one:

SELECT Road, Sum(case when SURF_TYPE ='Sealed' Then st_length(geometry) Else 0 End) As Sealed, Sum(case when SURF_TYPE ='Unsealed' Then st_length(geometry) Else 0 End) As Unsealed, Sum(case when SURF_TYPE ='Floodway' Then st_length(geometry) Else 0 End) As Floodway, Sum(st_length(geometry)) as Total, Count(*) as SegmentCount FROM road_extract GROUP BY Road

Answer 2

You can try PostGIS st_length function and then pass geometry to it I have added SQL snippet below for your reference

select
Road, SURF_TYPE,
sum(st_length(geometry))
from
road_extract
Group by
Road,SURF_TYPE;

• GeorgeC: thanks but I get an error - Query execution error on CREATE TEMP VIEW _tview AS select Road, SURF_TYPE, sum(st_length(geometry)) from road_extract Group by Grouping sets ( (Road, SURF_TYPE, sum(length)), (Road), (SURF_TYPE));: 1 - near "sets": syntax error
• Asad Abbas: ohh my bad, I have edited my answer hope it works now.