----------------------------------------------------------------------------------------------------- -- 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;