Divide the coordinates into longtitue and latitude

Question

I have coordinates in my database stored as (55.573012889640765, 9.72362365248182). I want to make a function that will get this value and put them on @latitude = 55.573012889640765 and @long=9.72362365248182.

So practically the function gets the coordinates and returns me the two points separately . I want to get this so I can calculate the distance between two points later on with a function like this one:

CREATE FUNCTION dbo.fnCalcDistanceKM(@lat1 FLOAT, @lon1 FLOAT, @lat2 FLOAT, @lon2 FLOAT)
RETURNS FLOAT 
AS
BEGIN

    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

As you see this function requires the points to be separated and in my database I have them as one . Can you please le me know how to divide the parts or modify the above function to fit my code.

Thank you in advance

score 5 · Accepted Answer · edited May 23 '17 at 12:20

You can save yourself some effort by using SQL's geometry functions.

declare @coords1 nvarchar(64) = '(55.573012889640765, 9.72362365248182)'
, @coords2 nvarchar(64) = '(56.573012889640765, 9.72362365248182)'

declare @g1 geometry = geometry::STGeomFromText('POINT' + replace(@coords1,',',' '), 0)
, @g2 geometry = geometry::STGeomFromText('POINT' + replace(@coords2,',',' '), 0)
SELECT @g1.STDistance(@g2);

More info here: http://msdn.microsoft.com/en-us/library/bb933952.aspx

Alternatively, if you're just looking to split the string around the comma, take a look at this example: How do I split a string so I can access item x?

Martin Brown · Answer 2 · 2014-12-18T23:25:35.840

I prefer JonLBevan's answer but this is literally what was asked for:

DECLARE @string nvarchar(max) = '(55.573012889640765, 9.72362365248182)';

SELECT @string;

DECLARE @latitude float;
DECLARE @long float;

SELECT @latitude = CONVERT(float, SUBSTRING(@string, 2, charindex(',', @string)-2)),
       @long = CONVERT(float, SUBSTRING(@string, charindex(',', @string)+1, LEN(@string)-charindex(',', @string)-1));

SELECT @latitude, @long;

It is worth pointing out though, that it would be better not to store the values in this way as you are unable to take any advantage of indexing when doing range searches due to the functions involved.

Divide the coordinates into longtitue and latitude

2 Answers2