Migrate SQL Server functions to PostgreSQL

Question

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.

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
– Dale K, Commented Oct 12, 2020 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
– Roller Fernandez, Commented Oct 12, 2020 at 20:48
edit it all into your question... and clarify specifically which part of the conversion you are stuck with. — Dale K
– Dale K, Commented Oct 12, 2020 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
– Jeroen Mostert, Commented Oct 12, 2020 at 20:53
Jeroen Mostert How could I do something similar in postgres. — Roller Fernandez
– Roller Fernandez, Commented Oct 12, 2020 at 20:58

Schwern · Accepted Answer · 2020-10-12 21:32:22Z

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.

answered Oct 12, 2020 at 21:32

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roller Fernandez Over a year ago

Thank you very much for the answer I get the following message "operator does not exist: variable character * integer 42883"

Collectives™ on Stack Overflow

Migrate SQL Server functions to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related