Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server T-SQL
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 13:54pm
RedDk9.9K
Comments
Sergey Alexandrovich Kryukov at 7-Dec-12 21:43pm
   
Why?!
--SA

1 solution

Rate this: bad
good
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]								-- 
/*
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
  Permalink  
v3

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

  Print Answers RSS
0 OriginalGriff 562
1 Sergey Alexandrovich Kryukov 484
2 Maciej Los 325
3 DamithSL 233
4 Mathew Soji 195
0 OriginalGriff 7,168
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,461
3 Manas Bhardwaj 4,876
4 Maciej Los 4,450


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 10 Dec 2012
Copyright © CodeProject, 1999-2014
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