Wordpress Query: Sort posts nearest proximity to farthest


/ Published in: PHP
Save to your folder(s)

A quick query example for a wordpress database query that gets posts ordered by distance via the custom fields `latitude` and `longitude`.


Copy this code and paste it in your HTML
  1. $latitude = '30.050000';
  2. $longitude = '-100.025000';
  3.  
  4. $query = "
  5. SELECT ID, post_title,
  6. (
  7. (
  8. (
  9. acos(
  10. sin(
  11. (".$latitude."*pi()/180)
  12. ) * sin(
  13. (latitude.meta_value*pi()/180)
  14. ) + cos(
  15. (".$latitude."*pi()/180)
  16. ) * cos(
  17. (latitude.meta_value*pi()/180)
  18. ) * cos(
  19. (
  20. (".$longitude."- longitude.meta_value)*pi()/180
  21. )
  22. )
  23. )
  24. )*180/pi()
  25. )*60*1.1515
  26. ) AS distance
  27. FROM $wpdb->posts
  28. INNER JOIN $wpdb->postmeta latitude
  29. ON (ID = latitude.post_id AND latitude.meta_key = 'latitude')
  30. INNER JOIN $wpdb->postmeta longitude
  31. ON (ID = longitude.post_id AND longitude.meta_key = 'longitude')
  32. ORDER BY distance
  33. LIMIT 0,50;
  34. ";
  35. $posts = $wpdb->get_results($query, OBJECT);

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.