4

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.

3

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? – user3347256 Nov 13 '15 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. – Max Vernon Nov 13 '15 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 '15 at 21:58
  • 1
    @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
  • 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 – user3347256 Nov 16 '15 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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