How to convert from SQL Server geometry BLOB to something else?

Question

I have a CSV data file from a 3rd party that is an exported SQL Server table. They simply did a select * from foo and output the result to a text file and sent it over.

In their table is column of Geometry type, so in my raw text I have something like "0xE610000010C47...", etc. At the moment I've loaded it into a table in SQL Server as an nvarchar.

I anticipated I would be able to reverse back into a Geometry field on my end, but that doesn't seem to be so easy. STGeomFromWKB doesn't work because it's not actually a WKB. I can't cast the string as Geometry because it complains that it's not a WKT.

So, is there any way I can get this value into SQL Server as if it is a normal Geometry BLOB? Can I tell SQL Server to treat it as such?

I found this link which helped at least answer my question about what is in SQL Server, but didn't get me all the way there: what is the format of Geometry data type of SQLServer 2008

Answer 1

When you import the data into SQL Server, put it into a VARBINARY(MAX) column. You should then be able to CAST this as a Geometry or Geography as required. You will need to be careful that the string 0xE6 ... is not changed during the import.

Another option is to do a dynamic query to get the selection. I put a couple of conversion examples below.

-- As a varchar and binary
DECLARE @NV AS NVARCHAR(MAX) = '0xE610000001040E0000002AA57BA76F5446C027B8FD971024654007517714795446C00C42473D1624654094CFCDE3795446C0D3F576B91624654068507E9C7A5446C0D7BE805E18246540F2FC444A7C5446C0E93DDF6019246540B4CA4C697D5446C0D5DF5C0D1A2465401A0923AC7C5446C08183DE3F1A2465407599E1907B5446C0E2A8818B1A24654058B4805A7A5446C0A70261DE1A24654089729F5C775446C072080BDD1B2465401AAC24A8745446C057CB10FE1C2465403D16A5446E5446C05A20FEE21A246540658BA4DD685446C00907FFA3172465402AA57BA76F5446C027B8FD971024654001000000020000000001000000FFFFFFFF0000000003' 
DECLARE @NB AS VARBINARY(MAX) = 0xE610000001040E0000002AA57BA76F5446C027B8FD971024654007517714795446C00C42473D1624654094CFCDE3795446C0D3F576B91624654068507E9C7A5446C0D7BE805E18246540F2FC444A7C5446C0E93DDF6019246540B4CA4C697D5446C0D5DF5C0D1A2465401A0923AC7C5446C08183DE3F1A2465407599E1907B5446C0E2A8818B1A24654058B4805A7A5446C0A70261DE1A24654089729F5C775446C072080BDD1B2465401AAC24A8745446C057CB10FE1C2465403D16A5446E5446C05A20FEE21A246540658BA4DD685446C00907FFA3172465402AA57BA76F5446C027B8FD971024654001000000020000000001000000FFFFFFFF0000000003

-- Failing conversions
SELECT CAST(@NV AS Geometry)
SELECT CAST(CAST(@NV AS VARBINARY(MAX)) AS Geometry)
-- Correct conversion
SELECT CAST(@NB AS Geometry)
EXEC('SELECT CAST(' + @NV + ' AS Geometry)')

Answer 2

Building on @MickyT's answer, since you're going to have a table with your values already sitting in WKB (or whatever we're calling it), you would want to write sql that will convert all records to geometry, rather than having to declare a variable, etc. etc.

So if you start off with a simple temp table that would replicate the WKB in one record, it would look like this:

select 0x3D0B0000010C80BAE380AE064841005149CD6EFD3941 as wkb

Now if you treat that as a temp table and wrap some SQL around it, you have a column with the WKB in there and you can convert it to varbinary as suggested above:

select 
cast(temp.wkb as varbinary(max)) as geombinary
from 
(
select 0x3D0B0000010C80BAE380AE064841005149CD6EFD3941 as wkb
) as temp

where temp.wkb can be the column in your larger table containing the WKB values from the CSV

Finally, use the method described by MickyT and convert the varbinary to geometry:

select 
cast(geomconvert.geombinary as geometry) as geom
 from (
select 
cast(temp.wkb as varbinary(max)) as geombinary
from 
(
select 0x3D0B0000010C80BAE380AE064841005149CD6EFD3941 as wkb
) as temp
) as geomconvert

Which returns geometry and a spatial result:

EDIT where does the SRID get declared? As MickyT answered, it's in the binary, and you can wrap 1 more sql query around there to test:

select top 1 getsrid.geom.STSrid from (

select 
cast(geomconvert.geombinary as geometry) as geom
 from (
select 
cast(temp.wkb as varbinary(max)) as geombinary
from 
(
select 0x3D0B0000010C80BAE380AE064841005149CD6EFD3941 as wkb
) as temp
) as geomconvert

) as getsrid

which yields, and is correct in my example, 2877 (colorado state plane):

Answer 3

Original poster here, when I tried to finish signup it didn't link the signin for the original post. Anyway....

Thanks for all the help! I'll upvote each answer once I'm able to and maybe if I can figure out how to link this account and the original one I can mark an answer. Also, after your pointers I can't believe I missed using CONVERT instead of CAST. It makes it much easier.

I think my main problem was getting the raw binary "string" to something I could use. Here's a sample of how I solved it:

DECLARE @data TABLE (
  ID nvarchar(1024),
  ImportedGeometry nvarchar(max),
  FinalGeometry geometry
  )

  INSERT INTO @data (ID, ImportedGeometry) values ('1', '0xE6100000010C4703780B24B855C061C3D32B65093540')
  INSERT INTO @data (ID, ImportedGeometry) values ('2', '0xE6100000010C96438B6CE7D359C0BD5296218E853440')

select 
d.ID,
d.ImportedGeometry,
CONVERT(varbinary(max), d.ImportedGeometry, 1) as ConvertedGeometryBin,
(cast(CONVERT(varbinary(max), d.ImportedGeometry, 1) as geometry)) as FinalGeometry
from @data d

UPDATE @data
SET FinalGeometry = (cast(CONVERT(varbinary(max), ImportedGeometry, 1) as geometry))

select 
d.ID,
d.FinalGeometry,
d.FinalGeometry.STAsText(),
d.FinalGeometry.STSrid
from @data d