 # Thread: High-lighting "Nearly" equal values

1. ## High-lighting "Nearly" equal values

In my current project one of the fields in my contains a map reference in format DDDMMSSHDDDMMSSH where DDD is the Degree, MM Minute, SS Second and Hemisphere (Ie N,E,S,W)values (the first set are for Latitude the second for Longitude). Currently I use a colour expression to change to "white on red" when there is a total match between Current and Previous values of field map_refer. What I Can't seem to manage is to somehow flag a colour change when there are only small differences in either of the two SS values. I tried Soundex but that flags all map_refer values as the same!
Any suggestions anyone?  Reply With Quote

2. ## RE: High-lighting

Maybe the between() function could be used to test if the given SS value is in the range SS + x or SS - x, where x equals an arbitrary value you decide is close enough.

-- tom  Reply With Quote

3. ## RE: High-lighting

Hi Tom,
I've come up with a generalised expression:

between(val(substr(map_refer,1,7)),val(substr(PREVIOUS("map_refer"),1,7))-4,val(substr(PREVIOUS("map_refer"),1,7))+4).OR.between(val(substr(map_refer,9,7)),val(substr(PREVIOUS("map_refer"),9,7))-4,val(substr(PREVIOUS("map_refer"),9,7))+4)

but if the value of SS is less than 3 the answer would be -1 in a normal number (base 10) where as Degrees, Minutes, Seconds are base 60 so in the above example the SS value should be 59 and I still have to substract 1 from the value in MM as well.
Then if the value of SS is greater than 57 the answer would be 61 in a normal number, but 01 AND add 1 to MM
I think I also have to build in a test on whether the current record value is in the same hemisphere ie (N,E,S,W) as the previous record and so on.....

Any suggestion on how to incorporate this in the above expression (which is already getting quite complex)?  Reply With Quote

4. ## RE: High-lighting

Your problem is even worse. You might have 59 degrees 59 minutes 58 seconds as a candidate value. If you add 3 secs here it affects degrees and minutes, right?

I think you're going to have to write a UDF that converts degrees, minutes, and seconds into seconds. And then use that total as the beginning point for comparisons, using plus or minus seconds, to make the comparison for values that are "close enough".

-- tom  Reply With Quote

5. ## RE: High-lighting

Some Lateral Thinking, maybe this is a Trigonometry problem. Trouble is I haven't use it for Thirty years or more, I'll have to find someone local to me who has used it recently!  Reply With Quote

6. ## RE: High-lighting

This is only a trigonometric problem if you want to highlight any point within a given radius of the point in question. In other words, you need to use trigonometry (or the Pythagorean theorem) if you want any point within a circle of of a given radius.

If on the other hand it is OK to highlight points within a square whose center is the point of reference, use of the between() function will suffice and be simpler. I like Tom's suggestion to convert any combination of degrees, minutes and seconds into total seconds and use this one number for comparison.

Steve  Reply With Quote

7. ## RE: High-lighting

For those interested, my solution was to use the color equation to set one colour fo latitude and another for longitude:

Latitude

between(val(left(BASES-"map_refer,3))*3600+val(substr(BASES-"map_refer,4,2))*60+val(substr(BASES-"map_refer,6,2)),val(left(previous("BASES-"map_refer"),3))*3600+val(substr(previous("BASES-"map_refer"),4,2))*60+val(substr(previous("BASES-"map_refer"),6,2))-4,val(left(previous("BASES-"map_refer"),3))*3600+val(substr(previous("BASES-"map_refer"),4,2))*60+val(substr(previous("BASES-"map_refer"),6,2))+4).AND.BASES-"Map_Refer""""

Longitude

between(val(substr(BASES-"map_refer,9,3))*3600+val(substr(BASES-"map_refer,12,2))*60+val(substr(BASES-"map_refer,14,2)),val(substr(previous("BASES-"map_refer"),9,3))*3600+val(substr(previous("BASES-"map_refer"),12,2))*60+val(substr(previous("BASES-"map_refer"),14,2))-4,val(substr(previous("BASES-"map_refer"),9,3))*3600+val(substr(previous("BASES-"map_refer"),12,2))*60+val(substr(previous("BASES-"map_refer"),14,2))+4).AND.BASES-"Map_Refer""""  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•