CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Richard Pianka
Richard Pianka

Reputation: 3356

Join to get row matching minimum value of function

I have two tables:

CREATE TABLE [dbo].[Gps] (
    [Date] [datetime] NOT NULL,
    [Latitude] [decimal](9, 6) NOT NULL,
    [Longitude] [decimal](9, 6) NOT NULL
)

CREATE TABLE [dbo].[Stops] (
    [StopName] [varchar](128) NOT NULL,
    [StopLat] [decimal](9, 6) NOT NULL,
    [StopLon] [decimal](9, 6) NOT NULL
)

I would like to get the closest Stop for each Gps entry using a Haversine() scalar function that I wrote with this signature:

CREATE FUNCTION [dbo].[Haversine]
(
    @aLatitude  DECIMAL(9, 6),
    @aLongitude DECIMAL(9, 6),
    @bLatitude  DECIMAL(9, 6),
    @bLongitude DECIMAL(9, 6)
)
RETURNS DECIMAL(16, 6)

Example:

SELECT dbo.Haversine(0.0, 0.0, 0.0, 0.0)

I would like to get both the StopName as well as the result of the Haversine function. How would I go about doing that efficiently?

Upvotes: 2

Views: 617

Answers (3)

valex
valex

Reputation: 24144

select [Date],Latitude,Longitude,StopName,StopLat,StopLon, dbo.Haversine(Latitude,Longitude,StopLat,StopLon) as distance 
INTO #Distances 
from GPS,STOPS;

select * from #Distances d
join 
(
select [Date],min(distance) minDist from #Distances group by [Date]
) B on (d.[date]=B.[date]) and (d.distance=b.minDist);

Note that This query can select multiple rows for one GPS position if it has equal minimum distance to for example two STOPS. The main performance issue is in the first query (creating temporary #Distance table).

I think you should optimize this query through WHERE statement to reduce output table. For example you can set say maximum allowed distance from GPS position to any STOP position and add WHERE dbo.Haversine(Latitude,Longitude,StopLat,StopLon)<MAX_ALLOWED_DISTANCE.

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Given you are using SQL Server 2008, I recommend storing your GPS and STOP points using the built in geography data type, and using the built in STDistance function to measure the distance.

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56735

This query should work:

SELECT  Gps.*, Stops.*
FROM    Gps
JOIN    Stops   
        ON (dbo.Haversine(Latitude, Longitude, StopLat, StopLon) 
            = (SELECT MIN(dbo.Haversine(Latitude, Longitude, s2.StopLat, s2.StopLon))
                FROM    Stops As s2
            ) )

But it's sure not going to be fast.

Here's another way that should work:

;WITH
  cte As
(
    SELECT  Gps.*, Stops.*,
            dbo.Haversine(Latitude, Longitude, StopLat, StopLon) As distance,
            MIN(dbo.Haversine(Latitude, Longitude, StopLat, StopLon))
                    OVER(Partition By Latitude, Longitude)       As minDistance
    FROM    Gps
    CROSS JOIN
            Stops
)
SELECT  *, distance
FROM    cte
WHERE   distance = minDistance

Still just as slow though.

Upvotes: 0

Related Questions