CODEWITHSUNDEEP
javascriptphpmysqldatabase
APSB
APSB

Reputation: 587

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

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 : data1

and here my wp_postmeta :

here

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

Answers (1)

smozgur
smozgur

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

Related Questions