How to calculate distance from lat long and select the shortest distance in php

Question

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:

my wp_posts :

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

-

Possible duplicate of [Sorting users by distance in mysql using st\_distance](http://stackoverflow.com/questions/28853778/sorting-users-by-distance-in-mysql-using-st-distance) — e4c5, Jan 05 '17 at 07:19

score 1 · Accepted Answer · answered Jan 05 '17 at 07:43

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.

Note that the `.' '.`s are unnecessary. And you might as well use JOIN, because a null value is meaningless in this equation — Strawberry, Jan 05 '17 at 08:36

How to calculate distance from lat long and select the shortest distance in php

1 Answers1