11

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

  • It looks like WKB (Well Known Binary) there is an Esri description edndoc.esri.com/arcsde/9.1/general_topics/… but the format is OGC (Open Geospatial Consortium) I would think it would be a bit easier to use the builtin functions as described on the provided link or one listed msdn.microsoft.com/en-AU/library/bb933960.aspx I think the problem lies in that you've imported a hex string as text and not actually as binary - but I can't help there I haven't had much to do with the backend of SQL. Perhaps you could ask on Super User or DB Admin on stackexchange. – Michael Stimson Sep 2 '15 at 21:40
  • @Peter did you ever find any of these a solution for your issue? – DPSSpatial Jan 15 '16 at 22:57
9

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)')
6

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:

enter image description here

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

enter image description here

  • 1
    The SRID is built into the Geometry binary. The binary that is supplied is not WKB, but the actual SQL Servery binary representation of the Geometry. – MickyT Sep 2 '15 at 22:35
  • @MickyT ah yes! I will post one more snippet to test... Thank you!!! – DPSSpatial Sep 2 '15 at 22:39
  • Would it be possible to add a Geometry column to the original table and update that column with the Geometry? – Peter Horsbøll Møller Sep 3 '15 at 7:58
  • 1
    @PeterHorsbøllMøller I think that's the right move... once it's converted. I will work on that and post later on. – DPSSpatial Sep 3 '15 at 12:52
2

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(100),
  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
  • looks good!!! Good to have all this documented... it'll come in handy one day! – DPSSpatial Sep 3 '15 at 16:21
  • this did come in handy - a fellow down in our BI department independently found this post and used it to get SQL Server Integration Services (SSIS) to pass geometry back and forth between servers, which at this point is not found natively in SSIS!!! – DPSSpatial Oct 15 '15 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.