Need help with modifying zip code search query

[justride]

Does anybody have time helping me out? I have a query that calculates the distance between a given zip code and all other zipcodes in the db within a specified search radius. It works fine.

however if a user wants to search for Boston, MA within 20 miles i have a problem. We all know there are more than 1 zip code for soem cities, so i need to run this query multiple times and merge all the records found. The way I am doing it now is very inneficient and take way to much computation time.

I would like to nest it in the query soemhow where my calculation can calculate a number of times.

heres the current query that works for 1 zip code:

"SELECT Z2.TOWN,Z2.STATE,Z2.ZIPCODE, 
				    	TRUNCATE (acos( (sin( Z1.LATITUDE * 0.017453293 ) * sin( Z2.LATITUDE * 0.017453293 ) ) + (cos( Z1.LATITUDE * 0.017453293 ) * cos( Z2.LATITUDE * 0.017453293 ) * cos( (Z2.LONGITUDE * 0.017453293) - ( Z1.LONGITUDE * 0.017453293 ) ) )) *3956,1) AS DISTANCE 
			    		FROM DINEZIPCODES Z1,DINEZIPCODES Z2 
			    		WHERE Z1.ZIPCODE = '$zips[$i]' AND acos( (sin( Z1.LATITUDE * 0.017453293 ) * sin( Z2.LATITUDE * 0.017453293 ) ) + (cos( Z1.LATITUDE * 0.017453293 ) * cos( Z2.LATITUDE * 0.017453293 ) * cos( (Z2.LONGITUDE * 0.017453293) - ( Z1.LONGITUDE * 0.017453293 ) ) )) *3956 
			    		BETWEEN 0 AND '$radius' 
			    		ORDER BY DISTANCE DESC";

instead of using zips[$i] I want to have a string of zip codes. I just cant figure this out in mysql.

Thanks

 

[sleipnir214]

Is this question different from the one you asked in thread436-997976 ?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[justride]

pretty similar but i worded bad in the other and the subject was too broad. should i delete the other one?

 

[ericbrunson]

Assuming you're writing in PHP:

"where Z1.ZIPCODE in ( '" . implode( $zips, "', '" ) . "' )"

 

[sleipnir214]

Actually, you can't delete it. The best you can do is redflag it and explain to TT management why it should be deleted.


Anyway, I see that you're performing the calculation twice in your query and I assume this is because you can't use a calculated column in a WHERE clause. You can, however, use a calculated row in a HAVING clause. Have you tried something like:

SELECT
	Z2.TOWN,
	Z2.STATE,
	Z2.ZIPCODE, 
      TRUNCATE (acos( (sin( Z1.LATITUDE * 0.017453293 ) * sin( Z2.LATITUDE * 0.017453293 ) ) + (cos( Z1.LATITUDE * 0.017453293 ) * cos( Z2.LATITUDE * 0.017453293 ) * cos( (Z2.LONGITUDE * 0.017453293) - ( Z1.LONGITUDE * 0.017453293 ) ) )) *3956,1) AS DISTANCE 
FROM
	DINEZIPCODES Z1,
	DINEZIPCODES Z2 
WHERE
	Z1.ZIPCODE = '$zips[$i]'
HAVING 
	DISTANCE BETWEEN 0 AND '$radius' 
ORDER BY
	DISTANCE DESC

As to the "$zips[$i]" question. If $zips contains a list of ZIP codes in which "Z1.ZIPCODE" must be, then you might, in order to make the query return multiple records per run, use your scripting language to create an IN clause like:

...WHERE Z1.ZIPCODE IN (11111, 22222, 33333, 44444, 55555)



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[justride]

ok, this sounds great, this will work for an array of zips?
im going to try. will the implode work like this

zips[0] = "02921"
zips[1] = "12345"

when the where statement executes...

where Z1.ZIPCODE in (' '02921' '12345' )

is that what this will do?

thanks

 

[justride]

Ok, you guys rock, I found my issue.

I need to alter my algorithm....

This code gets too much info, I need to query zip code centroids for centers of cities. THats my issue.

I think I need to get a another database.

Any suggestions.

Thanks for the other tip by the way, that will come in handy too!!!

 

[sleipnir214]

Yes, implode() (I assume you're programming in PHP) should do it. But it won't be straightforward.

If $zip has the ZIP codes in it, then:

$a = "'" . implode ("','", $zip) . "'";

should put all the internal singlequotes and commas in place.

Want the best answers? Ask the best questions!

TANSTAAFL!!