0

As you can see, my geography "POINT" does not contain all the bytes from lat and long. It is rounding my lat and long. I have defined GeoText as nvarch(max). This needs to be precise.

Here is my code:

select [GeoLocation].STAsText() as GeoText, [lat], [long]
FROM [Temp].[dbo].[table1]
order by [GeoLocation].STAsText();

My results:
GeoText                     lat          long
POINT (-106.734 35.0806)    35.080571   -106.7339
POINT (-106.734 35.0806)    35.080571   -106.7339
POINT (-106.734 35.0806)    35.080571   -106.7339
POINT (-106.779 32.3107)    32.31071    -106.77931
POINT (-106.779 32.3107)    32.31071    -106.77931
POINT (-107.134 38.4738)    38.47382    -107.1337
POINT (-107.659 37.8162)    37.816161   -107.65926
POINT (-107.659 37.8162)    37.816161   -107.65926
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Irisheyes169
  • 1
  • 1
  • What do you get if you select `[GeoLocation].Latitude` and `[GeoLocation].Longitude` from the SqlGeography column rather than your separate numeric columns? – canon Jul 13 '17 at 20:47

1 Answers1

0

Following SQL:

DECLARE @Lat FLOAT= 35.080571;
DECLARE @Long FLOAT= -106.7339;

SELECT CAST(CAST(@Lat AS DECIMAL(20, 6)) AS VARCHAR)+' '+CAST(CAST(@Long AS DECIMAL(20, 6)) AS VARCHAR) Point,  @Lat Lat, @Long Long;

Will result as:

Result

Make sense? You can use same in your sql.

ViKiNG
  • 1,294
  • 2
  • 19
  • 26
  • Now that I am looking at it closer @Viking it is adding zero's to the end of the lat and long, if there is only 5 digits after the decimal. I wonder if I can just use concat. I want to do a group by on the "point". SELECT Concat([lat],' ',[long]) as Point, lat, long from [Temp].[dbo].[Table1]; – Irisheyes169 Jul 14 '17 at 19:53
  • https://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server – ViKiNG Jul 15 '17 at 00:01
  • Otherwise, you need to use a combination of CASE and DECIMAL. Like CASE WHEN LEN(of decimal part is 6) THEN DECIMAL(20,6), OR DECIMAL(20,5) etc. – ViKiNG Jul 15 '17 at 00:13