Another Query From Hell -

[ppetree]

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((".<strong>$lat</strong>."*pi()/180)) * sin((`lat`*pi()/180))+cos((".<strong>$lat</strong>."*pi()/180)) * cos((`lat`*pi()/180))* cos(((".<strong>$lon</strong>."- `lon`)*pi()/180))))*180/pi())*60*1.1515) AS dist_x FROM `message` HAVING dist_x<=".<strong>$distance</strong>." AND dist_x<=<strong>$distance</strong> 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?

[smantscheff]

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?

[almostbob]

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"

[ppetree]

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.

[ppetree]

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?