March 28, 2016 at 3:15 pm
I am using SS2014 and T-SQL.
I have two tables. Both have latitude and longitude. I added a field called [coordinates) of geography data type to both tables and populated both with geography coordinates using "geography::Point([Lat], [Lon], 4326)" method.
Now I want to match all the coordinates in Table A with the closest coordinate in Table B. Table A has about 300 records. Table B has about 1,600 records. While performance is not a major concern, I still don't want to wait minutes for the query to assign the nearest point to each point in table A.
I have found examples where one supplies 2 coordinates and out pops a distance, but I need to do this computation for more than one point ... I need to compute the nearest point for every point in Table A.
Would anyone have a link to an example on the web that would demonstrate this?
Thanks ahead of time for any help you can provide!
March 30, 2016 at 9:38 am
Taking a wild monkey stab in the pitch of darkness here.
I tried this but am unable to verify the data.
I tried matching the addresses in adventureworks2012.Person.address to its nearest address as per below to get a column and its nearest neighbour.
Runs like a snail on fire.
adapted from http://sqlblog.com/blogs/rob_farley/archive/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly.aspx
CREATE SPATIAL INDEX Spin_Address ON Person.Address(Spatiallocation);
WITH Mylocations
AS (SELECT *
FROM
Person.Address AS T)
SELECT L.Spatiallocation AS Mylocation
, L.Addressline1 AS Myaddressline1
, L.City AS Mycity
, A.Addressline1 AS Nearestaddressline1
, A.City AS Nearestcity
, A.Spatiallocation AS Nearestlocation
FROM
Mylocations AS L
CROSS APPLY (
SELECT TOP 1 *
FROM
Person.Address AS Ad
WHERE Ad.Addressid <> L.Addressid
--exclude current location. can be used to test if current location is returned when removed. should both be same.
ORDER BY L.Spatiallocation.Stdistance
(Ad.Spatiallocation
) ) AS A;
March 30, 2016 at 1:21 pm
I appreciate those who took time to look at this issue with me. In the meantime, I found a solution that was 98% of the exact code I needed.
It can be found at: https://alastaira.wordpress.com/2012/02/22/updating-a-sql-server-table-with-nearest-neighbours-optimised-for-sql-server-2012-sql-azure/
March 31, 2016 at 8:40 am
A long, long, long time ago, I wrote a chapter for Adam Machanic's book "Expert SQL Server 2005 Development". This chapter was all about working with spatial data, stored as latitude and longitude in the database.
After MS introduced the spatial datatypes in SQL Server 2008, I thought this work was now rendered useless. However, you mention that you have lat and lon so allow me to put in a quick plug for my work. 😉
In that chapter, one of the things I describe is how to find nearest neighbours, and I introduce a tuning technique called the "Dynamic Bounding Box" to limit the amount of rows that have to be checked. I don't own the content so I cannot share it with you, but perhaps you can find a copy of that books at a used books store. Or you can buy it at Amazon.com; new copies cost $39.95, but you can also check out the used copies Amazon.com offers.
Note that after the book went to print I found a small mistake in my code; a blog post describing the issue and a fix is here: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/06/27/The-bounding-box_2C00_-corrected-version.aspx.
March 31, 2016 at 11:12 am
Thanks for the information!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply