revised GFW queries for UMD loss/gain data

gistfile1.sql

# global sum of loss and gain by year
# note that the gain is the same each year (it is total gain / 12), so if you want total gain
# you need to add up all the gain values, or multiply one of them by 12
SELECT year, 
       Sum(loss) loss, 
       Sum(gain) gain 
FROM   umd_nat 
WHERE  thresh = 10 
        AND year > 2000
GROUP  BY year 
ORDER  BY year 

---------------------------------------------------

# get top ten countries for loss
SELECT umd.iso, 
       c.name, 
       c.enabled, 
       Sum(umd.loss) loss 
FROM   umd_nat umd, 
       gfw2_countries c 
WHERE  thresh = 10 
       AND umd.iso = c.iso 
       AND NOT loss = 0 
       AND umd.year > 2000
GROUP  BY umd.iso, 
          c.name, 
          c.enabled 
ORDER  BY loss DESC 
LIMIT  10 

---------------------------------------------------

# get loss totals for a particular country
SELECT iso, 
       Sum(loss) loss, 
       Sum(gain) gain 
FROM   umd_nat 
WHERE  iso = 'RUS' 
       AND thresh = 10
       AND year > 2000
GROUP  BY iso 

---------------------------------------------------

# get loss percentage by year, along with gain
# note that the gain is the same each year (it is total gain / 12), so if you want total gain
# you need to add up all the gain values, or multiply one of them by 12.
# also note that extent_offset is used in order to calculate percent
# loss based on the extent for the previous year
SELECT year, 
       Sum(gain), 
       Sum(loss) / Sum(extent_offset)  ratio_loss
FROM   umd_nat 
WHERE  thresh = 10 
       AND year > 2000 
GROUP  BY year 

---------------------------------------------------

# this stays the same - loss relative to 2000 extent
WITH e AS
    (SELECT iso,
            extent
     FROM umd_nat
     WHERE YEAR = 2000
       AND thresh = 10)
  SELECT c.iso,
         c.name,
         c.enabled,
         p.perc ratio_loss
  FROM
    (SELECT umd.iso,
            sum(umd.loss) / avg(e.extent) perc
     FROM umd_nat umd,
          e
     WHERE umd.thresh = 10
       AND umd.iso = e.iso
       AND e.extent != 0
       AND umd.year > 2000
     GROUP BY umd.iso,
              e.iso
     ORDER BY perc DESC) p,
    gfw2_countries c WHERE p.iso = c.iso
  AND c.enabled IS TRUE
  AND NOT perc = 0
ORDER BY p.perc DESC LIMIT 10 

---------------------------------------------------

# loss percent for a given country, by year
SELECT year, 
       loss_perc 
FROM   umd_nat 
WHERE  thresh = 10 
       AND iso = 'BRA' 

---------------------------------------------------

# global loss and extent, by year 
SELECT year, 
       Sum(loss)   loss, 
       Sum(extent_offset) extent 
FROM   umd_nat 
WHERE  thresh = 10 
       AND year > 2000 
GROUP  BY year 
ORDER BY year

---------------------------------------------------

# THIS QUERY DOES NOT MAKE ANY SENSE
# it adds up the extent for each year into a total extent.
# if you had extents in 2000-3 as 100, 90, & 80 the total
# would be 270, which is is nonsense. I am not sure what this
# query does, but it should be replaced with something useful!

WITH umd AS 
( 
         SELECT   iso, 
                  Sum(extent) sum_extent 
         FROM     umd_nat 
         WHERE    thresh = 10 
         GROUP BY iso) 
SELECT   c.iso, 
         c.NAME, 
         c.enabled, 
         umd.sum_extent 
FROM     gfw2_countries c, 
         umd 
WHERE    c.iso = umd.iso 
AND      NOT umd.sum_extent = 0 
ORDER BY umd.sum_extent DESC limit 10

---------------------------------------------------

# get loss and extent by year for a particular country
SELECT year, 
       loss, 
       extent_offset extent 
FROM   umd_nat 
WHERE  thresh = 25 
       AND iso = 'RUS' 
       AND year > 2000 

---------------------------------------------------

# get loss/gain ratio for top 50 countries ordered by total loss
# it is unclear what the 2012 extent is good for, but it appears
# in the original query
WITH e AS 
( 
       SELECT iso, 
              extent 
       FROM   umd_nat 
       WHERE  thresh = 10 
       AND    year = 2012), u AS 
( 
         SELECT   iso, 
                  Sum(loss) sum_loss, 
                  Sum(gain) sum_gain 
         FROM     umd_nat 
         WHERE    thresh = 10 
         GROUP BY iso) 
SELECT   c.iso, 
         c.NAME, 
         c.enabled, 
         u.sum_loss, 
         u.sum_gain, 
         u.sum_loss / u.sum_gain ratio, 
         e.extent 
FROM     gfw2_countries c, 
         u, 
         e 
WHERE    u.sum_gain IS NOT NULL 
AND      NOT u.sum_gain = 0 
AND      c.iso = u.iso 
AND      e.iso = u.iso 
ORDER BY u.sum_loss DESC limit 50

---------------------------------------------------

# this one is very similar to the last one - it's actually
# a subset of the previous query.
WITH u AS 
( 
         SELECT   iso, 
                  Sum(loss) sum_loss, 
                  Sum(gain) sum_gain 
         FROM     umd_nat 
         WHERE    thresh = 10 
         GROUP BY iso), ratio AS 
( 
         SELECT   c.iso, 
                  c.NAME, 
                  c.enabled, 
                  u.sum_loss / u.sum_gain ratio 
         FROM     gfw2_countries c, 
                  u 
         WHERE    u.sum_gain IS NOT NULL 
         AND      NOT u.sum_gain = 0 
         AND      c.iso = u.iso 
         ORDER BY u.sum_loss DESC limit 50) 
SELECT   * 
FROM     ratio 
WHERE    ratio IS NOT NULL 
ORDER BY ratio DESC limit 10

---------------------------------------------------

# cannot be revised - no info on climate domains by threshold
SELECT name,
       y2001,
       y2002,
       y2003,
       y2004,
       y2005,
       y2006,
       y2007,
       y2008,
       y2009,
       y2010,
       y2011,
       y2012,
       GREATEST(y2001, y2002, y2003, y2004, y2005, y2006, y2007, y2008, y2009, y2010, y2011, y2012) AS MAX
FROM countries_domains 
 
 ---------------------------------------------------

# cannot be revised - no info on climate domains by threshold
SELECT name,
       total_loss,
       total_gain,
       GREATEST(y2001, y2002, y2003, y2004, y2005, y2006, y2007, y2008, y2009, y2010, y2011, y2012) AS MAX
FROM countries_domains
ORDER BY total_loss DESC