Advanced Search

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`.

Expand | Embed | Plain Text

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.