Azure SQL geography::Point(latitude, longitude, 4326).Lat / Long returns the coordinate with a comma instead of a dot

Question

I'm currently experiencing the strangest behaviour (or at least I find it strange) with geographical queries on SQL Server in Azure. This happens in both SQL Server Management Studio(v18.8) and Visual Studio (2019).

When I run the following code:

DECLARE @latitude VARCHAR(50) = '52.1234567';
DECLARE @longitude VARCHAR(50) = '4.61234567';

SELECT geography::Point(@latitude, @longitude, 4326).Lat
SELECT geography::Point(@latitude, @longitude, 4326).Long
SELECT geography::Point(@latitude, @longitude, 4326).ToString()

I get the following results:

52,1234567
4,61234567
POINT (4.61234567 52.1234567)

As you can see It returns the Latitude and Longitude with a comma instead of a dot.
The ToString() function however does return the Latitude and Longitude as expected.

Does anyone have any idea on how to get the correct behaviour when using .Lat and .Long functions? i.e. getting a string with a dot and not a comma.

When I try to work around it (which I don't prefer) by replacing the ',' by a '.' I do get a string with a dot in stead of a comma but the string gets rounded off, which is undesirable because I need the location to be as accurate as can be.

REPLACE(geography::Point(@latitude, @longitude, 4326).Lat, ',','.');

returns

52.1235 instead of 52.1234567

Thanks in advance.

Regards, Raymond

Answer 1

It seems a known issue of Management Studio:

• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/255ad3c3-5cb2-4a1a-ad67-099253ee0cb4
• https://social.msdn.microsoft.com/Forums/en-US/29cafaa1-4f41-4ad0-ad00-5990877947f2

As a workaround, try casting to decimal type in your output statements, or use FORMAT:

declare @lat1 float = 52.1234567
declare @lat2 decimal(38,7) = 52.1234567

select @lat1 -- '52,1234567'
select @lat2 -- '52.1234567'
select cast(@lat1 as decimal(38,7)) -- '52.1234567'


DECLARE @latitude VARCHAR(50) = '52.1234567';
DECLARE @longitude VARCHAR(50) = '4.61234567';

SELECT geography::Point(@latitude, @longitude, 4326).Lat -- '52,1234567'
SELECT cast(geography::Point(@latitude, @longitude, 4326).Lat as decimal(38,7)) -- '52.1234567'
SELECT format(geography::Point(@latitude, @longitude, 4326).Lat, 'G') -- '52.1234567'

Answer 2

Hi @Raymond ,

Welcome to Microsoft Q&A!

I tried with different verisons of SSMS and could not reproduce your issue of returning with a comma instead of a dot.

What will 'select 1/2.0' resturn from your side? Is it 0,500000 or 0.500000?

If it is 0,500000, then it may be coming from the regional settings of your computer or Azure. How to set those will be dependent on your exact OS, but if you search in the Start menu for something like, "change number format" or "regional settings" you should hopefully be able to find it.

Besides,definitely don't try to use REPLACE to get around this. Display issues should be handled in the display layer whenever possible. If you have a float, treat it as a float, not a string.

If you insist to use replace, you could refer below and check whether it is working.

SELECT REPLACE(LTRIM(STR(geography::Point(@latitude, @longitude, 4326).Lat, 25, 10)), ',','.');  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.