Sql-server – Minimum distance of a location; one table of points compared to a set of points SQL 2012

spatialsql serversql-server-2012t-sql

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.

Best Answer

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 ;
Related Question