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

Question

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?

Possibly relevant: http://stackoverflow.com/questions/639695/how-to-convert-latitude-or-longitude-to-meters — Politank-Z, Jul 09 '15 at 13:55

score 1 · Accepted Answer · answered Jul 09 '15 at 14:22

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.

score 0 · Answer 2 · answered Jul 09 '15 at 13:54

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;
}

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

2 Answers2