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 3 of 3
  1. #1
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post

    how to calculate distance between 2 postcodes.

    1) Project Details: (be as specific as possible):

    I have the following code below which works but I would like it done using one query, or two at best to prevent the current code from freezing out the server.

    below is my testing script i have been working on but as you can see this will take a lot of resources on the server if i was to search say over 5000 members ?!

    i would like some help in finding a way to do a single query and not grabbing each users details and checking each and everyone of them one by one for the matches.

    is this possible?


    As for costs, please PM me, if your quote is unrealistic you will not get a look in.

    I have already paid 40 for this one page! but I know that once I go live and get members it will cause problems with the server.



    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <?
      function distance($pcodeA,$pcodeB) {
        $db_code1 = explode(' ',$pcodeA); $db_code2 = explode(' ',$pcodeB);
    
        $postcode1 = mysql_fetch_assoc(mysql_query("SELECT * FROM `postcodes` WHERE postcode='$db_code1[0]' LIMIT 1"));
        $postcode2 = mysql_fetch_assoc(mysql_query("SELECT * FROM `postcodes` WHERE postcode='$db_code2[0]' LIMIT 1"));
    
        $p1_gridN = $postcode1['grid_n']; $p1_gridE = $postcode1['grid_e'];
        $p2_gridN = $postcode2['grid_n']; $p2_gridE = $postcode2['grid_e'];
    
        $distance_n=$p1_gridN-$p2_gridN; $distance_e=$p1_gridE-$p2_gridE;
        $hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e));
    
        $distance = array("".round($hypot)."","".round(0.62*($hypot/1000),1)."");
        return $distance;
      }
    
    include('connection.php');
    
    // details of the search to do.
    $searchGender = "Male";
    $searchPostcode = "SM1";
    $searchDistance = "2000";  // 5 miles
    
    // get all users details.
      $getUsers = mysql_query("SELECT * FROM user_accounts WHERE gender='$searchGender' ORDER BY account_id ASC");
      $number = 0;
      // check if a user is within the 'searchDistance' of the '$user['postcode']', if so display their details.
      while($user = mysql_fetch_assoc($getUsers)) {
       $distance = distance($searchPostcode,$user['postcode']);	//	say $user['postcode'] = "AB12"
       echo("" . $user['postcode'] . "  " . $searchDistance . " miles of their postcode.<br>");
    
    		 if($distance[1] < $searchDistance && $distance[1] > 0 && $distance[0] > 0) { $number++;
    		   echo 'Fullname: ',$user['firstname'],' ',$user['middlename'],' ',$user['lastname'],' ',$user['gender'],"\n";
    		   echo '<div id="distanceDiv'.$user['account_id'].'">'.$distance[1].":\n".
    				'</div><br>'."\n\n";
    		 }
      }
    ?>
    </body>
    </html>
    an extract of my mysql's
    Code:
    CREATE TABLE IF NOT EXISTS `user_accounts` (
      `account_id` int(100) NOT NULL auto_increment,
      `firstname` varchar(50) NOT NULL,
      `middlename` varchar(50) NOT NULL,
      `lastname` varchar(50) NOT NULL,
      `gender` varchar(6) NOT NULL,
      `postcode` varchar(10) NOT NULL,
      PRIMARY KEY  (`account_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
    
    INSERT INTO `user_accounts` (`account_id`, `firstname`, `middlename`, `lastname`, `gender`, `postcode`) VALUES
    (1, 'Mark', '', 'Smith', 'Male', 'SM1 1EA'),
    (2, 'Lisa', '', 'Smith', 'Female', 'SM2 1EA'),
    (3, 'Simon', '', 'Smith', 'Male', 'SM3 1EA');
    
    
    
    
    CREATE TABLE IF NOT EXISTS `postcodes` (
      `postcode_id` int(11) NOT NULL auto_increment,
      `postcode` tinytext NOT NULL,
      `grid_n` varchar(15) NOT NULL default '',
      `grid_e` varchar(15) NOT NULL default '',
      `latitude` varchar(15) NOT NULL default '',
      `longitude` varchar(15) NOT NULL default '',
      PRIMARY KEY  (`postcode_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2823 ;
    
    
    INSERT INTO `postcodes` (`postcode_id`, `postcode`, `grid_n`, `grid_e`, `latitude`, `longitude`) VALUES 
    (2295, 'SM1', '525900', '164600', '51.366', '-0.191'),
    (2296, 'SM2', '525600', '163100', '51.352', '-0.197'),
    (2297, 'SM3', '524400', '165000', '51.37', '-0.213'),
    (2298, 'SM4', '525300', '167500', '51.392', '-0.199'),
    (2299, 'SM5', '527600', '164800', '51.368', '-0.166'),
    (2300, 'SM6', '529300', '164000', '51.36', '-0.143'),
    (2301, 'SM7', '525400', '159800', '51.323', '-0.2');
    note: there are somewhere like 3000 postcodes in the original mysql file i got from google searching. thats why i supplied just ashort version.

  • #2
    Regular Coder slappyjaw's Avatar
    Join Date
    Mar 2009
    Location
    Wonderland!
    Posts
    146
    Thanks
    14
    Thanked 3 Times in 3 Posts
    I can do this for you, it seems like a pretty easy fix. If i can have a better look at it i can do it for a reasonable price. Please email me at me@slappyjaw.com

  • #3
    Regular Coder
    Join Date
    Apr 2010
    Posts
    417
    Thanks
    4
    Thanked 1 Time in 1 Post
    project already completed


  •  

    Posting Permissions

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