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
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.