asp.net mvc 3 - How to return a list of stores which are a specified distance from the entered Postcode -

i using google maps create store locator.

the user enter postcode , select dropdown menu distnce location - 5, 10 15 20 miles.

which return stores specified distnace location , put markers on map.

i have postcode table has following columns:

 postcode latitude longitude easting northing grid ref country district ward 

and stores table has foreign key relationship table

i using mvc3 , linq-sql, have no idea how write query pull stores 5 miles postcode has been entered

edit

i've found 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 dist postcodes having dist < radius order dist 

but part doesn t work:

having dist < radius     order dist 

am missing something..?

and want in linq if possible....

any appreciated.

thanks

i ended using class geocodecal wrok out needed.

please see class below:

using system;  namespace widgetdata {     /// <summary>     /// used when need calculate distance between 2 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 distance between 2 geocodes. defaults 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 distance between 2 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 os grid reference numbers easting , norhing 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 = 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;                                                                   {                     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 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 controller (post being initial postcode , store being other postcode)

var calcdistance = geocodecalc.calcdistance(postlong, postlat, storelong, storelat);