0

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),
   ()
  );
  • 2
    sum(st_length(geometry)) – Kazuhito Aug 3 at 4:00
4

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

3

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;
  • 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 – GeorgeC Aug 3 at 4:31
  • 1
    ohh my bad, I have edited my answer hope it works now. – Asad Abbas Aug 3 at 4:46

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.