wordpress get posts within radius defiend by user

Question

Details

Ok. So i have a custom field on my posts called Location. This location is geocoded into LAT LNG values.

I also have a custom field for users that does the exact same thing. The user can also define a radius in miles or kilometers. only posts within this radius are to show up.

I can calculate the distance between the posts lat lng and the users lag lng inside my query_posts loop by getting the posts meta data(lat long) and compare it to the users.

If i do this inside the loop and wrap an if statement that basically says if less then radius display post i get only posts with in the radius.

Problem
Lets say i add a posts_per_page of 5 and the first 5 posts to loop are not within the defined radius it will display 0 posts. If the first 3 are not within the radius i will get 2 posts. And so on.

Need solution

1) Obviously the best way to go about this would be inside my query. I'm ok with using wp_query vs query_posts however i'm not 100% sure how i can run my PHP function to calculate the distance between the users and posts lat + lng's right inside the query.

2) Is there a way to increase query_posts post_per_page by 1 if my if statement is not met inside an else?

Conclusion

Thanks in advance for anyone that has some insight on this. If i did not give enough info just let me know and i will try to explain more clearly. I could post code but im not looking for code in return. Just a point in the right direction.

Cheers!

Answer 1

Here is my final query. Does exactly what i need it to do. I have created a custom table in my database to store lat and lng info from posts VS adding them to the post meta table so i could join the 2 and get the distance from the post to the user. Any question let me know :)

$querystr = '
            SELECT *,
            (((acos(sin(('.$userLat.'*pi()/180)) * sin((lat*pi()/180))+cos(('.$userLat.'*pi()/180)) * cos((lat*pi()/180)) * cos((('.$userLng.'- lng)*pi()/180))))*180/pi())*60*1.1515) as distance
            FROM wp_posts, tbl_post_lat_lng
            WHERE wp_posts.ID = tbl_post_lat_lng.post_id
            AND wp_posts.post_status = "publish"
            AND wp_posts.post_type = "post"
            AND distance <= '.$userRadius.'
            ORDER BY wp_posts.post_date DESC
            LIMIT 5
         ';