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

  • 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 '14 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 '14 at 14:31
  • 1
    Year, with a compound test for min_year/max_year in the lookup. – Vince Jun 2 '14 at 14:35

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

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 !

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.