private_table_torque.sql

-- Type for normal response
CREATE TYPE axh_torque_cell AS (
	x float8,
	y float8,
	vals bigint[],
	dates bigint[]
)

-- Function for normal request
CREATE OR REPLACE FUNCTION AXH_PrivateTorqueRequest(table_name TEXT, column_name TEXT, x INT, y INT, zoom INT, resolution NUMERIC, step BIGINT, countby TEXT, min_date BIGINT)
	RETURNS SETOF axh_torque_cell
	AS $$
	DECLARE
	  sql text;
	BEGIN
	IF zoom > 18 THEN 
		RAISE EXCEPTION 'Invalid call to Zoom too large';
        END IF;
	sql := 'SELECT 
			st_x(g) x, 
			st_y(g) y, 
			array_agg(c::bigint) vals, 
			array_agg(d::bigint) dates 
			FROM ( 
				SELECT 
				ST_SnapToGrid(i.the_geom_webmercator, CDB_XYZ_Resolution($5) * $6) g, 
				'||$8||' c, 
				floor((date_part(''epoch'','||$2||')-$9)/$7) d 
				FROM '||$1||' i 
				WHERE i.the_geom_webmercator && CDB_XYZ_Extent($3,$4,$5) 
				GROUP BY g, d 
			) cte
			GROUP BY x,y';
    RETURN QUERY EXECUTE sql USING table_name, column_name, x, y, zoom, resolution, step, countby, min_date;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION AXH_PrivateTorqueRequest(TEXT, TEXT, INT, INT, INT, NUMERIC, BIGINT, TEXT, BIGINT) TO publicuser;

-- Type for cummulative response
CREATE TYPE axh_torque_cell_cumm AS (
	x float8,
	y float8,
	vals bigint[],
	dates bigint[],
	dates_end bigint[]
)

-- Function for cummulative request
CREATE OR REPLACE FUNCTION AXH_PrivateTorqueRequest(table_name TEXT, column_name TEXT, x INT, y INT, zoom INT, resolution NUMERIC, step BIGINT, countby TEXT, min_date BIGINT, expir_conv TEXT)
	RETURNS SETOF axh_torque_cell_cumm
	AS $$
	DECLARE
	  sql text;
	BEGIN
	IF zoom > 18 THEN 
		RAISE EXCEPTION 'Invalid call to Zoom too large';
        END IF;
	sql := 'SELECT 
			st_x(g) x, 
			st_y(g) y, 
			array_agg(c::bigint) vals, 
			array_agg(d::bigint) dates,
			array_agg(de::bigint) dates_end
			FROM ( 
				SELECT 
				ST_SnapToGrid(i.the_geom_webmercator, CDB_XYZ_Resolution($5) * $6) g, 
				'||$8||' c, 
				floor((date_part(''epoch'','||$2||')-$9)/$7) d 
				floor((date_part(''epoch'','||$10||')-$9)/$7) d 
				FROM '||$1||' i 
				WHERE i.the_geom_webmercator && CDB_XYZ_Extent($3,$4,$5) 
				GROUP BY g, d, de
			) cte
			GROUP BY x,y';
    RETURN QUERY EXECUTE sql USING table_name, column_name, x, y, zoom, resolution, step, countby, min_date;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION AXH_PrivateTorqueRequest(TEXT, TEXT, INT, INT, INT, NUMERIC, BIGINT, TEXT, BIGINT, TEXT) TO publicuser;

-- Type for metadata request
CREATE TYPE axh_torque_meta AS (
	xmax float8,
	ymax float8,
	xmin float8,
	ymin float8,
	max float8,
	min float8
)

-- Function for metadata request
CREATE OR REPLACE FUNCTION AXH_PrivateTorqueMetadata(table_name TEXT, column_name TEXT, is_time BOOLEAN)
	RETURNS SETOF axh_torque_meta
	AS $$
	DECLARE
	  sql text;
	BEGIN
	IF is_time THEN
		sql := 'SELECT st_xmax(st_envelope(st_collect(the_geom))) xmax,st_ymax(st_envelope(st_collect(the_geom))) ymax, st_xmin(st_envelope(st_collect(the_geom))) xmin, st_ymin(st_envelope(st_collect(the_geom))) ymin, date_part(''epoch'',max('||$2||')) max, date_part(''epoch'',min('||$2||')) min FROM '||$1||' WHERE the_geom IS NOT NULL';
	ELSE
		sql := 'SELECT st_xmax(st_envelope(st_collect(the_geom))) xmax,st_ymax(st_envelope(st_collect(the_geom))) ymax, st_xmin(st_envelope(st_collect(the_geom))) xmin, st_ymin(st_envelope(st_collect(the_geom))) ymin, max('||$2||') max, min('||$2||') min FROM '||$1||' WHERE the_geom IS NOT NULL';
	END IF;
  	RAISE DEBUG '%', sql;
    RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION AXH_PrivateTorqueMetadata(TEXT, TEXT, BOOLEAN) TO publicuser;