# Minimum distance of a location; one table of points compared to a set of points SQL 2012

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
as
begin

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
end
``````

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.

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

Here, I setup the test-bed:

``````USE tempdb;
CREATE TABLE dbo.TableA
(
LAT DECIMAL(10,5)
, LON DECIMAL(10,5)
);

CREATE TABLE TableB
(
Geolocation GEOGRAPHY NOT NULL
);
GO
``````

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)
)
GO
``````

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

``````INSERT INTO dbo.TableA(LAT, LON)
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:

``````SELECT a.*
, 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? – Robert Sparrow Nov 13 '15 at 20:48
• 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. – Max Vernon Nov 13 '15 at 21:55
• @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 '15 at 21:58
• @MaxVernon, what is the DistanceTable type for in this please? I can't see anything referencing it. – Mister Magoo Nov 15 '15 at 23:20
• 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 – Robert Sparrow Nov 16 '15 at 13:58