get_limits.txt

File contents

-----------------------------------------------------------------------------------------------------
--  PL/SQL function: get_limits                                                                    --
--  Written by: Tony Rees, CSIRO Australia (Tony.Rees@csiro.au)                                    --
--  Date initiallly created: March, 2002                                                           --
--     - validation added June, 2003                                                               --
--     - made recursive: June, 2003 (previously was hard coded for specific code lengths)          --
--  Description:                                                                                   --
--     (1) Tests a supplied c-square code to determine if it is valid (=validator section)         --
--     (2) If valid, gets N, S, W, E limits for a given c-square                                   --
--     (3) Produces error report if invalid code supplied                                          --
--  Inputs: csq - c-square code (varchar2)                                                         --
--          limit - bounding coordinate required (N, S, W or E);                                   --
--                    if none specified, all are returned                                          --
--  Output: either N, S, W, E limit as requested, or                                               --
--    string comprising comma-separated values of N, S, W and E as decimal degrees (converted      --
--     to varchar2), or error report if code is invalid                                            --
--  Remarks: could be split into 2 functions (validator + limit getter), however the latter        --
--     will fail if used alone on an invalid code.                                                 --
--  Changes: (1) Fixed bug in section "now transform the start lat and long to correct decimal places" -
--    which was causing incorrect longitudes around 100 degrees W/E (AJR, 09/2003)                --
--    (2) Altered section "Now, parse the string to extract minimimum absolute values of lat and long" --
--      to use numeric rather than character-based logic, as latter had bug affecting              --
--      squares smaller than 0.5 degrees (AJR, 09/2004)                                            --
-----------------------------------------------------------------------------------------------------

  function get_limits (csq varchar2 := null, limit varchar2 := null) return varchar2 is
  
	  csq_string varchar2(60);
	  err_msg varchar2(50) := null;
	  asterisk_start_reached varchar2(1) := 'N';
	  leading_digit varchar2(1) := null;
	  trailing_digit varchar2(1) := null;
	  start_lat number;
	  start_long number;
	  end_lat number;
	  end_long number;
	  temp_sqr_size number := 100;  -- will divide by ten for every cycle e.g. cycle 1=tens, cycle 2=units, etc.
	  N_limit varchar2(60);
	  S_limit varchar2(60);
	  W_limit varchar2(60);
	  E_limit varchar2(60);
	  
