How can I convert geometry (binary) to nvarchar usg T-SQL ... cut&paste example to follow

Question

1.00/5 (1 vote)

See more:

The problem is I can't find ANY method to provide, SELECT query or PRINT or otherwise, a string of characters as a plain string of characters.

USE[master]
GO

CREATE SCHEMA [DCST]

CREATE TABLE [DCST].[geoGeo](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[GeogCol1] [geometry] NULL,
	[GeogCol2]  AS ([GeogCol1].[STAsText]())
) ON [PRIMARY]


INSERT INTO [DCST].[geoGeo](GeogCol1)
	VALUES(geometry::STPointFromText('POINT(-73.57529478233685 45.50986635357582)',4326))
INSERT INTO [DCST].[geoGeo](GeogCol1)	
	VALUES(geometry::STPointFromText('POINT(149.1389139479469 -35.39418756346831)',4326))

SELECT [idx]
      ,[GeogCol1]
      ,[GeogCol2]
  FROM [master].[DCST].[geoGeo]
GO

Sets up the data on [master]

/*

idx	GeogCol1					GeogCol2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	0xE6100000010C8FEC34A1D16452C02FF8F84C43C14640	POINT (-73.575294782336854 45.509866353575823)
2	0xE6100000010C7CECA9FB71A462400CCBF2BC74B241C0	POINT (149.13891394794689 -35.394187563468307)

*/

And the steps to understanding the problem is in the tests:

DECLARE @r geometry
SET @r = (SELECT [GeogCol1] From [master].[DCST].[geoGeo] WHERE [idx] = 1) 
PRINT CONVERT([nvarchar](1024),@r)						-- (messages) "POINT (-73.575294782336854 45.509866353575823)"
SELECT @r.STAsText()				        			-- POINT (-73.575294782336854 45.509866353575823)
SELECT @r.STAsBinary()								-- 0x01010000008FEC34A1D16452C02FF8F84C43C14640

DECLARE @rs geometry
SET @rs =  geometry::STGeomFromWKB(@r.STAsBinary(),0)
SELECT @rs.STAsText()								-- POINT (-73.575294782336854 45.509866353575823)
PRINT @rs.STAsText()								-- (messages) "POINT (-73.575294782336854 45.509866353575823)"
PRINT CAST(@rs.STAsText() As binary)						-- (messages) "0x50004F0049004E005400200028002D00370033002E003500370035003200"							
SELECT CAST(@rs.STAsText() As binary)	
DECLARE @binstr [nvarchar](MAX)
SET @binstr = (SELECT CAST(@rs.STAsText() As binary))
PRINT @binstr

Now, the thing is ... this end "@binstr"

-- (messages) "POINT (-73.5752" (why is this truncation occuring ? ... and collateral question, answer of which was the original question waylaid by this little discovery of a truncation here, how can I get a simple string of characters out of this binary reprentation known as geometry?!!)

(that linefeed of the comments makes this columnar stuff hard to read but please bear with it because the line merits explanation)

Posted 7-Dec-12 12:54pm

RedDk

Add a Solution

Comments

Sergey Alexandrovich Kryukov 7-Dec-12 21:43pm

Why?!
--SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Accepted Answer · 2012-12-10T09:50:00

Figured out why the truncation is occuring. Here posting the results as the answer to the problem; my use of [nvarchar] to contain data of type [varbinary].

As an example:

SELECT CAST('0x50004F0049004E00' As binary)															-- 0x307835303030344630303439303034453030000000000000000000000000  ( ascii code: "0x50004F0049004E00NULNULNULNULNULNULNULNULNULNULNULNUL")
SELECT CAST('0x50004F0049004E005400200028002D003100320032002E00340031003500' As binary)				-- 0x307835303030344630303439303034453030353430303230303032383030
SELECT CAST('0x50004F0049004E00' As [nvarchar](128))												-- 0x50004F0049004E00
SELECT CAST('0x50004F0049004E005400200028002D003100320032002E00340031003500' As [nvarchar](MAX))	-- 0x50004F0049004E005400200028002D003100320032002E00340031003500
SELECT CAST(CAST(@rs.STAsText() As binary) As [nvarchar](MAX))										-- POINT (-134.00
SELECT CAST(CAST(@rs.STAsText() As binary) As binary)												-- 0x50004F0049004E005400200028002D003100320032002E00340031003500
DECLARE @regstr [nvarchar](MAX)
SET @regstr = CAST(CAST(@rs.STAsText() As binary) As binary)
SELECT @regstr As [binaryStr]
/* and the conceptual impasse solution */
SELECT CAST(CAST(@rs.STAsText() As binary) As binary) As [binaryStr]								-- 
/*
binaryStr
0x50004F0049004E005400200028002D003100320032002E00340031003500
*/
SELECT CAST(CAST(@rs.STAsText() As binary) As [varbinary](MAX))        --  0x50004F0049004E005400200028002D003100320032002E00340031003500

(In order to better see the code in these PRE tags click on the "Copy Code")

So, in conclusion ... I CAN get the binary string @binstr by using the right type(d'oh)!, [varbinary](MAX). The operation in the SELECT clause returning the abbreviation as an unknown conversion is some sort of default behavior. Unexpected but not necessarily as wrong as redtext error.

Even more info:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/65353777-e3e6-461c-9504-ea4d83312692.htm