4

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!

Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
KrustyGString
  • 903
  • 2
  • 13
  • 32
  • What *accuracy* did you originally measure the latitude and longitude to? Are you aware that at the fourth decimal place, you're accurate to ~10 metres? Do you really have measurements that are so much more precise such that you need to retain additional digits? – Damien_The_Unbeliever Jul 09 '15 at 08:13
  • Yeah, I think that 5 or 6 decimal places would be plenty accurate, but don't like hardcoding an arbitrary value which leads to loss of accuracy (no matter how minute, if it's in the geography value I'd like to use it) – KrustyGString Jul 09 '15 at 08:22

2 Answers2

8

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)))
Thanos Markou
  • 2,587
  • 3
  • 25
  • 32
-1

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)

KrustyGString
  • 903
  • 2
  • 13
  • 32
  • I think you should accept @Thanos answer instead of posting a new one. – SepehrM Nov 23 '16 at 10:33
  • 2
    I partially agree so will switch to accepting Thanos answer, but still undecided. I specifically asked if there was something so I wouldn't have to use a workaround like Thanos suggested, hence my own answer of "what I was looking for doesn't exist", but the workaround is useful so will go with it. – KrustyGString Nov 29 '16 at 15:43