Mathematical T-SQL

Question

Mathematical T-SQL

b_boy

SSCertifiable

Points: 5145
More actions
January 5, 2008 at 4:13 am

#182407

How do i write a T-SQL statement that will satisfy this mathematical equation?
Distance = sqrt( square(x1-x2) + square(y1-y2) )

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

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 1

SQRT and SQUARE are standard mathematical functions in pretty much every make and flavor of SQL including SQL Server.

Your values of the coordinates of x1, x2, y1, and y2 can either be numeric expressions, scalar variables, or column names. Since this is purely mathematical and does not require separate access to a table, this is a pretty good candidate for a user defined function... like this...

CREATE FUNCTION dbo.fnDistance

--===== Declare the input parameters (order sensitive)

(

@X1 FLOAT, --X component of coordinate pair 1

@Y1 FLOAT, --Y component of coordinate pair 1

@X2 FLOAT, --X component of coordinate pair 2

@Y2 FLOAT --Y component of coordinate pair 2

)

RETURNS FLOAT

AS

BEGIN --Pythagorean's formula for length of hypothenuse

RETURN (SELECT SQRT(SQUARE(@X1-@X2) + SQUARE(@Y1-@Y2)))

END

... then, using the coordinates for a classic 3*4*5 triangle as a proof, here's how you could call it...

--===== Test the function in each quadrant of a Cartesian coordinate system

SET NOCOUNT ON

SELECT Distance = dbo.fnDistance(0,0,3,4) --Quadrant 1 test

SELECT Distance = dbo.fnDistance(0,0,-3,4) --Quadrant 2 test

SELECT Distance = dbo.fnDistance(0,0,-3,-4) --Quadrant 3 test

SELECT Distance = dbo.fnDistance(0,0,3,-4) --Quadrant 4 test

... Results ...

Distance

-----------------------------------------------------

5.0

Distance

-----------------------------------------------------

5.0

Distance

-----------------------------------------------------

5.0

Distance

-----------------------------------------------------

5.0

Again, the hard coded coordinates I used in the tests may be numeric expressions that result in Cartesian Coordinates, variables that contain the coordinates, or columns from a table that contain the coordinates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

p.s. IF, by chance, you are using the V&H coordinates (as Telcordia calls them) from the "Donald Projection" that is used so often for calculating distance for telephony, airlines, or delivery systems (instead of Lat/Lon), please be advised that 1 mile is equivalent to SQRT(10) V&H... the correct formula for using the Donald Projection coordinates would be as follows...

CREATE FUNCTION dbo.fnDistanceVnH

--===== Declare the input parameters (order sensitive)

(

@X1 FLOAT, --X component of coordinate pair 1

@Y1 FLOAT, --Y component of coordinate pair 1

@X2 FLOAT, --X component of coordinate pair 2

@Y2 FLOAT --Y component of coordinate pair 2

)

RETURNS FLOAT

AS

BEGIN --Pythagorean's formula for length of hypothenuse

--with Donald Projection modifier for conversion to miles.

RETURN (SELECT SQRT((SQUARE(@X1-@X2) + SQUARE(@Y1-@Y2))/10))

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob Farley SSCrazy Points: 2739 More actions · Answer 3

Can I do a quick plug for the new Spatial types in SQL2008 here? You can use them to find the distance between two points quite easily.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

Show us how, Rob...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob Farley SSCrazy Points: 2739 More actions · Answer 5

It's really simple - check out Books Online

http://msdn2.microsoft.com/en-us/library/bb933952(SQL.100).aspx

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

That's pretty cool... Thanks, Rob...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 7

If you are mapping locations on the earth you need to use the geography type with the approriate Spatial Reference. This will allow for the curve of the earth. If you're doing it for a flat surface i.e. football pitch use the Geometry type.

If useing the geography type you need to specify a longitude and latitude not an x and y.

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons