CODEWITHSUNDEEP
sqlsql-server-2008geometrydistance
powerball22
powerball22

Reputation: 33

Calculate what pair of coordinates are within a distance of another pair

I need some help with the following issue: I have a Table X where I store some street names with pair of coordinates.

Table X

Street | x | y
street1, 22.342342, 44.234242
street2, 23.4324324, 44.432554

etc.

For example, if my input is street 1 data, i want to find what other streets are within a 500 meters radius of street 1. How can this be done, without using Geography in SQL Server?

Upvotes: 2

Views: 179

Answers (2)

jtrayford
jtrayford

Reputation: 156

If the coordinates are cartesian and in meters, you could do this to return the appropriate streets:

SELECT             
     X.Street 
FROM             
     X as X,             
     X as REF             

WHERE             
    REF.Street = 'street1' and
    X.Street <> 'street1' and 
    SQRT(SQUARE(X.x - REF.x) + SQUARE(X.y - REF.y)) < 500

where 'street1' could be any input street. The X.Street <> 'street1' line avoids the input street being returned if this is important.

Upvotes: 1

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2376

The best you can do without use of a mapping software is to calculate Straight Line Distance between the two points.

Here is a good link explaining the Haversine method, which i have used in the past.

Here is a code snippet showing the calculation:

function haversineDistance(latlong1,latlong2)
{
    var lat1 = DegtoRad(latlong1.Latitude);
    var lon1 = DegtoRad(latlong1.Longitude);
    var lat2 = DegtoRad(latlong2.Latitude);
    var lon2 = DegtoRad(latlong2.Longitude);
    var dLat = lat2-lat1;
    var dLon = lon2-lon1;
    var cordLength = Math.pow(Math.sin(dLat/2),2)+Math.cos(lat1)*Math.cos(lat2)*Math.pow(Math.sin(dLon/2),2);
    var centralAngle = 2 * Math.atan2(Math.sqrt(cordLength), Math.sqrt(1-cordLength));
    return earthRadius * centralAngle;
}

Upvotes: 0

Related Questions