Convert string value to Geography data type in SQL Server

Question

I have a table with a Location column. This column is a varchar(255) at present stored from a spreadsheet. It contains a long SRID string value, like this example: 0xE6100000010C61C3D32B65A14440C4B12E6EA3BD5BC0

Question, I have a need to take that value as is, and store it in my addresses table as a Geography data type. When I look in the addresses table, I see other Location data that exists and it looks exactly as my example value above.

However, when I try and use the Convert(Geography, Location) from my Select statement, I get this error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

System.FormatException: 24114: The label 0xE6100000010C1F6FA8 in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only). System.FormatException:...

How do I convert or cast this existing varchar data to the already existing Geography data in my addresses table?

Answer 1

You could use the geography::STGeomFromWKB, which needs a binary value and a SRID as input. The problem you have a binary value stored in a varchar, and converting/casting it to binary will actually make it something different.

See: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql about Binary Data Types.

Try elaborating on the following example (works on my SQL 2014):

DECLARE @location varchar(255)='0xE6100000010C61C3D32B65A14440C4B12E6EA3BD5BC0'

DECLARE @binlocation varbinary (max)=CONVERT(varbinary(max), @location, 1)

DECLARE @b geography

SET @b=@binlocation

SELECT @b.ToString()