Calculating Distances - Chapter 3

[margaux]

I am trying to apply the zip code script 3.3 to a similar situation using UK postcodes i.e. return a list of locations and their distance from a specific postcode which the user inputs in a form. Below is the (simplified) code I've started with.

if (!empty($_POST['postcode']) && (preg_match('/^(([gG][iI][rR] {0,}0[aA]{2})|((([a-pr-uwyzA-PR-UWYZ][a-hk-yA-HK-Y]?[0-9][0-9]?)|(([a-pr-uwyzA-PR-UWYZ][0-9][a-hjkstuwA-HJKSTUW])|([a-pr-uwyzA-PR-UWYZ][a-hk-yA-HK-Y][0-9][abehmnprv-yABEHMNPRV-Y]))) {0,}[0-9][abd-hjlnp-uw-zABD-HJLNP-UW-Z]{2}))$/',$_POST['postcode']))) {
 $lenpcArea = strcspn($_POST['postcode'],' ',4); // get the length of the postcode up to the first instance of a space
 $pcArea = substr($_POST['postcode'],0, $lenpcArea);
 echo '<h2 class="title"> Closest Paintballing Venues to ' . $_POST['postcode'] .':</h2>';
 require (MYSQL);
 $q = "SELECT Latitude, Longitude FROM postcodes where Pcode='$pcArea' AND Latitude IS NOT NULL";
 $r = mysqli_query($dbc, $q);
 if (mysqli_num_rows($r) == 1 ) {
  list($lat, $long) = mysqli_fetch_array($r, MYSQLI_NUM);
  $q = "SELECT name, website, email, CONCAT_WS('<br />', address1, address2), city, county, pb.postcode, phone, description, ROUND(DEGREES(ACOS(SIN(RADIANS($lat))
  * SIN(RADIANS(Latitude))
  + COS(RADIANS($lat))
  * COS(RADIANS(Latitude))
  * COS(RADIANS($long - Longitude)))) * 69.09) AS distance FROM pbvenues AS pb LEFT JOIN postcodes AS pc ON SUBSTR(pb.postcode,0,4)=pc.Pcode ORDER BY distance ASC";
  $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n <br />MySQL Error: " . mysqli_error($dbc));
  if (mysqli_num_rows($r) > 0) {
while ($row = mysqli_fetch_array($r, MYSQLI_NUM)) {

 echo '<div class="content"><h3>' . $row[0] . '</h3>
 <h4>'. $row[4] . ' - ' . $row[9] . ' miles</h4>
 <p>' . $row[8] . '</p>
 </div>';
}

 

I haven't displayed the code for the form displayed to the user but it includes an input field named 'postcode'. I've downloaded a UK postcode directory and input the data to a d/b named postcodes. The UK directory only provides the postal area i.e. up to the first 4 characters of the postcodes with their corresponding latitude and longitude in a column type of tinyint(4). Syntactically, the code is okay, validating the post code and outputting the data and I'm not getting any mysql errors - all the data I've asked for is being returned. However, the distance logic isn't working.

 

2 questions:

1. What's the best way to do a join when the 2 columns are of different types - the postcodes.Pcode column is tinyint(4) and the pbvenues.postcode column is varchar(9). Would it be best to change the format of the postcodes.Pcode column?
   
2. Why is the above code returning the same distance for every location stored in the pbvenues table?
   

If you're not familiar with UK postcodes the following are all valid postcodes - WC2R 0BL, S6 6JE, M33 5EE, BN6 9EA, AB10 9AB, the first section known as the postal area is between 2 and 4 characters long separated from the second section by a space, case insensitive. Any suggestions? as always improvements welcome. Thanks

[Antonio Conte]

1: if the columns holds, similar data, I would use the same type on both columns, yes. Without any guarantees, the join would probably work anyway. The only thing to do is to test this.

 

2. That is hard to say. If I remember correctly, Larry used a saved procedure in the database to calculate distances. Are you using this procedure? Have you seen if the query works inside phpmyadmin/similar? You need to isolate the problem by testing both your queries and php step by step. Don't expect code to work flawlessly on this level of programming.

[margaux]

Antonio, thanks for replying:

1. I'll try changing the column types so they're both the same type e.g. varchar(8) but I'm wondering if the problem is stemming from the downloaded data being a maximum of 4 characters e.g. AB10 or S6, whilst I need the full postcode and am storing it as 8 characters e.g. AB10 5AU or S6 6JE. Is there a way with mysqli to isolate the characters in the latter type column to just S6 or AB10? In the second query I tried using SUBSTR but if the field is S6 6JE, the comparison will be on S6 6 and that won't work.
   
2. I have tested in phpmyadmin and the result is as I explained above. The locations are returned but all with the same distance. The formula I'm using is the one Larry used in his zip codes example. Also I believe I've tested step by step as I've echo'ed out various variables which are correct and I've echo'ed the results of the first query and that also is correct. Are there other tests I could try?
   

[Antonio Conte]

1. Download it again and increase the column capacity. That data is truncated by the database now. When you have a column which holds the correct 8 chars, trying running it again.

 

You can do this if you don't wanna download postals/etc again, but I recommend trying that first. It's just much easier and also what you are really looking for. You MAY, instead of substring/similar functionality, try with a like instead for testing. What like = 'string%' does is to match with string + any other data that might fit. like = 'Larry U%'; would match Larry Ullman for example.

 

SELECT * FROM table WHERE postal like = 'AB10%';

 

'AB10' would then match 'AB10 5AU'. But as I said. Download the postal coded again and save them to a column with 8 chars capacity.

 

2. Try to do it straight in phpmyadmin first. When you have 2-3 correct distances calculated, you can be pretty sure it's working. That is what I would've done. Do not bother with PHP before it works correctly.

[margaux]

Thanks for replying. I can't give this alot of time as I've won a paying(!) contract, but just to be clear for when I can revisit...

 

the postcodes data that I downloaded only provides the first 4 characters so the data is not getting truncated. In the venues table that I've created, I store an 8 character postcode as I need the full postcode for other purposes. For the distance calculation I need to get the latitude and longitude provided by the downloaded table so perhaps the like = 'string%' option is the way to go. Will try it.

[margaux]

Finally got some time to return to this and got it working using

SUBSTRING_INDEX(postcode,' ',1)

But I am surprised that both the following did not work

SUBSTR(postcode,0,4)

SUBSTRING(postcode,0,4)

Any ideas?

[Antonio Conte]

From the mysql manual on Substring:

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

 

For some reason, mysql don't start the count from zero, which is in many ways "the standard".

 

I would guess something like this might work:

 

substring(postcode, 1, 5)

 

SUBSTR() is only a synonym for substring, so if the above works, both will work.

[margaux]

For some reason, mysql don't start the count from zero, which is in many ways "the standard".

Ahh thanks Antonio. I do remember reading that somewhere but had forgotten it. Hopefully I won't forget again. SUBSTRING(postcode,1,4) and its SUBSTR equivalent worked.

[Antonio Conte]

Brilliant. Glad you took the time to come back to this and finish it. I'm not much of a "finisher" myself.

[margaux]

It's been niggling at the back of my mind for awhile as I'm sure it will come in handy at some point. Also its a good way to learn/remember functions. Thanks again for your help.