Search code examples
sql-servert-sqlsubqueryssmsrdbms

Comparing distance between Latitude and longitude between two columns using T-SQL

Hi guys I am trying to compare the distance between a client and its respective server using Geography data in MSSQL.

I am able to successfully compare the distance between the two points. However The distance for only the last entry is being displayed.

Here is the Query I am using:

Declare @locations1 GEOGRAPHY;
Declare @locations2 GEOGRAPHY;
Declare @ServerID int;

SELECT @locations1 = GeoLoc from [TestDb].[dbo].[Locations] where IsClient =1;
SELECT @ServerID = ServerID from [TestDb].dbo.Locations where IsClient=1; 
SELECT @locations2 = GeoLoc from [TestDb].[dbo].[Locations] where IsClient =0 AND id=@ServerID ;

select @locations1.STDistance(@locations2)/1000 As [Distance in KM]

I believe the above query works as follows:

locations1 - has the GeoLocation of those columns where IsClient is True.
ServerId - has the ServerID of the columns where IsClient is True.
locations2 - has the GeoLocation of the column where IsClient is 0 and the ServerId obtained in above query matches the id of the machine.

So,
locations1 must have the GeoLocation of the Clients.
locations2 must have the GeoLocation of the respective servers.

My Table Design:

enter image description here

When I compare I am getting the correct result, but only the distance between the last client and server entry in the table. The distance of any other entry is not being displayed.

How do I get the distance of all the entries? Help needed:). Thank you.

Solution

  • looks like you need to do a self join, for which you always need to 'alias' the table names. something like

    SELECT l1.id,l2.id,l1.serverid, l1.geoloc.STDistance(l2.geoloc)/1000.0 As [Distance in KM] FROM
       [TestDb].[dbo].[Locations] l1 JOIN
       [TestDb].[dbo].[Locations] l2
            ON L1.IsClient = 1 AND L2.isClient = 0 AND L2.id = L1.ServerId

    so that would be, for each client, join to it's server record, as defined by l1.ServerId

    • l1 is the LEFT table and l2 is the right table.

    l1 and l2 are 'aliases' of your single table