Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts

    Postcode search based on distance

    Hi, I'm trying to find a way to allow users to search based on UK Postcode and have results pulled from a mySQL database of members all of whom have a postcode associated with them (i.e their location). For example the user types in W1 1AA and the query pulls in members in order of how far away they are from that postcode.

    I've seen a number of PHP scripts that offer a database of postcodes and calculate distances between them, but I need this to work based on comparing the postcode entered in a form, with a fairly small list of members' postcodes, and then listing the members starting with the geographically closest at the top. The solutions I've seen seem to be pretty complex and I only need this for a small-scale solution.

    I already have a search script for actual postcode but it searches based on it being a literal string so ignores actual distance.

    Anyone know how this can be set up? Thanks!

  2. #2
    Regular Coder
    Join Date
    Sep 2014
    Posts
    226
    Thanks
    0
    Thanked 39 Times in 37 Posts
    I haven't touched this for a long time, but this is what I did in an app I worked on a long time ago.

    1) Using google services (API), you ask for the geolocation (latitude and longtitude) of the post code. You then get the distance between the two geolocations.

    I would advice though, that you simply create a database containing the geolocation of the postcode.

  3. #3
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Thanks, I've no idea how I would get Google to put that data into the database though, and also how to get it to output the geolocations nearest to the specified postcode which are also in members' addresses. This looks like a hell of a problem, although I know it must be possible (somehow) as I've seen a number of websites do it...

  4. #4
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Anyone know how this might be achieved?

  5. #5
    Master Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    5,202
    Thanks
    114
    Thanked 614 Times in 600 Posts
    You wouldn't get google to insert the data into your database, you would have to make http calls to a google url and supply the data and then use the data google returns in your sql insert query.

    As for how to calculate the distances, no idea, never touched on this.
    Quote Originally Posted by deathshadow View Post
    So seriously, loosen up that tie, let out the belt, and try relating to normal people on the street instead of the gentleman's club crowd.

  6. #6
    Regular Coder
    Join Date
    Sep 2014
    Posts
    226
    Thanks
    0
    Thanked 39 Times in 37 Posts
    This is the site for google map services.

    Please note that you need an API key.

    I found this site that might be useful to you. https://www.doogal.co.uk/ukpostcodes.php Download the list of postcodes as csv. It contains the post code and the corresponding geolocation. Save the infos (postcode and geolocation (latitude/longitude) in your data base. Instead of going to google, just get the geolocation from your database.

    This is my code to compute the distance between two geolocations. It is in csharp, so you'll have to convert it to php.

    Code:
    private static double FindDistance(double lat1, double lon1, double lat2, double lon2)
    {
    	const Int32 R = 6371; // km
    	double dLat = (lat2 - lat1) / (180 / Math.PI);
    
    	double dLon = (lon2 - lon1) / (180 / Math.PI);
    
    	double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
    			Math.Cos(lat1 / (180 / Math.PI)) * Math.Cos(lat2 / (180 / Math.PI)) *
    			Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
    
    	double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
    	return (R * c);
    }
    ETA: Check https://www.movable-type.co.uk/scripts/latlong.html
    Last edited by josephm; 11-14-2017 at 02:44 AM.

  7. #7
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by josephm View Post
    This is the site for google map services.

    Please note that you need an API key.

    I found this site that might be useful to you. https://www.doogal.co.uk/ukpostcodes.php Download the list of postcodes as csv. It contains the post code and the corresponding geolocation. Save the infos (postcode and geolocation (latitude/longitude) in your data base. Instead of going to google, just get the geolocation from your database.

    This is my code to compute the distance between two geolocations. It is in csharp, so you'll have to convert it to php.

    Code:
    private static double FindDistance(double lat1, double lon1, double lat2, double lon2)
    {
    	const Int32 R = 6371; // km
    	double dLat = (lat2 - lat1) / (180 / Math.PI);
    
    	double dLon = (lon2 - lon1) / (180 / Math.PI);
    
    	double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
    			Math.Cos(lat1 / (180 / Math.PI)) * Math.Cos(lat2 / (180 / Math.PI)) *
    			Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
    
    	double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
    	return (R * c);
    }
    ETA: Check https://www.movable-type.co.uk/scripts/latlong.html
    Thanks, at least I will have the postcodes in place so that's a useful start. However never touched C# so no idea how to convert it to PHP.

  8. #8
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Unfortunately that file won't work - if I try opening it in Excel to remove the data I don't need, it only loads part of the file, because it contains more than the million or so rows that Excel allows.

    Also as it's almost 800Mb in size it's far too big to import into mySQL. So it's back to the drawing board

  9. #9
    Regular Coder
    Join Date
    Sep 2014
    Posts
    226
    Thanks
    0
    Thanked 39 Times in 37 Posts
    Sorry to hear that. I don't understand why it should be that big. The only thing I can think of, is to open it in a text editor (like notepad++). The first entry should tell you what the columns contain. You'll have to write a little program that will read it line by line. Once you read line, split it into an array using comma (,) as delimiter. When I looked at the file yesterday, I think the information (the postcode, latitude, longitude) you need is within the first 5 columns. Then just write those columns separated again by comma.

    If that does not work, you'll have to use google's map services.

    This is the geocoding api link.. As was mentioned, you need an API key (it's free), but there is a maximum number of request you can do per day (quota).

    When you request a geolocation, you'll need the pass the postcode, state(?) and country. (I don't know the equivalent in UK).

    Good luck.
    Last edited by josephm; 11-15-2017 at 12:27 PM.

  10. Users who have thanked josephm for this post:

    galahad3 (11-15-2017)

  11. #10
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by josephm View Post
    Sorry to hear that. I don't understand why it should be that big. The only thing I can think of, is to open it in a text editor (like notepad++). The first entry should tell you what the columns contain. You'll have to write a little program that will read it line by line. Once you read line, split it into an array using comma (,) as delimiter. When I looked at the file yesterday, I think the information (the postcode, latitude, longitude) you need is within the first 5 columns. Then just write those columns separated again by comma.

    If that does not work, you'll have to use google's map services.

    This is the geocoding api link.. As was mentioned, you need an API key (it's free), but there is a maximum number of request you can do per day (quota).

    When you request a geolocation, you'll need the pass the postcode, state(?) and country. (I don't know the equivalent in UK).

    Good luck.
    Ok, thanks. I'll see what I can do.

  12. #11
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    In case anyone else needs a table of postcodes to work with, I eventually got mine from here: https://www.freemaptools.com/downloa...de-lat-lng.htm

    Still no idea how to convert all that C# code into PHP though, but I'll try to figure it out.

  13. #12
    Regular Coder
    Join Date
    Sep 2014
    Posts
    226
    Thanks
    0
    Thanked 39 Times in 37 Posts
    Still no idea how to convert all that C# code into PHP though, but I'll try to figure it out.
    Try:

    PHP Code:
    //
    //  $lat1, $lon1 - first geolocation
    //  $lat2, $lon2 - second geolocation
    //  returns the distance betwwn two geolocation (latitude,longitude)
    //
    function FindDistance($lat1$lon1$lat2$lon2)
    {
        
    $R 6371// km
        
    $dLat = ($lat2 $lat1) / (180 pi());

        
    $dLon = ($lon2 $lon1) / (180 pi());

        
    $a sin($dLat 2) * sin($dLat 2) +
                
    cos($lat1 / (180 pi())) * cos($lat2 / (180 pi())) *
                
    sin($dLon 2) * sin($dLon 2);

        
    $c atan2(sqrt($a), sqrt($a));
        return (
    $R $c);

    Last edited by josephm; 11-16-2017 at 04:55 AM.

  14. Users who have thanked josephm for this post:

    galahad3 (11-16-2017)

  15. #13
    Regular Coder
    Join Date
    Sep 2009
    Posts
    243
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Thanks - looks good - I'll see if I can get that set up.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •