How to return a list of stores which are a specified distance from the entered Postcode

Question

I am using Google Maps to create a store locator.

The user will enter their postcode and select from a dropdown menu the distnce from the location - 5, 10 15 20 miles.

Which will return the stores that are the specified distnace from their location and put markers on the map.

I have a postcode table which has the following columns:

 PostCode Latitude Longitude Easting Northing Grid Ref Country District Ward

and a stores table which has a foreign key relationship with this table

I am using MVC3 and linq-sql, but i have no idea how to write a query to only pull back the stores which are say 5 miles from the postcode that has been entered

EDIT

I've found the following:

select *,
    acos(cos(51.720663 * (PI()/180)) *
     cos(-0.299929 * (PI()/180)) *
     cos(latitude * (PI()/180)) *
     cos(longitude * (PI()/180))
     +
     cos(51.720663 * (PI()/180)) *
     sin(-0.299929 * (PI()/180)) *
     cos(latitude * (PI()/180)) *
     sin(longitude * (PI()/180))
     +
     sin(51.720663 * (PI()/180)) *
     sin(latitude * (PI()/180))
    ) * 3959 as Dist
from postcodes
having Dist < radius
order by Dist

but this part doesn t work:

having Dist < radius
    order by Dist

am i missing something..?

and really i want to do this in linq if possible....

Any help would be greatly appreciated.

Thanks

Answer 1

I actually ended up using the class GeoCodeCal to wrok out what i needed.

Please see the class below:

using System;

namespace WidgetData
{
    /// <summary>
    /// Used when we need to calculate the distance between two geocodes (lat/long) points. 
    /// </summary>
    public static class GeoCodeCalc
    {

        ///<summary>
        ///</summary>
        public const double EarthRadiusInMiles = 3956.0;

        ///<summary>
        ///</summary>
        public const double EarthRadiusInKilometers = 6367.0;

        ///<summary>
        ///</summary>
        ///<param name="val"></param>
        ///<returns></returns>
        public static double ToRadian(double val)
        {
            return val * (Math.PI / 180);
        }

        ///<summary>
        ///</summary>
        ///<param name="val1"></param>
        ///<param name="val2"></param>
        ///<returns></returns>
        public static double DiffRadian(double val1, double val2)
        {
            return ToRadian(val2) - ToRadian(val1);
        }

        /// <summary> 
        /// Calculate the distance between two geocodes. Defaults to using Miles. 
        /// </summary> 
        public static double CalcDistance(double lat1, double lng1, double lat2, double lng2)
        {
            return CalcDistance(lat1, lng1, lat2, lng2, GeoCodeCalcMeasurement.Miles);
        }

        /// <summary> 
        /// Calculate the distance between two geocodes. 
        /// </summary> 
        public static double CalcDistance(double lat1, double lng1, double lat2, double lng2, GeoCodeCalcMeasurement m)
        {
            double radius = EarthRadiusInMiles;
            if (m == GeoCodeCalcMeasurement.Kilometers)
            {
                radius = EarthRadiusInKilometers;
            }
            return radius * 2 * Math.Asin(Math.Min(1, Math.Sqrt((Math.Pow(Math.Sin((DiffRadian(lat1, lat2)) / 2.0), 2.0) + Math.Cos(ToRadian(lat1)) * Math.Cos(ToRadian(lat2)) * Math.Pow(Math.Sin((DiffRadian(lng1, lng2)) / 2.0), 2.0)))));
        }

