CODEWITHSUNDEEP
sqlsql-serverfloating-point-precisionfloating-point-conversionsqlgeography
KrustyGString
KrustyGString

Reputation: 1013

Converting SQL Geography Lat/Long to VARCHAR without loss of precision

I have a geography column called Location. I need to SELECT the Location.Lat and Location.Long as a single VARCHAR result. Using the following I am losing precision:

Query:

SELECT CONVERT(VARCHAR(MAX), Location.Lat) + ' ' + CONVERT(VARCHAR(MAX), Location.Long)

Original Location.Lat: 52.2708633333333

Original Location.Long: -9.73093666666667

Result form above: 52.2709 -9.73094

I understand that this is due to Lat and Long being float and not having precision specified so possible workaround would be to force it to decimal first with a specified precision and then convert that to varchar. This seems.....clunky, and having to pick a random precision will force either

(a) loss of trailing values if it is too low or

(b) additional unnecessary trailing values.

Please tell me I'm missing a function or conversion method to return these as accurate varchar representations!

Upvotes: 5

Views: 4887

Answers (2)

KrustyGString
KrustyGString

Reputation: 1013

I guess I'll have to answer my own question here as it seems there is no functionality that meets the criteria.

To get desired results apparently need to use workaround like forcing it to decimal first with a specified precision and then convert that to varchar (Thanos answer provided sample of this if needed)

Upvotes: -1

Thanos Markou
Thanos Markou

Reputation: 2623

You need something like this:

SELECT CONVERT(VARCHAR(40),CAST(Location.Lat AS decimal(18,14))) + ' ' 
+ CONVERT(VARCHAR(40),CAST(Location.Long AS decimal(18,14)))

Upvotes: 8

Related Questions