0

How can I do these same conversions that are written in SQL Server to PostgreSQL.

These are the data I receive:

@AVLData_GPSElement_Longitude   @AVLData_GPSElement_Latitude
D224E31D                        F8C95DB9

This is the conversion that is written in SQL Server.

SUBSTRING(CONVERT(VARCHAR(50), CONVERT(INT, CONVERT(VARBINARY, @AVLData_GPSElement_Latitude, 2))), 1, 3) + '.' + SUBSTRING(CONVERT(VARCHAR(50), CONVERT(INT, CONVERT(VARBINARY, @AVLData_GPSElement_Latitude, 2))), 4, 7)

SUBSTRING(CONVERT(VARCHAR(50), CONVERT(INT, CONVERT(VARBINARY, @AVLData_GPSElement_Longitude, 2))), 1, 3) + '.' + SUBSTRING(CONVERT(VARCHAR(50), CONVERT(INT, CONVERT(VARBINARY, @AVLData_GPSElement_Longitude, 2))), 4, 7)

and the result it gives is the following:

@MOPO_LAT        @MOPO_LON
-12.102099900   -76.933449900

So far I have managed to do this conversion so far:

SUBSTRING(CONVERT(VARBINARY, AVLData_GPSElement_Latitude, 2)::INTEGER ::VARCHAR FROM 1 FOR 3) + '.' + SUBSTRING(CONVERT(VARBINARY, AVLData_GPSElement_Latitude, 2)::INTEGER ::VARCHAR) FROM 4 FOR 7);

but my big question arises in this function:

CONVERT (VARBINARY, @AVLData_GPSElement_Latitude, 2)

I don't know how to translate it because I don't find much information about it. I am working on conversions of teltonika fm920 gps equipment. I appreciate your help in advance.

Roller Fernandez
  • 171
  • 1
  • 3
  • 12
  • What have you tried? The official documentation shows the functions available, and googling the function name plus the RDBMS name gives lots of info... – Dale K Oct 12 '20 at 20:40
  • So far I have managed to do this conversion : SUBSTRING(CONVERT(VARBINARY, AVLData_GPSElement_Latitude, 2)::INTEGER ::VARCHAR FROM 1 FOR 3) + '.' + SUBSTRING(CONVERT(VARBINARY, AVLData_GPSElement_Latitude, 2)::INTEGER ::VARCHAR) FROM 4 FOR 7); but my big question arises in this function CONVERT(VARBINARY, @AVLData_GPSElement_Latitude, 2) In postgres it has a bytea function but I don't know how to pass it the value 2 – Roller Fernandez Oct 12 '20 at 20:48
  • [edit] it all into your question... and clarify specifically which part of the conversion you are stuck with. – Dale K Oct 12 '20 at 20:48
  • That's a very ugly way to do that conversion even in T-SQL, incidentally, and since it seems to assume negative numbers I'm not even sure it's reliable. `SELECT CONVERT(INT, CONVERT(BINARY(4), @AVLData_GPSElement_Latitude, 2)) / 10000000.0` would be cleaner (this can then be converted/formatted further if fewer decimals are desired). It's basically just parsing the number as hex and dividing, which is probably easier in Postgres than the string manipulation too (but I don't know it well enough to say). – Jeroen Mostert Oct 12 '20 at 20:53
  • Jeroen Mostert How could I do something similar in postgres. – Roller Fernandez Oct 12 '20 at 20:58
  • Apparently it's [far from trivial](https://stackoverflow.com/q/8316164/4137916). `('x' || lpad(AVLData_GPSElement_Latitude, 8, '0'))::bit(32)::int` should do the trick (and then dividing by `10000000.0` has much the same result). – Jeroen Mostert Oct 12 '20 at 21:05
  • It might be easier if you described what you're trying to do, rather than how you're currently doing it. I'm having trouble following the SQL code, and there's probably a better way than a straight conversion. – Schwern Oct 12 '20 at 21:11

1 Answers1

1

This function is first converting hex to an integer. F8C95DB9 is -121020999. Use the technique here. Stick an x on the front so bit(32) will cast it as hex, then cast that to a 32 bit integer.

select ('x'||'F8C95DB9')::bit(32)::int4
    int4    
------------
 -121020999

Then divide by 10000000.0.

select ('x'||'F8C95DB9')::bit(32)::int4 / 10000000.0 as lat;
         lat          
----------------------
 -12.1020999000000000

And at that point I would stop and leave further formatting to the display layer.

Note: If you're working with Latitude and Longitude, consider PostGIS.

Schwern
  • 153,029
  • 25
  • 195
  • 336