PostgreSQL/PostGIS - group by series of values

Question

I need to sum up linear objects' length and to group them by intervals of several years. I have one table storing my objects like this :

- gid serial NOT NULL, 
- year INTEGER, 
- the_geom geometry(MULTILINESTRING) ;

I need a result like this :

period          | length
----------------+-----------
 2005 - 2014    | 18.6
 1995 - 2004    | 16.1
 1985 - 1994    | 7.6
 1975 - 1984    | 19.0
 1965 - 1974    | 28.2
 1945 - 1964    | 10.2
 before 1945    | 0.1 

I can't find out on the Web how to perform this, except by producing each line with a different query and to use UNION ALL to merge them together, which is not so good...

Many thanks for help !

wiltomap

Answer 1

Use a CASE statement and a sub-query:

SELECT foo.category, SUM(ST_Length(foo.the_geom)) as length FROM 
(SELECT gid, year, the_geom, 
    (CASE
        WHEN year BETWEEN 2005 AND 2014 THEN "1";
        WHEN year BETWEEN 1995 AND 2004 THEN "2";
        WHEN year BETWEEN 1985 AND 1994 THEN "3";
        WHEN year BETWEEN 1975 AND 1984 THEN "4";
        ...
        ELSE "9";
    END CASE) as category 
FROM sometable WHERE gid IS NOT NULL) as foo
GROUP BY foo.category

Answer 2

The solution I have found here : https://stackoverflow.com/questions/23997044/group-by-intervals-of-integer-values/24003629#24003629. Using an external table storing periods with begining and end years in additional columns.

Thanks to all !