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