------------------------------------------------------------------------------------------------- -- function: valid_csq -- -- written by: Tony Rees, CSIRO Australia (Tony.Rees@csiro.au) -- -- date created: April, 2005 -- -- purpose: determine if a supplied single c-square code is valid -- -- input: csquare code as csq -- -- output: 'Y' if valid or error message if not valid -- -- remarks: (1) will fail if supplied with more than Oracle varchar2 limit (32767 chars) -- -- (2) will reject codes in excess of 100 characters (likely to be erroneous) -- ------------------------------------------------------------------------------------------------- function valid_csq (csq varchar2 := null) return varchar2 is csq_string varchar2(100); bad_chars_string varchar2(100); err_msg varchar2(100) := null; asterisk_start_reached varchar2(1) := 'N'; begin if csq is null then err_msg := 'missing c-square code'; goto exit_sub; elsif length(csq) >100 then err_msg := 'c-square code exceeds 100 characters (probable error)'; goto exit_sub; end if; --first check for illegal characters - ^ (random illegal character) added as there must be -- at least one character translated into a non null character with this Oracle function -- (Note, this operation replaces all the "permitted" characters 0123456789:* with nulls) bad_chars_string := translate(csq, '^0123456789:*','^'); if length(bad_chars_string) >0 then -- something remains after replacement, not a permitted character err_msg := 'illegal character "'||substr(bad_chars_string,1,1)||'" at position '|| to_char(instr(csq,substr(bad_chars_string,1,1),1)); goto exit_sub; -- valid c-square code must be minimum 4 characters elsif length(csq) <4 then err_msg := 'c-square code is incomplete'; goto exit_sub; -- 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)-1 <4 then err_msg := 'initial cycle contains less than 4 digits'; goto exit_sub; elsif instr((csq||':'),':',1)-1 >4 then err_msg := 'initial cycle contains more than 4 digits'; goto exit_sub; else -- check the initial cycle -- first cycle is 4 characters -- now test they are all digits (no asterisks allowed) bad_chars_string := translate(substr(csq,1,4), '^0123456789','^'); if length(bad_chars_string) >0 then -- a character has been entered not in the set 0-9 err_msg := 'illegal character found at "'||substr(bad_chars_string,1,1)||'"'; goto exit_sub; -- 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'; goto exit_sub; -- 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'; goto exit_sub; -- 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); goto exit_sub; 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); goto exit_sub; 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'; goto exit_sub; elsif csq_string like '%:' then err_msg := 'code cannot terminate with a colon'; goto exit_sub; 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'; goto exit_sub; -- 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'; goto exit_sub; -- 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); goto exit_sub; -- if three digits are correctly present in cycle, second and third must agree -- with the designated intermediate quadrant ( substr(csq_string,2,1) = '1' and (to_number(substr(csq_string,3,1)) not between 0 and 4 or to_number(substr(csq_string,4,1)) not between 0 and 4) ) or ( substr(csq_string,2,1) = '2' and (to_number(substr(csq_string,3,1)) not between 0 and 4 or to_number(substr(csq_string,4,1)) not between 5 and 9) ) or ( substr(csq_string,2,1) = '3' and (to_number(substr(csq_string,3,1)) not between 5 and 9 or to_number(substr(csq_string,4,1)) not between 0 and 4) ) or ( substr(csq_string,2,1) = '4' and (to_number(substr(csq_string,3,1)) not between 5 and 9 or to_number(substr(csq_string,4,1)) not between 5 and 9) ) then err_msg := 'illegal triplet at '||substr(csq_string,1,4); goto exit_sub; end if; -- move to the next cycle (strip the one just tested) csq_string := substr(csq_string, 5); end loop; end if; <> if err_msg is not null then return err_msg; else return 'Y'; end if; end valid_csq; ------------------------------------------------------------------------------------------------- -- function: valid_csq_string -- -- written by: Tony Rees, CSIRO Australia (Tony.Rees@csiro.au) -- -- date created: April, 2005 -- -- purpose: determine if a supplied c-squares string is valid -- -- input: csq_string as csq_str -- -- output: 'Y' if valid or error message if not valid -- -- remarks: will fail if supplied with more than Oracle varchar2 limit (32767 chars) -- ------------------------------------------------------------------------------------------------- function valid_csq_string (csq_str varchar2 := null) return varchar2 is csq_string varchar2(32767); bad_chars_string varchar2(32767); this_csq varchar2(100); this_result varchar2(200); code_count integer :=0; prev_code_length integer; this_code_length integer; err_msg varchar2(100) := null; begin if csq_str is null then err_msg := 'no c-squares string supplied'; goto exit_sub; end if; --first check for illegal characters - ^ (random illegal character) added as there must be -- at least one character translated into a non null character with this Oracle function -- (Note, this operation replaces all the "permitted" characters 0123456789:*| with nulls) bad_chars_string := translate(csq_str, '^0123456789:*|','^'); if length(bad_chars_string) >0 then -- something remains after replacement, not a permitted character err_msg := 'illegal character "'||substr(bad_chars_string,1,1)||'" found at position '|| to_char(instr(csq_str,substr(bad_chars_string,1,1),1)); goto exit_sub; -- valid c-squares string (minimum 1 code) must be minimum 4 characters elsif length(csq_str) <4 then err_msg := 'c-square code is incomplete'; goto exit_sub; elsif csq_str like '%|' then err_msg := 'c-square string cannot terminate with separator character'; goto exit_sub; elsif csq_str like '|%' then err_msg := 'c-square string cannot start with separator character'; goto exit_sub; end if; if err_msg is null then -- add a final separator character (otherwise will loop for ever) csq_string := csq_str||'|'; while length(csq_string) >0 and err_msg is null loop code_count := code_count+1; prev_code_length := this_code_length; this_csq := substr(csq_string,1,instr(csq_string,'|',1)-1); this_result := valid_csq(this_csq); this_code_length := length(this_csq); if this_result != 'Y' then err_msg := this_result||' (at code count='||to_char(code_count)||')'; goto exit_sub; end if; -- not permitted to mix resolutions (code lengths) within the same string if code_count >1 and this_code_length != prev_code_length then err_msg := ' inconsistent code length (at code count='||to_char(code_count)||')'; goto exit_sub; end if; -- move to next code in string (as available) csq_string := substr(csq_string,instr(csq_string,'|',1)+1); end loop; end if; <> if err_msg is not null then return err_msg; else return 'Y'; end if; end valid_csq_string;