1

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.

1

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
  • Well, that was embarrassingly simple. I'll try it now. Is that the right way round? I want every row in SourceData with just matching rows in NewLocations. I guess it is, just seems not selecting from newlocations, or does it not matter given it's a cross join? – Neil Walker Apr 9 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.