Location Search with ActiveRecord

The samples in this article are based on a great Code Project article by Thomas Kurek. Links to his original work are found in the references section of this article.

Zip code proximity searching is an important aspect of organizing your web site content by location. Whether you plan to build a database of mobile dog groomers or compile a list of microbrews, zip code searching will help make your site more interactive. This article will demonstrate how to use ActiveRecord (and some borrowed math) to add location based features to your site.

Though you'll likely have some content tables that map to a table of locations, for the purpose of this article I'm keeping it simple. I'll work with only a locations table that has only U.S. locations. The table is defined as follows:

LOCATION
LOCATION_ID
CITY
STATE
ZIP
LONGITUDE
LATITUDE

The ActiveRecord class for this table (before location searching is included) is simple. I've included some search methods to return locations by city and state, or zip. A more complete solution would probably consider like conditions for city name.

[Serializable, ActiveRecord("locations")]
    public class Location : ActiveRecordBase<Location>
    {
        [PrimaryKey(PrimaryKeyType.Identity, "location_id")]
        public int Id { get; set; }

        [Property("city")]
        public string City { get; set; }

        [Property("state")]
        public string State { get; set; }

        [Property("zip")]
        public string Zip { get; set; }

        [Property("Latitude")]
        public double Latitude { get; set; }

        [Property("longitude")]
        public double Longitude { get; set; }
        
        public static Location[] FindAllOrderdByCity()
        {
            return FindAll(new Order("City", true));
        }

        public static Location FindByCityAndState(string city, string state)
        {
            return FindFirst(new EqExpression("City", city, true),
                             new EqExpression("State", state, true));
        }

        public static  Location FindByZip(string zip)
        {
            return FindFirst(new EqExpression("Zip", zip));
        }
        ...
    }

The Code Project original article included a number of conversion functions within its Location class. I've factored those out into a utility class named ConversionUtility.

internal static class ConversionUtils
    {
        internal const int a = 6378137;
        internal const double e = 0.0066943799901413165;

        public static double CalcRoCinPrimeVertical(this double lat0) { ...}        
        
        public static double CalcMeridionalRadiusOfCurvature(this double lat0)  { ...}
        
        public static double CalcDistanceLatLons(this double Rm, double Rpv, double lat0, <br />
                                                double lon0, double lat, double lon)  { ... }
        
    }

I'll leave the math explanations to Thomas Kurek in his Code Project article. Below I'll focus on how I changed his Location object's FindNearbyLocations method to work with ActiveRecord.

My AR version returns a Location[] to be consistent with other AR FindX methods.

public static Location[] FindNearbyStories(Location centroidLocation, double searchRadius) { ... }

A Location instance is passed in as the center point. That object is used to find derive the search boundaries.

double lat0 = Convert.ToDouble(centroidLocation.Latitude) * degreesToRadians;
double lon0 = Convert.ToDouble(centroidLocation.Longitude) * degreesToRadians;

I then find all locations using an AR FindAll.

Location[] nearbyLocations = Location.FindAll
    (
        Expression.Gt("Latitude", latMin),
        Expression.Lt("Latitude", latMax),
        Expression.Gt("Longitude", lonMin),
        Expression.Lt("Longitude", lonMax)
    );

There's some more code that Thomas includes for greater precision. I've refactored the DataTable portion to instead use a generic Dictionary that gets copied to a cleaned up Location array.

Consuming the code from a controller is straightforward.

public void Search(string zip)
{    
    Location centerPoint = Location.FindByZip(zip);
    Location[] locations = Location.FindNearbyLocations(centerPoint, 5);
    Flash["Locations"] = locations;
    Flash["CenterPoint"] = centerPoint;
    
    RedirectToReferrer();
            
}

Download Sample Project

References

This article is a derivative work from this Code Project article.
Sample project source code

Article Posted: Saturday, February 16, 2008