Sql-server – SQL to find nearest location in one table from locations in another

spatialsql server

I have a table like this, that contains a few million locations (POINT):

CREATE TABLE NewLocations(
    [NewName] [varchar](20),
    [NewLocation] [geometry] 
)

I have another table like this, that contains a few hundred existing locations (POINT):

CREATE TABLE SourceData(
    [OriginalName] [varchar](20),
    [originalLocation] [geometry]
)

What I want to do is construct a sql select that for every row in SourceData find an entry(ies) in NewLocations that is within a few metres, in other words I'm trying to find a NewName either in the same place or near enough the same place as each originalname in SourceData.

e.g. 

Name    NewName
loc1    loc56
loc2    NULL

I'm not bothered if there are multiple rows, I can remove them later, and there need not be a match.

With a single created point I can call STDistance and find a match, but I don't quite know how to construct the SQL for this to work with the table. I guess I could also turn it into a function, but I figured there might be a way to do it wholly in sql.

thanks for any help.

Best Answer

construct a sql select that for every row in SourceData find an entry(ies) in NewLocations that is within a few metres

Something like:

select *
from NewLocations n
cross join SourceData s
where originalLocation.STDistance(NewLocation) < 5
Related Question