regenerate test data converting CDM to table driving website

cdm_test.csv

gfw2_forma_test

x,y,z,date_array,sd,se
50064,72705,17,{72},{72},{1}
50064,72706,17,{71},{71},{1}
25032,36353,16,{71},{71},{1}
25032,36352,16,{72},{72},{1}
12516,18176,15,"{71,72}","{71,72}","{1,1}"

gistfile1.sql

# insert z17 into test table

INSERT INTO gfw2_forma_test (x,y,date_array,z) 
  (SELECT x, y, array_agg(undate) as date_array, 17 as z 
  FROM (SELECT x::int, y::int, p::int AS undate FROM cdm_test) foo
  GROUP BY x,y);

# insert z16 into test table

INSERT INTO gfw2_forma_test (x,y,date_array,z)
  (SELECT x, y, array_agg(undate) AS date_array, 16 AS z 
  FROM (SELECT floor(x/2) AS x, floor(y/2) AS y, unnest(date_array) AS undate FROM gfw2_forma_test WHERE z = 17) foo
  GROUP BY x,y);

# update null se/sd

UPDATE gfw2_forma_test SET sd = ARRAY(SELECT DISTINCT UNNEST(date_array) ORDER BY 1);
UPDATE gfw2_forma_test SET se = ARRAY(SELECT count(*) FROM UNNEST(date_array) d GROUP BY d ORDER BY d);

# insert z15 into test table - gets us a multi-element array at z15

INSERT INTO gfw2_forma_test (x,y,date_array,z) 
  (SELECT x, y, array_agg(undate) AS date_array, 15 AS z
  FROM (SELECT floor(x/2) AS x, floor(y/2) AS y, unnest(date_array) AS undate FROM gfw2_forma_test WHERE z = 16) foo 
  GROUP BY x,y);

UPDATE gfw2_forma_test SET sd = ARRAY(SELECT DISTINCT UNNEST(date_array) ORDER BY 1);
UPDATE gfw2_forma_test SET se = ARRAY(SELECT count(*) FROM UNNEST(date_array) d GROUP BY d ORDER BY d);