Spatial Data: finding the closest point to a geographical point. Populating a distance field in a table.

  • 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!

  • 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;

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • 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/

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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