Postcode and radius lookup recommendations

Question

I look after a number of divisional websites for a uk based membership organisation and what we want to do is provide, as well as other address functions, is a closest member lookup to a web user from the websites themselves.

A few use cases that I want to fill:

Case 1: The user puts in their post code and wants to see all the members in a 5/10/15/20/30/40 mile radius from them

Case 2: The member puts in an area (city, county, etc.) and gets a list of members in that area.

Essentially what I'm looking for is a programmable API which I can code against to do:

• post code lookup and returns addresses (after picking house number for example).
• search post code + radius (5miles, 10miles etc) and get a set of applicable post codes to then join onto the membership records in the database

Any recommendations? It can be a quarterly update install on the server, it can be a queryable web service. I'm open to suggestions.

Thanks in advance

Edit Not forgotten about this; something else has been thrown into the mix at work and this has gone onto the back burner. Will update when it comes back to the boil (applogies for the pan boiling comments :-))

Answer 1

Update: I just saw on the BBC News site that the PostCode Data will be Free from this month. In light of this, I would use this data. I would have a look for APIs to this database.

Previous answer: UK Post Code data is supplied at SIGNIFICANT cost by the Royal Mail. This comes with Lng Lat data for each postcode. The proprietry nature of the Royal Mail's database is quite controversial. See this site for more info.
Having said that, you could use Google Maps Api to do this. Users could enter place a pin on the map where they live and you could capture the Lng and Lat of that. It won't give you everything you specified above.

Here is a good article on the Google Geocoding API

Answer 2

At least within the United States (I am completely ignorant of any other country's postal code format -- but if they map to Long/Lat, this ought to work) you should be able to use the Haversine Formula to work out the ZIP codes within a given geospatial radius.

Simply put, you must have a postal code -> long/lat table. Then, from a given postal code (coordinate) you can mathematically determine all latitude and longitudinal coordinates within a given distance of that point -- then you take those approximate coords and convert back to zip, then query for members within zip codes.

After that, it's a matter of plotting them out on a map. The following will give MUCH more detail than I could, as it explains the math and actually translates it into a working MySQL query to get you started:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Answer 3

One thing you will probably encounter at some point is having to calculate the distance between two points (latitude/longitude) pairs. One of the best known algorithms is the Haversine Forumla. I've written an implementation of this, based on some C code I found (can't remember the original author to credit them). It goes something like this:

public static double DistanceBetween(LatLng pos1, LatLng pos2, DistanceUnit unit)
{
    double R = 6371;

    switch (unit)
    {
        case DistanceUnit.Miles:
            R = 3960;
            break;
        case DistanceUnit.Kilometers:
            R = 6371;
            break;
        case DistanceUnit.Meters:
            R = 6371000;
            break;
    }

    double dLat = GeoMath.DegreesToRadians(pos2.Latitude - pos1.Latitude);
    double dLon = GeoMath.DegreesToRadians(pos2.Longitude - pos1.Longitude);
    double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
        Math.Cos(GeoMath.DegreesToRadians(pos1.Latitude)) *
        Math.Cos(GeoMath.DegreesToRadians(pos2.Latitude)) *
        Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
    double c = 2 * Math.Asin(Math.Min(1, Math.Sqrt(a)));
    double d = R * c;
    return d;
}

Where DistantUnit is a simple enum and LatLng is essentially just a struct or class with two double properties.

Answer 4

I did something similar few months ago. Given an IP address i needed to know the closes town/city where this service should run on our own servers. From a website i dont know any more i got a list of lat/lng/city locations all around the world. The table contained around 8 mio entries.

As Dan Diplo suggest you will need the distance for such queries. My Query was to find the closest location for a given lat/lng coordinate. Using a similar function as Dan Diplo's i created an index which calculated the distance of the DB entry (latlng1) and zero (latlng2). This index is only used to get an index scan to reduce the data set - it doestn help finding the final value. Finally you have to query for the data you search AND the distance to zero to reduce the search set.

Thus a query took around 100ms. But you have to use Diplo's function as a SQL index which shouldnt be a problem on current databases providing mathematical functions.