-- Function: rasters.unique_tiles(character varying, character varying, character varying, character varying)

-- DROP FUNCTION rasters.unique_tiles(character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION rasters.unique_tiles(character varying, character varying, character varying, character varying)
  RETURNS text AS
$BODY$
DECLARE
	namespace alias for $1;
	tablename alias for $2;
	tableresult alias for $3;
	ts alias for $4; --tile size, format integerxinteger
	th int; --tile height
	tw int; --tile width
	ph float; --pixel height
	pw float; --pixel width
	bf float; --buffer factor
	nodataval float;
	query varchar;
	nrow int;
	ret varchar;
		
BEGIN
--variables values to be used in different functions below

query:='
select max(st_pixelheight(rast)) from '||namespace||'.'||tablename||';';
EXECUTE query  into ph;

query:='
select max(st_pixelwidth(rast)) from '||namespace||'.'||tablename||';';
EXECUTE query  into pw;

query := 'select substring('''||ts||''',1,(position(''x'' in '''||ts||''')-1));';
EXECUTE query  into tw;
query := 'select substring('''||ts||''', (position(''x'' in '''||ts||''')+1), char_length('''||ts||'''));';
EXECUTE query  into th;

if ph > pw then bf:=ph; else bf:=pw; end if;

query:='
select st_bandnodatavalue(rast) from '||namespace||'.'||tablename||' limit 1;';
EXECUTE query  into nodataval;

raise notice 'ph =% / pw = % / th = % / tw = % / bf = % / nodata = %',ph, pw, th, tw, bf, nodataval;

query:='
--polygon container of all original tiles

create table '||namespace||'.tmp_rast_bb as
select st_union(st_envelope(rast)) the_geom
from '||namespace||'.'||tablename||';';

EXECUTE query;

query :='
-- bouding box for this polygon

create table '||namespace||'.tmp_bb as
select 1::int gid, st_envelope(the_geom) the_geom from '||namespace||'.tmp_rast_bb;';

EXECUTE query;
 

query :='
-- new grid of squarred polygons

create table '||namespace||'.tmp_bb_grid as 
select 	('||namespace||'.makegrid(the_geom,'||pw||'*'||tw||','||ph||'*'||th||')).gid,
	('||namespace||'.makegrid(the_geom,'||pw||'*'||tw||','||ph||'*'||th||')).the_geom
from '||namespace||'.tmp_bb;

create index tmp_bb_grid_geom on '||namespace||'.tmp_bb_grid using gist(the_geom);';

EXECUTE query;

query :='
-- new tiles

create table '||namespace||'.tmp_grid as
select a.* from '||namespace||'.tmp_bb_grid a, '||namespace||'.tmp_rast_bb b
where st_intersects(a.the_geom, b.the_geom);

create index tmp_grid_geom on '||namespace||'.tmp_grid using gist(the_geom);';

EXECUTE query;

query :='
-- new table of slope tiles from raster dem

create table '||namespace||'.'||tableresult||' as
select gid rid,
st_union(
	st_clip(rast, the_geom, '||nodataval||')
) rast
from '||namespace||'.tmp_grid, '||namespace||'.'||tablename||'
where st_intersects(the_geom, rast)-- and st_isempty(st_clip(rast, the_geom, '||nodataval||'))=''f''
group by gid, the_geom;';

EXECUTE query;

GET DIAGNOSTICS nrow = ROW_COUNT;

query := '
delete from '||namespace||'.'||tableresult||'
where st_height(rast) = 0 or st_width(rast) = 0;';

EXECUTE query;
/*
query := '
create index '||tableresult||'_rast on '||namespace||'.'||tableresult||' using gist(st_convexhull(rast));';
EXECUTE query;
*/

query := '
drop table '||namespace||'.tmp_rast_bb;
drop table '||namespace||'.tmp_bb;
drop table '||namespace||'.tmp_bb_grid;
drop table '||namespace||'.tmp_grid;';

EXECUTE query;

ret := 'Table '||namespace||'.'||tableresult||': '||nrow||' created';
	
RETURN ret;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION rasters.unique_tiles(character varying, character varying, character varying, character varying)
  OWNER TO hug;
