0

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.

Coding with noob
  • 1
  • 2
  • The above is Python (?) not T-SQL, what do you actually want? – Charlieface Apr 20 '23 at 11:46
  • Are you sure it's valid WKB, it doesn't seem to match any of the formats? – Charlieface Apr 20 '23 at 11:52
  • it uses internal .NET format, so no. but it took me a little google thing to find STAsBinary https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/stasbinary-geometry-data-type?view=sql-server-ver16 maybe, you'll get more luck with that – siggemannen Apr 20 '23 at 12:01
  • you could also use STAsText method to just return the WKT value which is what i usually do. although you still need to know the original SRID – siggemannen Apr 20 '23 at 12:02
  • Or, if you're converting coordinates TO geometry, you can use STGeomFromText and build a string using WKT – siggemannen Apr 20 '23 at 12:04
  • @Charlieface Sorry if I was unclear, have edited the question! – Coding with noob Apr 20 '23 at 13:14
  • The binary format of geography is described at https://learn.microsoft.com/en-us/openspecs/sql_server_protocols/ms-ssclrt/77460aa9-8c2f-4449-a65e-1d649ebd77fa Page 9: Integers and double-precision floating-point numbers are expressed in little-endian format. SRID(4 bytes/integer) Version(1 byte) Serialization Properties(1 byte) Number of Points (optional, unsigned, omitted based on Serialization Properties) (4 bytes) Points (optional, variable) (16 * Number of Points bytes/ 8 bytes for each lat&long) Z Values (optional, 8 * Number of Points bytes) – lptr Apr 20 '23 at 14:25

1 Answers1

0

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43