OK, I admit I am completely clueless on how to perform this one.

In one table I have a list zip codes and the user will select one or more from this list. Once I have those zip codes, I need to perform my query and this is where it gets rough.

In my users table I have a million records that have a geocoded latitude and longitude. What I need to do is return a list of "zones"

Let's say the user selects a zip of 33308. In 33308 I have 5,000 users. I need to break the 5,000 users down in to maybe a dozen zones of 'x' distance with 1 user being the center of that zone and any other users in that zone being ommited from the results.

Then the next row returned being a new zone with one user at the center and all other users in that zone being omitted. Wash, rinse, repeat until I have all the zones.

``SELECT *,(((acos(sin((".[B]\$lat[/B]."*pi()/180)) * sin((`lat`*pi()/180))+cos((".[B]\$lat[/B]."*pi()/180)) * cos((`lat`*pi()/180))* cos(((".[B]\$lon[/B]."- `lon`)*pi()/180))))*180/pi())*60*1.1515) AS dist_x FROM `message` HAVING dist_x<=".[B]\$distance[/B]." AND dist_x<=[B]\$distance[/B] ORDER BY dist_x ASC";``

With the above query I have no problem querying ALL users within 'x' distance of a specific address, what I dont know how to do is only get one user and make that user the center of a "zone" and omit all other users from that "zone" and then get another zone. I prefer to do as much as this in mysql vs php for speed/performance reasons but I've no clue how to do this in php either! LOL

Suggestions?

## All 4 Replies

I do not understand your select formula, but it seems to me that you have to introduce a zone function by which you can GROUP users BY zones and then select only the first (or n-th) user from each group. So the question is: What exactly is a zone?

``SELECT *,(((acos(sin((".\$lat."*pi()/180)) * sin((`lat`*pi()/180))+cos((".\$lat."*pi()/180)) * cos((`lat`*pi()/180))* cos(((".\$lon."- `lon`)*pi()/180))))*180/pi())*60*1.1515) AS dist_x FROM `message` HAVING dist_x<=".\$distance." AND dist_x<=\$distance ORDER BY dist_x ASC limit 1"``

I do not understand your select formula, but it seems to me that you have to introduce a zone function by which you can GROUP users BY zones and then select only the first (or n-th) user from each group. So the question is: What exactly is a zone?

The select function is pretty niffty... basically it selects all users within \$distance of \$lat/\$lon.

A zone is an arbitrary geographical area of 'x' distance. Let's say you have a city and you want to divide it into 4ths so you would have 4 zones. Or you could have a county and want to divide it into 8 zones. Or you could have a state and want 29 zones. The user would decide the number of zones for their geographical coverage.

``SELECT *,(((acos(sin((".\$lat."*pi()/180)) * sin((`lat`*pi()/180))+cos((".\$lat."*pi()/180)) * cos((`lat`*pi()/180))* cos(((".\$lon."- `lon`)*pi()/180))))*180/pi())*60*1.1515) AS dist_x FROM `message` HAVING dist_x<=".\$distance." AND dist_x<=\$distance ORDER BY dist_x ASC limit 1"``

Bob's that's almost genious! LOL That gets me 1 person within \$distance of point x/y. Now how do I divide it into zones?

The detail I gave in the post above is relevant. How do I divide zip code A or City B or County M or State S into 'x' zones?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.