CODEWITHSUNDEEP
c#sqlsilverlight
GJJ
GJJ

Reputation: 494

Calculating distance between 2 points with sql

I have to calculate the distance between 2 points, X and Y being the coordinates of the first point while axisX and axisY are the coordinates of the second point. My only lead is this SQL statement, however this SQL does not return the result I'm looking for. So can anyone help me identify any mistake I may have made with this statement?

string x = "SELECT TownName,Address,SUBSTRING(X, 1, 2) AS Xaxis, SUBSTRING(Y, 1, 2) AS Yaxis,Type,Availability,Price,Size,Bedrooms,Bathrooms,Contact,Screenshot1,Screenshot2,Screenshot3,Screenshot4 FROM House"+
                                    "WHERE (SQRT(POWER(X - "+axisX+" , 2) + POWER(Y - "+axisY+", 2)) <= 2000)";

Upvotes: 2

Views: 1060

Answers (1)

user766987
user766987

Reputation: 712

SELECT <replace with your select>,

3963.0 * ACOS (
    sin(SUBSTRING(X, 1, 2)/57.2958) * sin(" + axisX + "/57.2958) + cos(SUBSTRING(X, 1, 2)/57.2958) * cos(" + axisX + "/57.2958) * cos(" + axisY + "/57.2958 - SUBSTRING(Y, 1, 2)/57.2958))
as distance
 FROM House
ORDER BY distance

I'm assuming axisX and axisY are your co-ordinates. This uses the distance calculation technique which may provide you a more accurate reading.

http://www.meridianworlddata.com/Distance-Calculation.asp

Hope this helps.

Upvotes: 5

Related Questions