question

Raymond-6872 avatar image
0 Votes"
Raymond-6872 asked ·

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

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


sql-server-transact-sqlazure-sql-database
· 4
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Raymond-6872 We are checking this but we have added the tag for Transact SQL as well for better traction with this issue.

0 Votes 0 ·

Thanks for looking into it.

Kind regards, Raymond

0 Votes 0 ·

What does 'select 1/2.0` return

0 Votes 0 ·

Hi David,

Thanks for the reply. The result of your proposed query is: 0.500000

Kind Regards, Raymond

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @Raymond-6872,

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.

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Melissa,

Thanks for the warm welcome.

When i run the 'SELECT 1/2.0' query the result is 0.500000

As a workaround (which i preferably don't want to use) i'm currently using the following:

REPLACE(CONVERT(DECIMAL(10,8), geography::Point(@latitude, @longitude, 4326).Lat), ',','.')

This does return the latitude with the accuracy I need.

Kind regards, Raymond


0 Votes 0 ·

Hi @Raymond-6872,

Thanks for your update.

Glad that you have got your solution.

Please post your answer and accept it or accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

It seems a known issue of Management Studio:

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'


· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

`Thanks for the information Viorel, i'll look into that.

And i appreciate the workaround examples.

I was already using the following workaround:

REPLACE(CONVERT(DECIMAL(10,8), geography::Point(@latitude, @longitude, 4326).Lat), ',','.')

Which does provide the correct format + accuracy, however I would rather just see the .Lat function return the correct value without a workaround.

Kind regards, Raymond




0 Votes 0 ·

Probably REPLACE is not necessary.

0 Votes 0 ·

You're absolutely right Viorel, can't believe I didn't even test that myself...

0 Votes 0 ·