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

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

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 then      -- 10 deg. resolution, e.g. Hobart: "3414"
      return trim(code_char1)||trim(code_char2)||trim(code_chars34);
      
    elsif resolution = 5 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 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 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 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' VOLATILE;
ALTER FUNCTION get_csquare(float8, float8, int4) OWNER TO postgres;