Update one table from another table joined on minimum differences

Question

Update one table from another table joined on minimum differences

Charlottecb

Hall of Fame

Points: 3932
More actions
September 21, 2006 at 3:32 am

#114953

Hi All, (SQL Server 2000)
Could someone please help me with the following problem.
I have two tables tblTree and tblMapAddress.

tblTree
intTreeID int (key)
intX int
IntY int
strAddressLine1 varchar(50)

tblMapAddress
intAddressID int (key)
intXcoordinate int
intYcoordinate int
strAddressLine1 varchar(50)

I need to update the strAddressLine1 in tblTree with the closest address (strAddressLine1) from tblMapAddress.
This needs to be done in the following way…
I need to compare the intX and intY valus in the tblTree table and get the closest match with the intXcoordinate and intYcoordinate from tblMapAddress table. Any of the four values compared could be a negative number, so they would need to be converted temporarily to a positive number during the matching phase. I can do this by squaring it then square rooting it (but there may be a better way). If there is more than one address which has a minimum difference, I only need to take one (it does not mater which so long as if the process is re-run, I get the same result).

I am hoping that this can be done without using cursers by some sort of join on the minimum values.
Any help would be very much appreciated.

Thanks
CCB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

If you want to have all numbers positive, you can use ABS (<column&gt function. As to the rest, unfortunately I don't have time to analyze the requirement (and fortunately never needed to do somthing like that), so let's hope you'll get some help from other people.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

This looks like geocodes although float would be a better data type.

With geocodes Pythagoras’ therom does not work very well away from the equator. (It fails completely at the poles!)

Spherical trigonometry gives reasonable results and the math is not too complicated. I found the following GreatCircleDistance function on this site some time ago:

CREATE FUNCTION dbo.GreatCircleDistance

(@Latitude1 float = NULL,

@Longitude1 float = NULL,

@Latitude2 float = NULL,

@Longitude2 float = NULL)

RETURNS float

AS

BEGIN

IF @Latitude1 IS NULL RETURN 0

IF @Longitude1 IS NULL RETURN 0

IF @Latitude2 IS NULL RETURN 0

IF @Longitude2 IS NULL RETURN 0

RETURN (ACOS(SIN(@Latitude1 * PI() / 180) * SIN(@Latitude2 * PI() / 180) +

COS(@Latitude1 * PI() / 180) * COS(@Latitude2 * PI() / 180) * COS((@Longitude2 - @Longitude1) * PI() / 180)) * 3963.1)

END

Ellipsoids give more accurate results but the math becomes complicated. Given that you are holding the co-ordinates as integers it seems you do not need to be too accurate.

Assuming the geocodes in tblTree are within a few miles of the ones in tbleMapAddress, and the addresses are not too near the poles, I would be inclined to try something like:

create table #temp

(

TreeID int not null

,Dist float not null

,AddressLine1 varchar(50) not null

)

insert #temp

select T.intTreeID

,dbo.GreatCircleDistance(T.intY, T.intX, M.intYcoordinate, M.intXcoordinate) as Dist

,M.StrAddessLine1 as AddessLine1

from tblTree T

join tblMappAddress M

-- restrict the join at a reasonable level

-- the more it is restricted the quicker it will be

on T.intX between M.intXcoordinate - 1 and M.intXcoordinate + 1

and T.intY between M.intYcoordinate - 1 and M.intYcoordinate + 1

update T

set strAddressLine1 = isnull(D.AddressLine1, 'Outside range')

from tblTree T

left join (

select T1.TreeID, min(T1.AddressLine1) as AddressLine1

from #temp T1

join (select T2.TreeId, min(T2.Dist) as Dist

from #Temp T2

group by T2.TreeID)

D1 on T1.TreeID = D1.TreeID and T1.Dist = D1.Dist

group by T1.TreeID )

D on T.intTreeID = D.TreeID

This has not been tested but it should give you a starting point.

Good luck...

ps I assumed you are holding geocodes as degrees. If they are in, say, thousandths of a degree you will need to +/- 1000 instead of 1 in the join and divide by 1000 on the call to dbo.GreatCircleDistance().

Charlottecb Hall of Fame Points: 3932 More actions · Answer 3

Hi Ken,

Thanks very much for your reply.

It almost works except the insert bit generates a "A domain error occurred." message - If I remove the call to the UDF and put a hardcoded value in its place it will work. Any suggestions ? If I run the Select bit without the insert with the UDF in place it works.

One thing I don't understand about it is that if I have 10 trees each with an X and Y of 1 - 10, and 10 addresses each with an X and Y of 1 - 10, nothing is returned for trees 9 and 10. Any suggestions here?

Thanks for your patience.

Regards

CCB

Ken McKelvey SSCoach Points: 18842 More actions · Answer 4

Not sure about the insert but could try creating the temp table with default db collation.

create table #temp

(

TreeID int not null

,Dist float not null

,AddressLine1 varchar(50) collate database_default not null

)

I do not know about the results. You could try checking the join conditions.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 5

Just managed to have a quick look at this.

The problem seems to be in the function where rounding can cause the cosine to be greater than 1 when the co-ordinates are the same. Try:

CREATE FUNCTION dbo.GreatCircleDistance

(

@Latitude1 float = NULL,

@Longitude1 float = NULL,

@Latitude2 float = NULL,

@Longitude2 float = NULL

)

RETURNS float

AS

BEGIN

IF @Latitude1 IS NULL RETURN 0

IF @Longitude1 IS NULL RETURN 0

IF @Latitude2 IS NULL RETURN 0

IF @Longitude2 IS NULL RETURN 0

DECLARE @cosine float

SET @cosine = SIN(@Latitude1 * PI() / 180) * SIN(@Latitude2 * PI() / 180)

+ COS(@Latitude1 * PI() / 180) * COS(@Latitude2 * PI() / 180)

* COS((@Longitude2 - @Longitude1) * PI() / 180)

-- stop acos domain errors

SELECT @cosine = case

WHEN @cosine > 1.0 THEN 1.0

WHEN @cosine < -1.0 THEN -1.0

ELSE @cosine

END

RETURN ACOS(@cosine) * 3963.1

END

Charlottecb Hall of Fame Points: 3932 More actions · Answer 6

Charlottecb

Hall of Fame

Points: 3932

September 21, 2006 at 9:48 am

#661800

Perfect Kev,

You are a star.

CCB

Ken McKelvey SSCoach Points: 18842 More actions · Answer 7

Looking this up on th web the following function may be more accurate over short distances (read the contents of the link):

CREATE FUNCTION dbo.GreatCircleDistance2

(

@Latitude1 float = NULL,

@Longitude1 float = NULL,

@Latitude2 float = NULL,

@Longitude2 float = NULL

)

RETURNS float

AS

-- more accurate method from http://en.wikipedia.org/wiki/Great-circle_distance

BEGIN

IF @Latitude1 IS NULL RETURN 0

IF @Longitude1 IS NULL RETURN 0

IF @Latitude2 IS NULL RETURN 0

IF @Longitude2 IS NULL RETURN 0

DECLARE @sin1 FLOAT

,@sin2 FLOAT

,@sinD FLOAT

,@cos1 FLOAT

,@cos2 FLOAT

,@cosD FLOAT

SELECT @sin1 = SIN(@Latitude1 * PI() / 180)

,@sin2 = SIN(@Latitude2 * PI() / 180)

,@sinD = SIN((@Longitude2 - @Longitude1) * PI() / 180)

,@cos1 = COS(@Latitude1 * PI() / 180)

,@cos2 = COS(@Latitude2 * PI() / 180)

,@cosD = COS((@Longitude2 - @Longitude1) * PI() / 180)

RETURN ATN2 (SQRT(SQUARE(@cos2 * @sinD) + SQUARE(@cos1 * @sin2 - @sin1 * @cos2 * @cosD))

,@sin1 * @sin2 + @cos1 * @cos2 * @cosD &nbsp * 3963.1

END

Charlottecb Hall of Fame Points: 3932 More actions · Answer 8

Charlottecb

Hall of Fame

Points: 3932

September 22, 2006 at 1:54 am

#661917

Thanks again Ken.

CCB