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?

learn.microsoft.com/en-us/sql/t-sql/spatial-geography/… This function requires a binary argument and an SRID. Something does not add up with your source data. — Jacob H
– Jacob H, Commented May 18, 2017 at 14:02
It goes as a .csv file through a mapping service to insert the value along with longitude and latitude. From there, it ends up in a imports table as a string value. — zberg007
– zberg007, Commented May 18, 2017 at 14:05

Antonio · Accepted Answer · 2017-05-18 14:18:06Z

4

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()

edited May 18, 2017 at 14:18

answered May 18, 2017 at 14:12

Antonio

53310 silver badges27 bronze badges

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

4 Comments

zberg007 Over a year ago

What am I missing? I am trying to use your code above as

BEGIN TRANSACTION 			WHILE ( @ROWCOUNTER <= @ROWCOUNT )             BEGIN                 DECLARE @location varchar(max) 				DECLARE @binlocation varbinary (max)=CONVERT(varbinary(max), @location) 				DECLARE @b geography 				SET @b=@binlocation 				 /* Insert Ship To Addresses */ 				INSERT INTO dbo.Addresses 				( 					,Location 					,Longitude 					,Latitude 				) 				SELECT  					,@b.ToString() 					,Convert(Float, I.Longitude) 					,Convert(Float, I.Latitude) 				FROM dbo.ImportsCustomers I 				WHERE I.Id = @ID;

zberg007 Over a year ago

it imports without error, however the location field in the Addresses table is NULL, so it's not populating with data. It's hard to read above, but here is what I have:

DECLARE @location varchar(max) 				DECLARE @binlocation varbinary (max)=CONVERT(varbinary(max), @location) 				DECLARE @b geography 				SET @b=@binlocation

Antonio Over a year ago

any news on this??

zberg007 Over a year ago

Yes, I actually ended up using the latitude and longitude values to re-create the location (SRID) value of each row on import to the Addresses table. The code I used was this: geography::STGeomFromText('POINT('+I.Longitude+' '+I.Latitude+')', 4326) and it works like a charm. I could not get the existing Location value to happily convert to the geograpghy data type that was needed. Most likely something I was doing wrong, just couldn't figure it out at the time. Thank you Antonio, your example led me down the path to discovering my solution.

Collectives™ on Stack Overflow

Convert string value to Geography data type in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related