2

I'm having an issue where the Y coordinate in a UTM is being rounded to the nearest 10th, and I don't know why.

I have a SQL Server Database with a view that is using columns from a table in another database through a linked server. This view has an extra SHAPE column in which I computer the POINT from the X and Y columns in table. I'm using this formula to create the Geometry,

geometry::STPointFromText('POINT(' + CAST(X_UTM_NAD83Z14_D AS VARCHAR(20)) 
                      + ' ' + CAST(Y_UTM_NAD83Z14_D AS VARCHAR(20)) + ')', 26914) AS SHAPE

The columns X_UTM_NAD83Z14_D and Y_UTM_NAD83Z14_D are float datatype

The problem was noticed when it was compared to original points and a temporary layer was made directly from the source table. The X direction was fine, but the Y direction was being rounded to the nearest tenth and thus shifting all the points north or south a maximum of 5 meters.

Original Coordinate: 335688.2, 6067867.1 PointFromText Coordinate: 335688.2, 6067870.0

Could someone help me figure this out? I don't understand where the error is coming from.

Improve this question
2
  • 2
    Does this error happen when using the STR function instead of cast? The description of the STR function is in this link: msdn.microsoft.com/en-us/library/ms189527.aspx
    – Fabien Ancelin
    Commented Jan 26, 2017 at 21:54
  • Thanks! That did the trick. STR(Y_UTM_NAD83Z14_D, 20, 2). Do you know why CAST had the behavior it did?
    – TSJ
    Commented Jan 26, 2017 at 23:10

1 Answer 1

Reset to default
3

I am not sure why, but this behaviour is documented on the official documentation: Cast may truncate your results:

From https://msdn.microsoft.com/en-us/library/ms187928.aspx:

Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

The function STR is designed for converting float expressions: https://msdn.microsoft.com/en-us/library/ms189527.aspx

STR ( float_expression [ , length [ , decimal ] ] )

From that article, it seems there are other options as well. I did not test them. http://www.connectsql.com/2011/04/normal-0-microsoftinternetexplorer4.html

Improve this answer
1
  • Thank you very much for pointing me to where I should have looked in the first place!
    – TSJ
    Commented Jan 27, 2017 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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