How to devide my Coordinates into longtitue and latitude in a SQL query

Question

I have the sql queries like this:

Select Userid, sum(cast(distanceinKM as int)) as KM, day from( 
SELECT [User].Userid
,[User].ulatitude, [User].ulongitude, [Checkpoint].clatitude, [Checkpoint].clongitutde,
(geography::Point([User].ulatitude, [User].ulongitude, 4326)).STDistance
(geography::Point([Checkpoint].clatitude, [Checkpoint].clongitutde, 4326))/100 as distanceinKM, cast(Timestamp as date) as day

FROM [User] INNER JOIN
Tablelog ON [User].Userid = Tablelog.Userid INNER JOIN
[Checkpoint] ON Tablelog.checkpointid = [Checkpoint].checkpiontid ) as distance 
where Day(day) = 6 AND MONTH (day) = 9 AND YEAR (day) = 2011 
group by userid , day

The function works well, but I have a problem with the coordinates. In my database my coordinates are stores this way: (55.56490549999999, 9.756640400000038) but in the query I have them divided into ulatitude and ulongtitude. So I was dividing them manually but I cannot do that anymore since I have a lot of data. What can I add in the query so I take the coordinates in this format (55.56490549999999, 9.756640400000038) and divide them .

Would you like to get the coordinates as `(55.56490549999999, 9.756640400000038)` being concatenated text value within a column result set of the query ? — , Jan 07 '15 at 23:42
They can be divided or concatenated but I need the function to work as it suppose to and I wont have to do anything manually — Amehiny, Jan 07 '15 at 23:45
Sounds like you need a simple string parse to parse the left and right coordinate. This can be done using CHARINDEX or PatternIndex with substring functions like LEFT RIGHT, and REPLACE to remove the parens. http://stackoverflow.com/q/17555755/84206 — AaronLS, Jan 08 '15 at 01:58

score 2 · Accepted Answer · 2015-01-08T01:49:14.787

2

UPDATED v-4

I've updated the query to fit the exact data format. Please try:

Select 
    distance.Userid, 
    sum(cast(distance.distanceinKM as int)) as KM, 
    distance.[day] 
from
    ( 
        SELECT
            UC_Lat_Long.Userid,
            UC_Lat_Long.ulatitude,
            UC_Lat_Long.ulongitude,
            UC_Lat_Long.clatitude,
            UC_Lat_Long.clongitutde, 
            (geography::Point(UC_Lat_Long.ulatitude, UC_Lat_Long.ulongitude, 4326)).STDistance(geography::Point(UC_Lat_Long.clatitude, UC_Lat_Long.clongitutde, 4326))/100 as distanceinKM, 
            UC_Lat_Long.[day] 
        FROM
        (
            SELECT 
                [User].Userid,
                CONVERT(NUMERIC(28,8), LTRIM(RTRIM(SUBSTRING(ISNULL([User].Coordinates, '0,0'), 1, CHARINDEX(',', ISNULL([User].Coordinates, '0,0')) - 1)))) AS ulatitude, 
                CONVERT(NUMERIC(28,8), LTRIM(RTRIM(SUBSTRING(ISNULL([User].Coordinates, '0,0'), CHARINDEX(',', ISNULL([User].Coordinates, '0,0')) + 1, LEN(ISNULL([User].Coordinates, '0,0')) - CHARINDEX(',', ISNULL([User].Coordinates, '0,0')) - 1)))) AS ulongitude, 
                CONVERT(NUMERIC(28,8), LTRIM(RTRIM(SUBSTRING(ISNULL([Checkpoint].coordinates, '0,0'), 1, CHARINDEX(',', ISNULL([Checkpoint].coordinates, '0,0')) - 1)))) AS clatitude, 
                CONVERT(NUMERIC(28,8), LTRIM(RTRIM(SUBSTRING(ISNULL([Checkpoint].coordinates, '0,0'), CHARINDEX(',', ISNULL([Checkpoint].coordinates, '0,0')) + 1, LEN(ISNULL([Checkpoint].coordinates, '0,0')) - CHARINDEX(',', ISNULL([Checkpoint].coordinates, '0,0')) - 1)))) AS clongitutde, 
                cast([Timestamp] as date) as [day]
            FROM 
                [User] 
                INNER JOIN Tablelog ON [User].Userid = Tablelog.Userid 
                INNER JOIN [Checkpoint] ON Tablelog.checkpointid = [Checkpoint].checkpiontid 
        ) AS UC_Lat_Long
    ) as distance 
