topic Re: Lat/Long stored as hex in SAS Programming

Lat/Long stored as hex

Jim_Ogilvie — Tue, 10 Mar 2020 14:41:45 GMT

Hi all,

I need to convert a series of latitudes and longitudes, which are stored as hex, into standard lat/long notation, for example:

Long: 0310149C

Lat: FFFDFD28

Should convert to something like this:

51.386396

-0.131605

I've tried:

data _null_;
   
   lat = input('0310149C',hex16.);
   long = input('FFFDFD28',hex16.);
   
   put lat;
   put long;
run;

Which gives:

51385500

4294835496

As you can see, the first is correct, but the second conversion is not. I'm sure this has to do with decoding the signed part of these numbers, but can't find a work around.

Thanks in advance

Jim

Re: Lat/Long stored as hex

mkeintz — Tue, 10 Mar 2020 15:59:36 GMT

I presume '00000000'x is zero degrees.

So tell me - what is the hex representation of 360 degrees?

Re: Lat/Long stored as hex

Jim_Ogilvie — Tue, 10 Mar 2020 16:19:57 GMT

Hi,

Because lat/long is always relative to either the Greenwich Meridian or the Equator, you would never have 360 degrees, rather, you would have -180 or 180 as the farthest point from these reference lines...

Re: Lat/Long stored as hex

mkeintz — Tue, 10 Mar 2020 22:41:18 GMT

@Jim_Ogilvie wrote:

Hi,

Because lat/long is always relative to either the Greenwich Meridian or the Equator, you would never have 360 degrees, rather, you would have -180 or 180 as the farthest point from these reference lines...

Ah yes, I should have realized that. Let me revise my question. What is the hex representation you expect for +180 degrees and for -180 degrees … and for 90 degrees?

Also, 51.386396 is not close to 51385500. The hex representation of 51.386396 is the value below, not 0310149C

339  data _null_;
340    lat=51.386396;
341    put lat=hex16.;
342  run;

lat=4049B1756C93A711

So I'm wondering - didn't your data provider generate 16 hexadecimal-digit values? That's the only length for which the HEX informat will generate floating point values (see hexW informat: windows or hex informat: UNIX . Shorter than 16, SAS documentation says it will be interpreted as integer. And it may be that even though you are trying t use HEX16., the shorter length may over-ride and cause treatment as an integer.

Re: Lat/Long stored as hex

ChrisNZ — Wed, 11 Mar 2020 03:59:20 GMT

We need more information about how the values are encoded.

This is very confusing. It looks like the equator is latitude FFFFFF.

We can always make up rules by reverse engineering the data, but actual rules would be better, especially since we always seem to be a bit off anyway.

data _null_;
   A = (input('FFFDFD28',hex8.) - input('FFFFFFFF',hex8.)) /1e6;
   putlog A=;
 run;

A=-0.131799

Re: Lat/Long stored as hex

Jim_Ogilvie — Wed, 11 Mar 2020 08:45:52 GMT

Thank for your input guys.

Unfortunately, the vendors' technical support person wasn't able to shed any light on this, said he needed to discuss it with a colleague's and has now gone on holiday for 2 weeks!

Chris,

This calculation works great for that particular one, but when I run the other hex value through the same processing, I get:

=-4243.581795

I presume that a little "if-then" logic would fix this, but I do not know what condition to check for.

Re: Lat/Long stored as hex

ChrisNZ — Wed, 11 Mar 2020 22:05:03 GMT

If you give a few representative values (at least one E and one W longitude, and one N and one S latitude) I don't doubt that the inquisitive minds here will compete the solve the puzzle.

Re: Lat/Long stored as hex

Jim_Ogilvie — Thu, 12 Mar 2020 09:40:45 GMT

I have heard back from the vendor (colleague of the original tech support guy) - who passed me some Oracle SQL, that they use to carry out the conversion.

I have built the following SAS function, which carries out the same work:

proc fcmp outlib=work.funcs.utils;
   function decode_hex_coord(coord $);
      num = input(coord,hex16.);
      if num  >= 2**32 / 2 then return(num - (2**32)/1000000);
      else return(num/1000000);
   endsub;
   
options cmplib=work.funcs;


data _null_;
   test_lat = decode_hex_coord('030ED8FC');
   test_lon = decode_hex_coord('0007A4A4');
   put test_lat ", " test_lon;
run;

Output: 51.3047 , 0.5009

Confirmed as correct in googlemaps.

I have also found that that one of those original hex codes was a user entry error!!! Really unhelpful - sorry.

Thanks for you help all.

Re: Lat/Long stored as hex

ChrisNZ — Thu, 12 Mar 2020 10:12:12 GMT

The function might possibly be more accurate like this?

   function decode_hex_coord(coord $);
      num = input(coord,hex16.);
      if num  >= 2**32 / 2 then return( (num - 2**32)/1e6);
      else return(num/1e6);
   endsub;

which can also be written

   function decode_hex_coord(coord $);
      NUM = input(coord,hex16.);
      return( (NUM - 2**32*(NUM >= 2**31) ) / 1e6);
   endsub;

though I am unsure that's an improvement.

Otherwise the value FFFDFD28 is misread.

Re: Lat/Long stored as hex

Jim_Ogilvie — Thu, 12 Mar 2020 11:48:28 GMT

Chris - that's perfection.

Many thanks,

Jim

Re: Lat/Long stored as hex

Tom — Thu, 12 Mar 2020 16:56:15 GMT

Not sure you need to get that complicated. Looks like the numbers are storing negatives as twos complements.

https://en.wikipedia.org/wiki/Two%27s_complement

Any string with the highest order bit set (so the first digit is 8,9,A,B,C,D,E or F) is a negative number. So a simple way to find the absolute value is to take the one's complement and add one. If you then negate that you have the number it represents.

So let's read in some of the hex strings you have shown and other key values.

data test;
  input string $8.;
cards;
030ED8FC
0007A4A4
0310149C
FFFDFD28
00000001
00000000
FFFFFFFF
055D4A80 
FAA2B580
0ABA9500 
F5456B00
;

And convert them into numbers. Lets move the decimal point 6 places to the left also.

data test2;
  set test;
  number = input(string,hex8.);
  if '8' <= string then number = -bnot(number)-1 ;
  number=number*1E-6;
  format number 11.6;
run;

Results

Obs     string            number

  1    030ED8FC        51.304700
  2    0007A4A4         0.500900
  3    0310149C        51.385500
  4    FFFDFD28        -0.131800
  5    00000001         0.000001
  6    00000000         0.000000
  7    FFFFFFFF        -0.000001
  8    055D4A80        90.000000
  9    FAA2B580       -90.000000
 10    0ABA9500       180.000000
 11    F5456B00      -180.000000

Re: Lat/Long stored as hex

Jim_Ogilvie — Thu, 12 Mar 2020 15:51:17 GMT

Also work brilliantly - thanks Tom.