SQL Server query with "equation"

I'm trying to figure out how to build a SQL query in following way:

I have coordi开发者_运维问答nates stored in my SQL Server database, and I'm trying to select all the records in a specified distance from point.

Any ideas how to make it done?

SELECT *
FROM YourTable
INNER JOIN
(SELECT (sqrt( ( xDestination - xOrigin)^2 + ( yDestination - yOrigin)^2 )) AS Distance
    FROM YourTable) AS computed ON YourTable.id = computed.id
WHERE computed.Distance = SpecifiedDistance

I dont know your exact definitions and how you would want to handle the input of the coordinates since I dont know if you are using a server side language to input the data so this is just a general example

This is more interesting than it looks at first sight because you say 'at a specified distance'.

The first cut simple solution is the Pythagorean distance calculation, that is

SELECT * FROM myTable
WHERE SQRT( (myPositionX - @pointX)^2 + (myPositionY - @pointY)^2 ) = @myDistance

However doing a floating point comparison on variables is never a good idea because of inaccuracies, and this is compounded in this case because of the square root - which you don't actually need. It should be obviously that if the distance from A to B is X, then the square of the distance from A to B is also X squared. Hence the next cut would be

SET @mySqrDistance = @myDistance^2

SELECT * FROM myTable
WHERE ( (myPositionX - @pointX)^2 + (myPositionY - @pointY)^2 ) = @mySqrDistance 

Not only does is this more accurate because of the removal of the SQRT, it's also a lot faster for the same reason (Square root functions, although optimized, are slow). However you still have the problem of comparing floats to some degree (of course this is dependent upon the flavour of SQL and the column types used, but in general), so a better approach might be

SET @mySqrDistanceMin = (@myDistance-@myError)^2
SET @mySqrDistanceMax = (@myDistance+@myError)^2

SELECT * FROM myTable
WHERE ( (myPositionX - @pointX)^2 + (myPositionY - @pointY)^2 ) 
BETWEEN @mySqrDistanceMin AND mySqrDistanceMax