diagnostic_msg varchar2 (2000);

  begin
  
        -----------------------------------------------
        -- ** code validation section starts here ** --
        -----------------------------------------------
  
	--first check for illegal characters  - ^ character added as there must be at least one character translated
	--  (Note, this operation replaces all the "good" characters following "^" with nulls)
	csq_string := translate(csq, '^0123456789:*','^');

	if length(csq_string) >0 then    -- something remains after replacement, not a good character

		err_msg := 'bad character found at "'||substr(csq_string,1,1)||'"';

	-- c-square code must be minimum 4 characters
	
	elsif (csq is null or length(csq) <4) then
	
		err_msg := 'c-square code missing or incomplete';
		
	-- test length of first cycle (to initial colon), reject if other than 4 characters
	--  (add a trailing colon in case none exists)

	elsif instr((csq||':'),':',1) <5 then

		err_msg := 'initial cycle contains less than 4 digits';

	elsif instr((csq||':'),':',1) >5 then

		err_msg := 'initial cycle contains more than 4 digits';

	else  -- check the initial cycle
	
		-- first cycle is 4 characters
		-- now test they are all digits (no asterisks allowed)
		csq_string := translate(substr(csq,1,4), '^0123456789','^');

		if length(csq_string) >0 then    -- a character has been entered not in the set 0-9, or ^ has been entered

			err_msg := 'bad character found at "'||substr(csq_string,1,1)||'"';

		-- check global quadrant (first character) for validity - must be 1, 3, 5 or 7
		elsif substr(csq,1,1) not in ('1','3','5','7') then

			err_msg := 'illegal global quadrant value at '||substr(csq, 1,1)||'xxx';

		-- check tens of degrees of latitude and longitude for validity

		-- minimum absolute latitude: character 2 (cannot be more than 89.9999999...)
		elsif to_number(substr(csq,2,1)) not between 0 and 8 then

			err_msg := 'illegal latitude value at x'||substr(csq, 2,1)||'xx';

		-- minimum absolute longitude: characters 3 and 4 (cannot be more than 179.999999...)
		elsif to_number(substr(csq,3,2)) not between 0 and 17 then

			err_msg := 'illegal longitude value at xx'||substr(csq, 3,2);
			
		end if;


	end if;

	if err_msg is null and length(csq) >4 then  -- more than one cycle is present

		csq_string := csq;
		
		-- First test for asterisks. If present, check correct form, then delete any valid cycles with asterisks
		--   before further processing
		
		if csq_string like '%*%' then  -- asterisk/s present
		
			-- delete any final "truncated" cycle (colon + single asterisk)
			if csq_string like '%:*' then
			
				csq_string := substr(csq_string, 1, length(csq_string)-2);
			
			end if;
			
			-- remaining cycles (apart from the first) must be three characters, 5 legal options if
			--   asterisks present. Test backwards from the end (since asterisks must always be in a chain)
			
			while csq_string like '%*' and length(csq_string) >4 and err_msg is null
			  and asterisk_start_reached != 'Y' loop
			
				if csq_string like '%:***' then

					-- current last cycle is valid and all asterisks,
					--  strip the cycle before testing the previous one
					csq_string := substr(csq_string, 1, length(csq_string)-4);
				
				elsif csq_string like '%:1**' or csq_string like '%:2**'
				  or csq_string like '%:3**' or csq_string like '%:4**' then
				  					
					-- current last cycle is valid, strip the cycle but don't test previous one,
					--  since the start of the legal asterisk block has been reached
					csq_string := substr(csq_string, 1, length(csq_string)-4);
					asterisk_start_reached := 'Y';
				
				else
				
					err_msg := 'illegal triplet at '||substr(csq_string,length(csq_string)-3);
				
				end if;
				
			end loop;

		end if;
		
		
		-- detect any valid remaining final "truncated" cycle (colon + single digit 1-4) and delete,
		--   flag if invalid value present
		
		if err_msg is null then
		
			if csq_string like '%:1' or csq_string like '%:2' or csq_string like '%:3' or 
			  csq_string like '%:4' then

				csq_string := substr(csq_string, 1, length(csq_string)-2);

			elsif csq_string like '%:5' or csq_string like '%:6' or csq_string like '%:7' or 
			  csq_string like '%:8' or csq_string like '%:9' then

				err_msg := 'illegal final intermediate quadrant value';

			elsif csq_string like '%:' then

				err_msg := 'code cannot terminate with a colon';

			end if;
			
		end if;
		

		-- now examine each cycle in turn, following the initial 4 digits
		-- (any valid final "truncated" cycle has already been stripped, 
		--   also any valid segments containing asterisks, so any remaining
		--   ones should be 3 digits, with no asterisks)
		
		-- remove initial 4 digits, leaving colon at start of next cycle
		csq_string := substr(csq_string,5);
		
		while length(csq_string) > 0 and err_msg is null loop
		
			-- cycle must be a colon plus 3 digits (plus additional cycles if present)
			
			if length(csq_string) <4 then
			
				err_msg := 'incomplete cycle after colon character';
				
			-- now check length of the cycle - to next colon character
			--   (trailing colon added in case not present)

			elsif instr((csq_string||':'),':',2) >5 then
				err_msg := 'cycle '||substr(csq_string,1,5)||'... contains more than 3 digits';

			-- check for valid intermediate quadrant digit
			
			elsif substr(csq_string,2,1) not in ('1','2','3','4') then
			
				err_msg := 'illegal intermediate quadrant value at '||substr(csq_string,1,4);
				
			-- if three digits are correctly present in cycle, second and third must agree
			--   with the designated intermediate quadrant

			elsif (substr(csq_string,2,1) = '1' and (substr(csq_string,3,1) not in ('0','1','2','3','4')
			  or substr(csq_string,4,1) not in ('0','1','2','3','4'))) or

			(substr(csq_string,2,1) = '2' and (substr(csq_string,3,1) not in ('0','1','2','3','4')
			  or substr(csq_string,4,1) not in ('5','6','7','8','9'))) or

			(substr(csq_string,2,1) = '3' and (substr(csq_string,3,1) not in ('5','6','7','8','9')
			  or substr(csq_string,4,1) not in ('0','1','2','3','4'))) or

			(substr(csq_string,2,1) = '4' and (substr(csq_string,3,1) not in ('5','6','7','8','9')
			  or substr(csq_string,4,1) not in ('5','6','7','8','9')))

			  then

				err_msg := 'illegal triplet at '||substr(csq_string,1,4);

			end if;


			-- move to the next cycle (strip the one just tested)
			csq_string := substr(csq_string, 5);

		end loop;

	end if;

	------------------------------------------
        -- ** end of code validation section ** --
        ------------------------------------------
        
  	-- now assume code is valid, if no error message (function only works for correctly formed codes)
  
	if err_msg is null then  -- code is valid, get the N, S, W, E limits

		--reset csq_string to original value
		csq_string := csq;

		-- remove any chains of asterisks if present (calculate boundaries on the total "compressed" area)
		if csq_string like '%*%' then

			while csq_string like '%*' or csq_string like '%:' loop

				csq_string := substr(csq_string, 1, (length(csq_string)-1));

			end loop;

		end if;
		
		-- get the leading digit
		leading_digit := substr(csq_string,1,1);
		
		
		-- test for any trailing digit, save if found and then strip last 2 characters
		--   to ensure string ends in a complete triplet
		
		if csq_string like '%:1' then
			trailing_digit := '1';
			csq_string := substr(csq_string, 1, (length(csq_string)-2));
		elsif csq_string like '%:2' then
			trailing_digit := '2';
			csq_string := substr(csq_string, 1, (length(csq_string)-2));
		elsif csq_string like '%:3' then
			trailing_digit := '3';
			csq_string := substr(csq_string, 1, (length(csq_string)-2));
		elsif csq_string like '%:4' then
			trailing_digit := '4';
			csq_string := substr(csq_string, 1, (length(csq_string)-2));
		end if;
		
		
		-- Now, parse the string to extract minimimum absolute values of lat and long
		
		-- first add a final colon (else will loop forever!)
		csq_string := csq_string||':';
		
		-- Go through the cycles one by one, extracting relevant digits and assembling hundreds, tens, units, etc. as required
		
		-- E.g., lat aa.aaaa and long bbb.bbbb are encoded as "[x]abb:[y]ab:[y]ab:[y]ab:[y]ab:[y]ab"
		--   where initial cycle is tens of latitude, and hundreds plus tens of longitude;
		--   second cycle is units of degrees of lat and long; third cycle is tenths; fourth cycle is hundredths; etc.
		
		while length(csq_string) >0 loop
		
			temp_sqr_size := temp_sqr_size/10;
			
			if temp_sqr_size = 10 then  -- first time through, 4-digit cycle, includes 2 characters for longitude
			
				start_lat := to_number(substr(csq_string,2,1))*temp_sqr_size; -- 1 character (tens)
				start_long := to_number(substr(csq_string,3,2))*temp_sqr_size; -- 2 characters (hundreds + tens)
				
			else  -- all other complete cycles (3 digits), single char for both latitude and longitude
			
				start_lat := start_lat+(to_number(substr(csq_string,2,1))*temp_sqr_size);
				start_long := start_long+(to_number(substr(csq_string,3,1))*temp_sqr_size);

			end if;
			
			-- strip the cycle just processed, plus colon character separator
			csq_string := substr(csq_string, instr(csq_string,':',1)+1);
			
		end loop;
		
		
		-- add relevant extra fraction for trailing intermediate quadrant if present
		
		if trailing_digit in ('3','4') then
			start_lat := start_lat+(temp_sqr_size*0.5);
		end if;
		
		if trailing_digit in ('2','4') then
			start_long := start_long+(temp_sqr_size*0.5);
		end if;
		
		
		-- get the end lat, longs

		if trailing_digit is not null then  -- we have a square from the "intermediate" sequence (5, 0.5, 0.05, etc.)
			
			end_lat := start_lat+(temp_sqr_size*0.5);
			end_long := start_long+(temp_sqr_size*0.5);
			
		else  -- square is from main sequence (10, 1, 0.1, etc.)
		
			end_lat := start_lat+temp_sqr_size;
			end_long := start_long+temp_sqr_size;
			
		end if;
		
			
		-- translate absolute values to correctly signed values
	    	-- (transpose starts, ends as appropriate)

		if leading_digit = '1' then   --NE global quadrant

			N_limit := to_char(end_lat);
			S_limit := to_char(start_lat);
			W_limit := to_char(start_long);
			E_limit := to_char(end_long);

		elsif leading_digit = '3' then   --SE global quadrant, lats are negative

			N_limit := to_char(0-start_lat);
			S_limit := to_char(0-end_lat);
			W_limit := to_char(start_long);
			E_limit := to_char(end_long);

		elsif leading_digit = '5' then   --SW global quadrant, lats and longs are both negative

			N_limit := to_char(0-start_lat);
			S_limit := to_char(0-end_lat);
			W_limit := to_char(0-end_long);
			E_limit := to_char(0-start_long);

		elsif leading_digit = '7' then   --NW global quadrant, longs are negative

			N_limit := to_char(end_lat);
			S_limit := to_char(start_lat);
			W_limit := to_char(0-end_long);
			E_limit := to_char(0-start_long);

		end if;
		

		--add trailing ".0" if appropriate

		if N_limit not like '%.%' then
			N_limit := N_limit||'.0';
		end if;

		if S_limit not like '%.%' then
			S_limit := S_limit||'.0';
		end if;

		if W_limit not like '%.%' then
			W_limit := W_limit||'.0';
		end if;

		if E_limit not like '%.%' then
			E_limit := E_limit||'.0';
		end if;
		

		--add leading "0" if appropriate
		
		if N_limit like '.%' then
			N_limit := '0'||N_limit;
		elsif N_limit like '-.%' then
			N_limit := '-0'||substr(N_limit,2);
		end if;

		if S_limit like '.%' then
			S_limit := '0'||S_limit;
		elsif S_limit like '-.%' then
			S_limit := '-0'||substr(S_limit,2);
		end if;

		if W_limit like '.%' then
			W_limit := '0'||W_limit;
		elsif W_limit like '-.%' then
			W_limit := '-0'||substr(W_limit,2);
		end if;

		if E_limit like '.%' then
			E_limit := '0'||E_limit;
		elsif E_limit like '-.%' then
			E_limit := '-0'||substr(E_limit,2);
		end if;

		--return limit as requested by user, or all if no single one specified
		
		if limit ='N' then		
			return N_limit;			
		elsif limit ='S' then		
			return S_limit;			
		elsif limit ='W' then		
			return W_limit;			
		elsif limit ='E' then		
			return E_limit;			
		else	
			return N_limit||','||S_limit||','||W_limit||','||E_limit;
		end if;

	else

		return 'Error: '||err_msg;

	end if;

  end get_limits;