Reputation: 587
Here I want to calculate the distance between locations, after calculating the subsequent data will be displayed only has the smallest distance. but i am confused how to make based on data such as the following:
and here my wp_postmeta :
and here my code :
<?php
$latitude = "23.139422";
$longitude = "-82.382617";
mysql_query( 'SELECT ( 3959 * acos( cos( radians( '.$latitude.' ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( '.$longitude.' ) ) + sin( radians( '.$latitude.' ))
* sin( radians( latitude ) ) ) )
AS distance from wp_posts
HAVING distance <= 100
ORDER BY distance ASC'
)
i dont know how to i take the latitude and longitude by id the post_id
have someone tell me what i need imporve in my code so its can be work like what i want ? Thanks kyu
-
Upvotes: 1
Views: 103
Reputation: 1812
This is how you get latitude and longitude from meta:
SELECT wp_posts.ID, pm1.meta_value, pm2.meta_value
FROM wp_posts
LEFT JOIN wp_postmeta AS pm1
ON pm1.post_id = wp_posts.ID
AND pm1.meta_key = 'latitude'
LEFT JOIN wp_postmeta AS pm2
ON pm2.post_id = wp_posts.ID
AND pm2.meta_key = 'longitude';
If we try to apply this to your SQL statement (assuming your formulation is working):
SELECT ( 3959 * acos(cos( radians( '.$latitude.' ) ) * cos( radians( pm1.meta_value))
* cos( radians( pm2.meta_value ) - radians( '.$longitude.' ) )
+ sin( radians( '.$latitude.' ))
* sin( radians( pm1.meta_value) ) ) ) AS distance
FROM wp_posts
LEFT JOIN wp_postmeta AS pm1
ON pm1.post_id = wp_posts.ID
AND pm1.meta_key = 'latitude'
LEFT JOIN wp_postmeta AS pm2
ON pm2.post_id = wp_posts.ID
AND pm2.meta_key = 'longitude'
HAVING distance <= 100
ORDER BY distance ASC;
It might look complicated but it is not if you look at the first one closer. It should help you to understand how to retrieve data for the same record from the same table joining it twice by using different aliases (pm1 for latitude and pm2 for longitude).
Hope it helps.
Upvotes: 1