Jump to content
Larry Ullman's Book Forums

Chapter 3: Working With Zip-Codes


Recommended Posts

Hi Larry, as like every says... fantastic books. I have 4.

I can't seem to get my head around this 1 part. I have altered the Zip Code script to just do Postcodes for now without any stores/items.

 

		$q = "SELECT postcode, ROUND(DEGREES(ACOS(SIN(RADIANS($lat)) 
* SIN(RADIANS(latitude)) 
+ COS(RADIANS($lat))  
* COS(RADIANS(latitude))
* COS(RADIANS($long - longitude)))) * 69.09, 2) AS distance FROM uk_postcodes WHERE distance<=1 ORDER BY distance ASC LIMIT 100";
	$r = mysqli_query($dbc, $q);

 

The "WHERE" clause seems to not work. I have tried putting it in brackets, single quotations etc but still wont work. It does work without the "WHERE" clause though. Ideally I'd like it to limit the radius of searches.

Can you see anything that I have done wrong? or what is the problem?

I have had a look at other topics to see whether this has already been covered, but couldn't see anything.

 

Thanks alot

Link to comment
Share on other sites

Thanks for the nice words and for the interest in the books. Much appreciated. When you say it "doesn't work", what does that mean? It doesn't return any results? Are there any distances that are less than 1 when you run the query without the WHERE clause?

Link to comment
Share on other sites

Sorry I should of been a lil more thorough about my query.

Here is the scripts with and without the "WHERE" clause...

http://www.flyinduck.com/distance1.php - Without

		$q = "SELECT postcode, ROUND(DEGREES(ACOS(SIN(RADIANS($lat)) 
* SIN(RADIANS(latitude)) 
+ COS(RADIANS($lat))  
* COS(RADIANS(latitude))
* COS(RADIANS($long - longitude)))) * 69.09, 2) AS distance FROM uk_postcodes ORDER BY distance ASC LIMIT 100";
	$r = mysqli_query($dbc, $q);

	if (mysqli_num_rows($r) > 0) {

http://www.flyinduck.com/distance.php - With

		$q = "SELECT postcode, ROUND(DEGREES(ACOS(SIN(RADIANS($lat)) 
* SIN(RADIANS(latitude)) 
+ COS(RADIANS($lat))  
* COS(RADIANS(latitude))
* COS(RADIANS($long - longitude)))) * 69.09, 2) AS distance FROM uk_postcodes WHERE distance<=1 ORDER BY distance ASC LIMIT 100";
	$r = mysqli_query($dbc, $q);

	if (mysqli_num_rows($r) > 0) {

Like I say I have tried it with "WHERE (distance<=1)" "WHERE distance<='1'" "WHERE (distance<='1')"

Does it have anything to do with the fact that I put it to 2 decimal points?

I did try the "WHERE" clause with "WHERE postcode != '$postcode'" which seemed to bring back results too.

If there is any additional information you need please let me know and I'd gladly provide it.

 

Thanks alot for any help

Link to comment
Share on other sites

Okay, my question is this: are there records for any given latitude and longitude that will have a distance less than 1 mile? When you run the query without the WHERE clause, what are the lowest distance values returned?

Link to comment
Share on other sites

There are at least 200 records which are less than 1 mile out of 1,600,000+ records. If you click on the links I provided and type "so163hg" you should see 1 will work and the other wont. The one that does shows at least 100 records within a mile.

 

Thanks for any help possible

Link to comment
Share on other sites

This seems a little strange... so after the PHP error gets thrown call:

 

mysqli_error($dbc);

 

to get the actual MySQL error - I doubt it will be particularly useful. Or if you run it directly in PHPMyAdmin what is the result/feedback?

 

Also can you echo out the actual queries I appreciate you've only changed that one line but I find it's always best to be methodical when debugging something.

 

p.s. where abouts in the UK are you based?

Link to comment
Share on other sites

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

This looks exactly like your issue. Try using HAVING instead of WHERE. It may only work in conjunction with GROUP BY clauses. Try this:

 

$q = "SELECT postcode, ROUND(DEGREES(ACOS(SIN(RADIANS($lat)) 
* SIN(RADIANS(latitude)) 
+ COS(RADIANS($lat))  
* COS(RADIANS(latitude))
* COS(RADIANS($long - longitude)))) * 69.09, 2) AS distance 
FROM uk_postcodes 
ORDER BY distance ASC 
GROUP BY distance 
HAVING distance <= 1 
LIMIT 100";

Link to comment
Share on other sites

I'm not sure why I didn't pick up on that before I did this exact same thing about a year ago and had the same problem. I'm not sure a HAVING clause will work (I may be wrong) I know at the time I used a sub-select/query to achieve the desired effect.

Link to comment
Share on other sites

Thanks for noticing the problem Paul and thanks for your try at the script, but unfortunately didn't work

... at the time I used a sub-select/query to achieve the desired effect.

Could you go a little into more detail as to how to achieve this if possible? I am just looking to do this so then only items within a certain mileage will show.

 

P.S. Also as I forgot to mention before....

MySQL: 5.1.52

PHP: 5.2.17

Link to comment
Share on other sites

I'm on a train using my phone so no idea how accurate this code will be but this is the basic premise (might need to play around with brackets etc...)

 

$q = "SELECT store_id, subselect.distance, 
FROM (SELECT store_id, latitude, longitude, 
((DEGREES(ACOS(SIN(RADIANS($latitude)) * SIN(RADIANS(latitude)) + COS(RADIANS($latitude)) * 
COS(RADIANS(latitude)) * COS(RADIANS($longitude - longitude))))) * 69.09) 
AS distance FROM table) AS subselect WHERE subselect.distance < 1 
ORDER BY subselect.distance ASC LIMIT 10";

 

From your previous posts I seem to remember the dataset is quite extensive. If performance becomes an issue I'd recommend dynamically creating a smaller dataset each time based on a bounding box. E.g 10 miles from the current location as a square - this allows you to eliminate most of the irrelevant datapoints and only query against decimal numbers e.g. latitude BETWEEN 39.00 AND 41.00 (numbers from thin air btw). Then run the full query to get accurate values.

 

Let me know how you get on.

Link to comment
Share on other sites

 Share

×
×
  • Create New...