I am trying to find the quickest way to find the minimum distance of one point to a table of points. The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K single points.

Or better explained Table A has 150K rows/points, Table B 1500 points. I want to know for every row in Table A what is the minimum distance from all those listed in Table B.

I have a function that does the distance calc, as an added column to the Table A. It just takes a VERY LONG time. Table B has a spatial index.

This is what I have:

select a.*, 
       dbo.fxn_distance(geography::STPointFromText('POINT(' + 
       CAST([Long] AS VARCHAR(20)) + ' ' + CAST([Lat] AS 
       VARCHAR(20)) +    ')', 4326)) as DistAway
from Table A a

my function:

create function fxn_distance
(@pointTableA geography
returns float 

declare @distance float

select top 1 @distance = b.GeoLocation.STDistance(@pointTableA) 
from TableB b  
where geolocation.STDistance(@pointTableA) is not null
order by geolocation.STDistance(@pointTableA)

return @distance

Sorry if I am totally a newbie to this, and I know the solution is probably simple, but I just can't wrap my head around this. So to hopefully clarify: I need to pass the lat/long of every point in Table A and see what the min distance is compared to every row in Table B, for every row in Table A. I care what the actual distance away is, but not the actual point from Table B.

Any help appreciated.

1 Answer 1


You may have better performance if you convert the function into a table-valued-function.

Here, I setup the test-bed:

USE tempdb;
    LAT DECIMAL(10,5)
    , LON DECIMAL(10,5)

    Geolocation GEOGRAPHY NOT NULL

Here is the table-valued-function, which is basically your function except this returns a table.

CREATE FUNCTION dbo.fxn_distance
    @pointTableA GEOGRAPHY
returns table
as return 
    SELECT TOP 1 Distance = b.GeoLocation.STDistance(@pointTableA) 
    FROM TableB b  
    WHERE geolocation.STDistance(@pointTableA) IS NOT NULL
    ORDER BY geolocation.STDistance(@pointTableA)

Insert a simple test-row into each of the two tables:

VALUES (49.0,170.0);

INSERT INTO dbo.TableB(Geolocation)
VALUES (geography::STGeomFromText(
    'LINESTRING(-122.360 47.656, -122.343 47.656)', 4326)

The query that uses the inline TVF to determine the closest point:

    , d.Distance --This is the distance calculated by the TVF. 
FROM dbo.TableA a
CROSS APPLY dbo.fxn_distance(geography::STPointFromText('POINT(' + 
       CAST(A.LON AS VARCHAR(20)) + ' ' + CAST(A.LAT AS 
       VARCHAR(20)) + ')', 4326)) d ;
  • how can I show the min distance as a column from the new function? Nov 13, 2015 at 20:48
  • 2
    The TVF evaluates the Distance once for each row in the source table, TableA, whereas running a regular function in the select clause can cause the function to be evaluated many times per row.
    – Hannah Vernon
    Nov 13, 2015 at 21:55
  • 2
    @user3347256 UDF scalar functions in SQL Server are notoriously slow. Put "scalar udf performance" into your favorite search engine and you can find a plethora of information about it if the cliff notes version that Max just provided isn't enough to satisfy your curiosity.
    – Erik
    Nov 13, 2015 at 21:58
  • 1
    @MaxVernon, what is the DistanceTable type for in this please? I can't see anything referencing it. Nov 15, 2015 at 23:20
  • 1
    The first version ran something in 2.5 hours vs the 36 hours it took before. Thanks @MaxVernon! In the updated version, is there any benefits to eliminating the user created type Nov 16, 2015 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.