Click here to Skip to main content
13,559,184 members
Rate this:
Please Sign up or sign in to 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.



	[idx] [int] IDENTITY(1,1) NOT NULL,
	[GeogCol1] [geometry] NULL,
	[GeogCol2]  AS ([GeogCol1].[STAsText]())

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]
  FROM [master].[DCST].[geoGeo]

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

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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


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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.180515.1 | Last Updated 10 Dec 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100