-- Aggregate: concat

-- DROP AGGREGATE concat(text);

CREATE AGGREGATE concat(
  BASETYPE=text,
  SFUNC=textcat,
  STYPE=text
);

-- Function: get_c_square_polygon("varchar", float4)

-- DROP FUNCTION get_c_square_polygon("varchar", float4);

CREATE OR REPLACE FUNCTION get_c_square_polygon("varchar", float4)
  RETURNS text AS
$BODY$
DECLARE

	csq_string_param alias for $1;
	resolution alias for $2;
	polygon varchar(300);
	  N_limit varchar(60);
	  S_limit varchar(60);
	  W_limit varchar(60);
	  E_limit varchar(60);
	  center_lat numeric;
	  center_long numeric;
	  buffer numeric;

BEGIN
	if resolution = 0.1 then
		buffer=0.05;
		center_lat = ((substr(csq_string_param,2,1)::int4*10)+substr(csq_string_param,7,1)::int4+(substr(csq_string_param,11,1)::int4*0.1)+buffer)*((ROUND(ABS(substr(csq_string_param,1,1)::int4-4)*2,-1)/5)-1)::float4;
		center_long = ((substr(csq_string_param,3,2)::int4*10)+substr(csq_string_param,8,1)::int4+(substr(csq_string_param,12,1)::int4*0.1)+buffer)*(((2*(round(substr(csq_string_param,1,1)::int4,-1)/10))-1)*-1)::float4;
	elsif resolution = 0.5 then
		buffer=0.25;
		center_lat = ((substr(csq_string_param,2,1)::int4*10)+substr(csq_string_param,7,1)::int4+(substr(csq_string_param,11,1)::int4*0.1)+buffer)*((ROUND(ABS(substr(csq_string_param,1,1)::int4-4)*2,-1)/5)-1)::float4;
		center_long = ((substr(csq_string_param,3,2)::int4*10)+substr(csq_string_param,8,1)::int4+(substr(csq_string_param,12,1)::int4*0.1)+buffer)*(((2*(round(substr(csq_string_param,1,1)::int4,-1)/10))-1)*-1)::float4;
	elsif resolution = 1 then
		buffer=0.5;
		center_lat = ((substr(csq_string_param,2,1)::int4*10)+substr(csq_string_param,7,1)::int4+(substr(csq_string_param,11,1)::int4*0.1)+buffer)*((ROUND(ABS(substr(csq_string_param,1,1)::int4-4)*2,-1)/5)-1)::float4;
		center_long = ((substr(csq_string_param,3,2)::int4*10)+substr(csq_string_param,8,1)::int4+(substr(csq_string_param,12,1)::int4*0.1)+buffer)*(((2*(round(substr(csq_string_param,1,1)::int4,-1)/10))-1)*-1)::float4;
	end if;
	

        N_limit =  (center_lat + buffer)::text;
	S_limit = (center_lat - buffer)::text;
	W_limit =  (center_long- buffer)::text;
	E_limit = (center_long + buffer)::text;

	polygon =  'POLYGON((' || W_limit::text || ' ' || N_limit::text ||',' ||E_limit::text || ' ' || N_limit::text || ','|| E_limit::text || ' ' || S_limit::text || ','  || W_limit::text || ' ' || S_limit::text ||  ',' || W_limit::text || ' ' ||N_limit::text ||'))';

	return polygon;

END;

$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;


-- Function: get_csquare(float8, float8, float4)

-- DROP FUNCTION get_csquare(float8, float8, float4);

CREATE OR REPLACE FUNCTION get_csquare(float8, float8, float4)
  RETURNS text AS
$BODY$
DECLARE
	nlat alias for $1; 
	nlong alias for $2;
	resolution alias for $3;
	code_char1 varchar;
    code_char2 varchar(15);
    code_chars34 varchar(15);
    code_char5 varchar(15);
    code_char6 varchar(15);
    code_char7 varchar(15);
    code_char8 varchar(15);
    code_char9 varchar(15);
    code_char10 varchar(15);
    lat_remainder float8;
    long_remainder float8; 

	--myrec RECORD;
	--okay boolean;
	--real_schema name;
