Converting Lat Long Coordinates to A Hex value geographical coordinate possible (EWKB)?

Question

I have a few coordinates and their corresponding coordinates which are stored in a geography format in my database.

2.352917, 101.801556 : 0xE6100000010D6DE7FBA9F1D2024017D9CEF7537359400000000000000000 1.283333, 103.500000 : 0xE6100000010DBA490C022B87F43F0000000000E059400000000000000000 4.233333, 117.883333 : 0xE6100000010D3BDF4F8D97EE10408D976E1283785D400000000000000000 17.683333, 83.216667 : 0xE6100000010D022B8716D9AE3140D9CEF753E3CD54400000000000000000 5.430167, 103.149722 : 0xE6100000010DB81E85EB51B815409A99999999C959400000000000000000 10.173167, 76.180222 : 0xE6100000010D7F6ABC7493582440EC51B81E850B53400000000000000000 4.533333, 100.516667 : 0xE6100000010D6F1283C0CA2112400C022B87162159400000000000000000 3.200000, 73.216667 : 0xE6100000010D9A99999999990940D9CEF753E34D52400000000000000000

Main purpose here is to use a python script to convert the coordinates to the respective string without using any database functions. I am unsure of the format for the string that I current have.

I have researched and it shows me that the string is potentially a EWKB format. I have tried searching to find a script that will convert the coordinates into the values.

def coordinates_to_hex(lat, lng):
    # Convert latitude and longitude to binary representation
    lat_bin = struct.pack('>d', lat)
    lng_bin = struct.pack('>d', lng)
    
    # Concatenate binary representations in a specific order
    hex_str = lng_bin.hex() + lat_bin.hex() + '0000000000000000'
    hex_str = hex_str[:16] + hex_str[18:20] + hex_str[16:18] + hex_str[20:]
    
    # Add prefix and return hexadecimal string
    return '0xE610000001' + hex_str.upper()

The script above would always return a different value for the specific coordinates.

Any help would be appreciated.

Answer 1

You can just cast it in SQL

SELECT CAST(YourValue AS geography)
FROM YourTable

db<>fiddle

The equivalent in C# is this

SqlGeography.Deserialize(new SqlBytes(yourByteArray))

I don't think Python has any functions to do this. You may need to write a Powershell script or a C# library to do this.

You could write your own Python parser, the format is documented here.