where 
    Day(distance.[day]) = 6 
    AND MONTH (distance.[day]) = 9 
    AND YEAR (distance.[day]) = 2011 
group by 
    distance.Userid, 
    distance.[day]

edited Jan 08 '15 at 01:49

answered Jan 07 '15 at 23:47

What you did here is show the concatenate the coordinates. But what I need is the query to take the coordinates in this form (55.56490549999999, 9.756640400000038) and apply the query to it. In my database I have properties USER.COORDINATE = (55.56490549999999, 9.756640400000038) and checkpoint.coordinate= (55.50549999999, 9.7566404038). – Amehiny Jan 07 '15 at 23:56
But the query I have up requires me to divide them into ulongtitue and ulatitude. So the query above will not work if my coordinates are in this form (55.56490549999999, 9.756640400000038) so I need to apply a function to the users.coordinate to divide its value into ulatitue and ulongtitude or modify the query to accept the coordinate in this form (55.56490549999999, 9.756640400000038) – Amehiny Jan 07 '15 at 23:59
So simple explain I want to do this : User.Coordinate(latitude,longtittude) ==> [User].ulatitude, [User].ulongitude OR Modify the query to take the coordinates as User.Coordinate(latitude,longtittude) – Amehiny Jan 08 '15 at 00:04
See I don't have data in my database like this : [User].ulatitude, [User].ulongitude,[Checkpoint].clatitude, [Checkpoint].clongitutde, but instead I have this User.Coordinate = ( 55.5699, 9.75668) and Checkpoint.coordinate = ( 55.560099, 9.7500668) . So I want to calculate the distance between this two point User.Coordinate = ( 55.5699, 9.75668) AND Checkpoint.coordinate = ( 55.560099, 9.7500668). What should I do to make the query work? How should I split the User.Coordinate = ( 55.5699, 9.75668) into Ulong =55.5699 and Ulat = 9.7500668 ? Thank you for the help – Amehiny Jan 08 '15 at 00:16
Can you explain me what is this [User].Coordinate.Lat ? Can you divide the Users.coordinate = ( 55.560099, 9.7500668) in this way [User].Coordinate.Lat and [User].Coordinate.Log ?? Sample data: id | coordinates 1 (55.0000 , 55.1111) – Amehiny Jan 08 '15 at 00:35
[User].Coordinate.Lat the server says : Cannot Call method on varchar :/ the coordinate is type Varchar64 – Amehiny Jan 08 '15 at 00:39
Assuming that `[User].Coordinate` field is of type `geography` the `[User].Coordinate.Lat` extracts the latitude from this coordinates (like described here: **http://msdn.microsoft.com/en-us/library/bb933806.aspx**) and `[User].Coordinate.Log` extracts the longitude (like described here: **http://msdn.microsoft.com/en-us/library/bb933958.aspx**). – Jan 08 '15 at 00:40
aaaah ok - this is what I should have asked on the start - the data type of coordinates - is type of `Checkpoint.coordinates` also `Varchar64` ? – Jan 08 '15 at 00:41
Ohh that's great. I did not know that. Thank you . But the type of the coordinates is VARCHAR64 – Amehiny Jan 08 '15 at 00:42
Yes all of them are Varchar64 – Amehiny Jan 08 '15 at 00:43
I get this error: Invalid length parameter passed to the LEFT or SUBSTRING function. BTW Thank you so much for helping me – Amehiny Jan 08 '15 at 01:23
Maybe because there is no parenthesis is just like this : 55.565601,9.760001 – Amehiny Jan 08 '15 at 01:24
So sometimes there are parenthesis and sometimes not? Or always no parenthesis ? As for helping - no problem :) I hope you will achieve the goal. – Jan 08 '15 at 01:27
When I select to see from the database the coordinates are like this : 55.565601,9.760001 . So there is no parenthesis :/ I think – Amehiny Jan 08 '15 at 01:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68378/discussion-between-t-g-and-ahmed-h-mehiny). – Jan 08 '15 at 01:30

How to devide my Coordinates into longtitue and latitude in a SQL query

1 Answers1