BEGIN

     --get the global quadrant
    if nlat>=0 then
      if nlong>=0 then
        code_char1 ='1';
      elsif nlong<0 then
        code_char1 ='7';
      end if;  
    elsif nlat<0 then
      if nlong>=0 then
        code_char1 ='3';
      elsif nlong<0 then
        code_char1 ='5';
      end if;  
    end if;
    
     --get the next digit (tens of degrees latitude)
    if nlat not in (90, -90) then     
      code_char2 = to_char(trunc(abs(nlat/10)),'99');
      lat_remainder = abs(nlat) - (to_number(code_char2,'99999999.9999999')*10);
      
    else     
         --special case for +90, -90 degrees
      code_char2 = '8';
      lat_remainder = 9.99999;
    end if;
    
    --get the next 2 digits (tens of degrees longitude)
     
    if nlong not in (180, -180) then     
      --code_chars34 = substr('00'||to_char(trunc(abs(nlong/10)),'999999'), -2);
         if abs(nlong/10) >=10 then
               code_chars34 = substr(trim(to_char(trunc(abs(nlong/10)),'99')), -2);
         else
	       code_chars34 = substr('0'||trim(to_char(trunc(abs(nlong/10)),'99')), -2);
         end if;
      long_remainder = abs(nlong) - (to_number(code_chars34,'99999999.9999999')*10);
    else     
         --special case for +180, -180 degrees
      code_chars34 = '17';
      long_remainder = 9.99999;
    end if;

   if resolution <10 then
    
       --get the 6th digit (single degrees latitude)
      code_char6 = to_char(trunc(lat_remainder),'9');
      lat_remainder = lat_remainder - to_number(code_char6,'99999999.9999999');
    
       --get the 7th digit (single degrees longitude)
      code_char7 = to_char(trunc(long_remainder),'9');
      long_remainder = long_remainder - to_number(code_char7,'99999999.9999999');


       --get the 5th digit (5-degree quadrant)
      if (to_number(code_char6,'99999999.9999999') between 0 and 4) and (to_number(code_char7,'99999999.9999999') between 0 and 4) then
        code_char5 = '1';
      elsif (to_number(code_char6,'99999999.9999999') between 0 and 4) and (to_number(code_char7,'99999999.9999999') between 5 and 9) then
        code_char5 = '2';
      elsif (to_number(code_char6,'99999999.9999999') between 5 and 9) and (to_number(code_char7,'99999999.9999999') between 0 and 4) then
        code_char5 = '3';
      elsif (to_number(code_char6,'99999999.9999999') between 5 and 9) and (to_number(code_char7,'99999999.9999999') between 5 and 9) then
        code_char5 = '4';
      end if;
      
      if resolution <1 then
        
         --get the 9th digit (tenths of degrees latitude)
        code_char9 = trim(to_char(trunc(lat_remainder*10),'9'));
        lat_remainder = lat_remainder - to_number((code_char9::float4/10),'99999999.9999999');
     
         --get the 10th digit (tenths of degrees longitude)
        code_char10 = trim(to_char(trunc(long_remainder*10),'9'));
        long_remainder = long_remainder - to_number((code_char10::float4/10),'99999999.9999999');

         --get the 8th digit (0.5-degree quadrant)
        if (to_number(code_char9,'99999999.9999999') between 0 and 4) and (to_number(code_char10,'99999999.9999999') between 0 and 4) then
          code_char8 = '1';
        elsif (to_number(code_char9,'99999999.9999999') between 0 and 4) and (to_number(code_char10,'99999999.9999999') between 5 and 9) then
          code_char8 = '2';
        elsif (to_number(code_char9,'99999999.9999999') between 5 and 9) and (to_number(code_char10,'99999999.9999999') between 0 and 4) then
          code_char8 = '3';
        elsif (to_number(code_char9,'99999999.9999999') between 5 and 9) and (to_number(code_char10,'99999999.9999999') between 5 and 9) then
          code_char8 = '4';
        end if;
      
      end if;
      
    end if;  
      
    if resolution = 10::float4  then      -- 10 deg. resolution, e.g. Hobart: "3414"
      return trim(code_char1)||trim(code_char2)||trim(code_chars34);
      
    elsif resolution = 5::float4  then      -- 5 deg. resolution, e.g. Hobart: "3414:2"
      return trim(code_char1)||trim(code_char2)||trim(code_chars34)||':'||trim(code_char5);
      
    elsif resolution = 1::float4  then      -- 1 deg. resolution, e.g. Hobart: "3414:227"
         return trim(code_char1)||trim(code_char2)||trim(code_chars34)||':'||trim(code_char5)||trim(code_char6)||trim(code_char7);

    elsif resolution = 0.5::float4  then      -- 0.5 deg. resolution, e.g. Hobart: "3414:227:3"
      return trim(code_char1)||trim(code_char2)||trim(code_chars34)||':'||trim(code_char5)||trim(code_char6)||trim(code_char7)||
        ':'||trim(code_char8);
        
    elsif resolution = 0.1::float4 then      -- 0.1 deg. resolution, e.g. Hobart: "3414:227:383"
        return trim(code_char1)||trim(code_char2)||trim(code_chars34)||':'||trim(code_char5)||trim(code_char6)||trim(code_char7)||
           ':'||trim(code_char8)||trim(code_char9)||trim(code_char10);
    end if;

END;

$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