        /// <summary>
        /// Convert the OS Grid reference numbers which are easting and norhing into a latitude/longitude
        /// </summary>
        /// <param name="easting"></param>
        /// <param name="northing"></param>
        /// <param name="latitude"></param>
        /// <param name="londgitude"></param>
        public static void ConvertOsGridToLatLong(int easting, int northing, out double latitude, out double londgitude)
        {
            var e = easting;
            var N = northing;
            const double a = 6377563.396;
            const double b = 6356256.910; // Airy 1830 major & minor semi-axes  
            const double f0 = 0.9996012717; // NatGrid scale factor on central meridian  
            const double lat0 = 49*Math.PI/180;
            const double lon0 = -2*Math.PI/180; // NatGrid true origin  
            const int n0 = -100000;
            const int e0 = 400000; // northing & easting of true origin, metres  
            const double e2 = 1 - (b*b)/(a*a); // eccentricity squared  
            const double n = (a - b)/(a + b);
            const double n2 = n*n;
            const double n3 = n*n*n;
            var lat = lat0;
            var m=0.0;                                         
            do 
            {    
                lat = (N-n0-m)/(a*f0) + lat;    
                var ma = (1 + n + (5/4)*n2 + (5/4)*n3) * (lat-lat0);    
                var mb = (3*n + 3*n*n + (21/8)*n3) * Math.Sin(lat-lat0) * Math.Cos(lat+lat0);    
                var mc = ((15/8)*n2 + (15/8)*n3) * Math.Sin(2*(lat-lat0)) * Math.Cos(2*(lat+lat0));    
                var md = (35/24)*n3 * Math.Sin(3*(lat-lat0)) * Math.Cos(3*(lat+lat0));    
                m = b * f0 * (ma - mb + mc - md);                // meridional arc  
            } while (N-n0-m >= 0.00001);  // ie until < 0.01mm  
            var cosLat = Math.Cos(lat);
            var sinLat = Math.Sin(lat);  
            var nu = a*f0/Math.Sqrt(1-e2*sinLat*sinLat);              // transverse radius of curvature  
            var rho = a*f0*(1-e2)/Math.Pow(1-e2*sinLat*sinLat, 1.5);  // meridional radius of curvature  
            var eta2 = nu/rho-1;  
            var tanLat = Math.Tan(lat);
            var tan2Lat = tanLat*tanLat;
            var tan4Lat = tan2Lat*tan2Lat;
            var tan6Lat = tan4Lat*tan2Lat;  
            var secLat = 1/cosLat;
            var nu3 = nu*nu*nu;
            var nu5 = nu3*nu*nu;
            var nu7 = nu5*nu*nu;  
            var vii = tanLat/(2*rho*nu);  
            var viii = tanLat/(24*rho*nu3)*(5+3*tan2Lat+eta2-9*tan2Lat*eta2);  
            var ix = tanLat/(720*rho*nu5)*(61+90*tan2Lat+45*tan4Lat);  
            var x = secLat/nu;  
            var xi = secLat/(6*nu3)*(nu/rho+2*tan2Lat);  
            var xii = secLat/(120*nu5)*(5+28*tan2Lat+24*tan4Lat);  
            var xiia = secLat/(5040*nu7)*(61+662*tan2Lat+1320*tan4Lat+720*tan6Lat);
            var dE = (e - e0);
            var dE2 = dE*dE;
            var dE3 = dE2*dE;
            var dE4 = dE2*dE2;
            var dE5 = dE3*dE2;
            var dE6 = dE4*dE2;
            var dE7 = dE5*dE2;  
            lat = lat - vii*dE2 + viii*dE4 - ix*dE6;  
            var lon = lon0 + x*dE - xi*dE3 + xii*dE5 - xiia*dE7;
            latitude = ToDegree(lat);
            londgitude = ToDegree(lon);
        }

        /// <summary>
        /// Convert radians to degreens
        /// </summary>
        /// <param name="angle"></param>
        /// <returns></returns>    
        public static double ToDegree(double angle)
        {
            return Math.PI * angle / 180.0;
        }

    }


    ///<summary>
    ///</summary>
    public enum GeoCodeCalcMeasurement
    {
        ///<summary>
        /// Miles
        ///</summary>
        Miles = 0,
        ///<summary>
        /// Kilometers
        ///</summary>
        Kilometers = 1
    }
}

and used CalcDistance called from my controller (post being the initial postcode and store being the other postcode)

var calcDistance = GeoCodeCalc.CalcDistance(postLong, postLat, storeLong, storeLat);

Answer 2

Here's a small snippet of how I did something similar by city using LINQ over EF:

            var cityloc = _db.Cities.Find(t.CityId).location;
            var radius = Int32.Parse(t.CityRadius);
            results = results.Where(x => x.City.location.Distance(cityloc) < radius * metersPerMile || x.city_id == t.CityId);

t.CityId is the city you are in, and it finds results that are within t.CityRadius miles from you. Results is an object that has a city property of City class. The City class has a property location that is a DbGeography type.

Spatial support in LINQ over SQL is non-existent. It is supported in EF 5 however. You may want to check out this post which talks about LINQ over SQL and how to convert to/from spatial types: Is it possible to use SqlGeography with Linq to Sql?

Good news is once you pull in your zipcode table into a class, then you might be able to use the built in .NET spatial stuff to do your distance calculations for you (or you can do it manually like you have been).

If you aren't too far into your development, you might even consider switching out LINQ over SQL to LINQ over Entity Frameworks. It's very similar, and it has full support for spatial data types.