MySQL query... zip code locator...

elklabone

Hello,

I'm using a script to calculate distance to distributors for an e-commerce site.

I've got two tables:

table zipData has [zipcode][lon][lat]

table distributors has [name][address][zip_code]

The script I'm using takes a user-inputted zipcode and then finds all zip codes within a 60 mile radius based on latitude and longitude.

It can then calculate distance to nearest distributor. Here's the query that grabs the zip codes... it then loads all of the zip codes into an array. This basically grabs all zip codes within given radius and then it loads it into an array later in the script.

$query="SELECT zipcode FROM zipData WHERE (POW((69.1(lon-\"$lon\")cos($lat/57.3)),\"2\")+POW((69.1(lat-\"$lat\")),\"2\"))<($radius$radius) ";

I don't want it to grab a zip code out of table "zipData" unless that zip code is present in the table "distributors". Does that make sense? There's no need in grabbing a zip code out of the table "zipData" unless there's a distributor in that zip code... so is there an easy to way to add a statement on the end that will tell it to only "SELECT a zipcode from zipData IF it is in the radius AND the radius is listed in table distributors"??

BTW, the distributors table contains only about 3,000 rows... the zip codes table contains over 800,000 because of the (seemingly) crazy way the Canadians do zip codes.

Thanks in advance,

Mark

notionlogic

Unless I've misunderstood the question, instead of:

$query="SELECT zipcode FROM zipData WHERE (POW((69.1(lon-\"$lon\")cos($lat/57.3)),\"2\")+POW((69.1(lat-\"$lat\")),\"2\"))<($radius$radius) ";

you would need something like this:

$query="SELECT zipcode FROM zipData, distributors WHERE zipcode = zip_code and (POW((69.1(lon-\"$lon\")cos($lat/57.3)),\"2\")+POW((69.1(lat-\"$lat\")),\"2\"))<($radius$radius) ";

elklabone

OK, that's what I wanted... EXCEPT the performance wasn't what I expected.

Based upon some test data I just ran, the original query returns 12,660 rows in about 2.34 seconds.

The new and improved query returns just 8 results, which is what I wanted, but it took 52 seconds.

I was hoping to improve the performance of a script by getting fewer results back for the script to work with...but if the query takes almost a minute to run that's not going to help any...

Any ideas?

jeremuck

My dad has a SOAP service to do what you want.
Find the distance between any two places in the world.

check out services by TransactionalWeb or ehwest on www.xmethods.com

[deleted]

Have you added any indexes to the tables? Especially the zipdcode table would benefit from an index on the zipcodes-column.

Also, try running an EXPLAIN on the query, to see what your database has to say about it.

As for the query itself, SQL prefers single-quotes, you shouldn't quote numbers, and you can span it accross many lines to make it more readable:

SELECT zipcode
FROM zipData, distributors
WHERE zipData.zipcode = distributors.zip_code
AND (
POW((69.1(lon-$lon)cos($lat/57.3)),2)
+
POW((69.1*(lat-$lat)),2)
)
<
POW($radius,2)