get_csquare.txt

File contents

---  PL/SQL function: get_csquare                                              
---  Written by: Tony Rees, CSIRO Australia (Tony.Rees@csiro.au)              
---  Date Created: January, 2002                                                            
---  Description: gets c-squares code for a supplied lat/long coordinate pair  
---  Inputs: nlat: latitude in decimal degrees (number)
---          nlong: longitude in decimal degrees (number)
---          resolution: square size in decimal degrees (number)
---  Output: code as varchar2
---  Remarks: resolution currently expected as one of the following: 10, 5, 1, 0.5, 0.1 [degrees]
---          0 deg latitude/longitude is treated as positive
---          90 deg latitude is treated as 89.99999, -90 deg latitude is treated as -89.99999
---          180 deg longitude is treated as 179.99999, -180 deg longitude is treated as -179.99999

  function get_csquare (nlat number :=null, nlong number :=null, resolution number :=1) return varchar2 is
  
    code_char1 varchar2 (1) :=null;
    code_char2 varchar2 (1) :=null;
    code_chars34 varchar2 (2) :=null;
    code_char5 varchar2 (1) :=null;
    code_char6 varchar2 (1) :=null;
    code_char7 varchar2 (1) :=null;
    code_char8 varchar2 (1) :=null;
    code_char9 varchar2 (1) :=null;
    code_char10 varchar2 (1) :=null;
    lat_remainder number :=null;
    long_remainder number :=null;    

  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)));
      lat_remainder := abs(nlat) - (to_number(code_char2)*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))), -2);
      long_remainder := abs(nlong) - (to_number(code_chars34)*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));
      lat_remainder := lat_remainder - to_number(code_char6);
    
       --get the 7th digit (single degrees longitude)
      code_char7 := to_char(trunc(long_remainder));
      long_remainder := long_remainder - to_number(code_char7);


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

         --get the 8th digit (0.5-degree quadrant)
        if (to_number(code_char9) between 0 and 4) and (to_number(code_char10) between 0 and 4) then
          code_char8 := '1';
        elsif (to_number(code_char9) between 0 and 4) and (to_number(code_char10) between 5 and 9) then
          code_char8 := '2';
        elsif (to_number(code_char9) between 5 and 9) and (to_number(code_char10) between 0 and 4) then
          code_char8 := '3';
        elsif (to_number(code_char9) between 5 and 9) and (to_number(code_char10) 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 code_char1||code_char2||code_chars34;
      
    elsif resolution = 5 then      -- 5 deg. resolution, e.g. Hobart: "3414:2"
      return code_char1||code_char2||code_chars34||':'||code_char5;
      
    elsif resolution = 1 then      -- 1 deg. resolution, e.g. Hobart: "3414:227"
      return code_char1||code_char2||code_chars34||':'||code_char5||code_char6||code_char7;
      
    elsif resolution = 0.5 then      -- 0.5 deg. resolution, e.g. Hobart: "3414:227:3"
      return code_char1||code_char2||code_chars34||':'||code_char5||code_char6||code_char7||
        ':'||code_char8;
        
    elsif resolution = 0.1 then      -- 0.1 deg. resolution, e.g. Hobart: "3414:227:383"
      return code_char1||code_char2||code_chars34||':'||code_char5||code_char6||code_char7||
        ':'||code_char8||code_char9||code_char10;
        
    end if;
    
  end get_csquare;