0

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.

3
  • 1
    This is more a SQL issue than a GIS one. Can't you create a lookup table with which to join your existing table, so that a GROUP BY will function?
    – Vince
    Jun 2, 2014 at 14:28
  • Yes, I have already thought about it BUT what would be the column to join with my main table ?
    – wiltomap
    Jun 2, 2014 at 14:31
  • 1
    Year, with a compound test for min_year/max_year in the lookup.
    – Vince
    Jun 2, 2014 at 14:35

2 Answers 2

4

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
0

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 beginning and end years in additional columns.

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.