How many db recs can I put in an array?

[Anon]

I am writing a web page to find the closest retail stores to a given zip code by cross referencing a zip code db with lat and long coordinates.

The calculation to find the distance between 2 zip codes is not trivial and I want to minimize the number of queries on the MySQL database.

I need to know how many total stores there are that meet the criteria and bring back only x number at a time. I am using a navigator to go to the next page of records when the total exceeds the limit per screen I have set.

Currently I am doing this in 2 queries, 1 to get the total number of records that meet the criteria, and then another to bring back the subset of records I want out of the list.

The other way I have considered doing this is to bring back all of the records and store the results into an array so I only do one query.

But there is a lot of data poetentially to bring back.

Is there a rule of thumb or guideline for determining an acceptable amount of data I can store in an array before I use up too much server memory?

Any other thoughts?

Thanks.

[tomhath]

We had a similar problem one time that was solved pretty neatly by a good mathematician. Basically you assume a cartesian coordinate system (origin at the center of the earth, x,y plane on the equator with x pointing at the prime meridian and z through the north pole), so every zipcode has an (x,y,z) coordinate. For your application you could probably use distances in nautical miles and assume the earth is a perfect sphere. Then your query returns rows ordered by the dot product of your location and the (x,y,z) of the row's location (the dot product is the cosine of the angle between the two vectors, so you can find exactly the rows you want in a single query, but of course you have to calculate the dot product in the select).

That was a while ago and I don't recall all the math, but if you understand the above it's easier than messing with lat/long coordinates.