Wordpress and Haversine Formula

Question

I am trying to use the Haversine Formula inside WordPress, using a custom post type "stores" with custom fields holding the latitude, longitude and the street_address for display only. I have been using this tutorial as a guide.

Here is the query I am trying to worth with...

SELECT wp_posts.ID, 
       wp_posts.post_title, 
       pm1.meta_value as address, 
       pm2.meta_value as latitude, 
       pm3.meta_value as longitude, ( 6371 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin( radians( latitude ) ) ) ) AS distance   
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='street_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='latitude') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='longitude') 
WHERE wp_posts.post_type = 'stores' 
AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20

But this returns...

Unknown column 'latitude' in 'field list'

I have also tried using a variation of the query here, but I get the same error regarding "Unknown column".

Any advice greatly appreciated!

M Khalid Junaid · Answer 1 · 2013-12-27T06:57:13.437

Try using original column names instead of new aliases in the formula like latitude =>pm2.meta_value and latitude=>pm3.meta_value

SELECT wp_posts.ID, 
       wp_posts.post_title, 
       pm1.meta_value AS address, 
       pm2.meta_value AS latitude, 
       pm3.meta_value AS longitude, 
       ( 6371 * ACOS( COS( RADIANS(37) ) * COS( RADIANS( pm2.meta_value ) ) * COS( RADIANS( pm3.meta_value ) - RADIANS(-122) ) + SIN( RADIANS(37) ) * SIN( RADIANS( pm2.meta_value ) ) ) ) AS distance   
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='street_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='latitude') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='longitude') 
WHERE wp_posts.post_type = 'stores' 
AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20

EDIT

SELECT 
  wp_posts.ID,
  wp_posts.post_title,
  pm1.meta_value AS address,
  pm2.meta_value AS latitude,
  pm3.meta_value AS longitude,
  (
    6371 * ACOS(
      COS(RADIANS(37)) * COS(
        RADIANS(
          CASE
            WHEN pm2.meta_value = '' 
            THEN 0 
            WHEN pm2.meta_value IS NULL 
            THEN 0 
            ELSE pm2.meta_value 
          END
        )
      ) * COS(
        RADIANS(
          CASE
            WHEN pm3.meta_value = '' 
            THEN 0 
            WHEN pm3.meta_value IS NULL 
            THEN 0 
            ELSE pm3.meta_value 
          END
        ) - RADIANS(- 122)
      ) + SIN(RADIANS(37)) * SIN(
        RADIANS(
          CASE
            WHEN pm2.meta_value = '' 
            THEN 0 
            WHEN pm2.meta_value IS NULL 
            THEN 0 
            ELSE pm2.meta_value 
          END
        )
      )
    )
  ) AS distance 
FROM
  wp_posts 
  LEFT JOIN wp_postmeta AS pm1 
    ON (
      wp_posts.ID = pm1.post_id 
      AND pm1.meta_key = 'street_address'
    ) 
  LEFT JOIN wp_postmeta AS pm2 
    ON (
      wp_posts.ID = pm2.post_id 
      AND pm2.meta_key = 'latitude'
    ) 
  LEFT JOIN wp_postmeta AS pm3 
    ON (
      wp_posts.ID = pm3.post_id 
      AND pm3.meta_key = 'longitude'
    ) 
WHERE wp_posts.post_type = 'stores' 
  AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0, 20

No errors, but it appears pm2.meta_value and pm3.meta_value are empty? — cab121, Dec 27 '13 at 06:45
@cab121 see my updated query i have used case to check empty values ,hope if this helps — M Khalid Junaid, Dec 27 '13 at 06:57
With the case statements in place, I get no results at all. Without the case statement I was getting some results that seem incorrect. If I try to print a field to screen using "pm2.meta_key" it is blank. But if I tried using the "latitude" alias however, it returns the correct value. Strange? — cab121, Dec 27 '13 at 07:29
@cab121 can you add your tables with some data using [**sql fiddle**](http://sqlfiddle.com/) then it will be easier to sort out ,in fiddle add only `wp_posts` and `wp_postmeta` table with some sample data and give the link of fiddle to me ,and also add in your question what results you need to return from query thanks — M Khalid Junaid, Dec 27 '13 at 18:42
Sorry for the delay and thanks for your assistance. I ended up using a slightly different approach seen here: http://stackoverflow.com/a/13251492/1434456 — cab121, Jan 17 '14 at 07:36

score 0 · Answer 2 · answered Mar 28 '23 at 21:16

Assumes you have a custom post type named listings and you have added meta values for lat/lng q_loc_map_lat q_loc_map_lng and are passing some search vars into the url from perhaps a googleMaps API embedded search form...

// get search vars from url
$near_lat = $_GET['near_lat'];
$near_lng = $_GET['near_lng'];
$search_radius = $_GET['search_radius'];
    
global $wpdb;

// Radius of the earth 3959 miles or 6371 kilometers.
$earth_radius = 3959;

$sql = $wpdb->prepare( "
    SELECT DISTINCT
        p.ID,
        ( %d * acos(
        cos( radians( %s ) )
        * cos( radians( map_lat.meta_value ) )
        * cos( radians( map_lng.meta_value ) - radians( %s ) )
        + sin( radians( %s ) )
        * sin( radians( map_lat.meta_value ) )
        ) )
        AS distance
    FROM $wpdb->posts p
    INNER JOIN $wpdb->postmeta map_lat ON p.ID = map_lat.post_id
    INNER JOIN $wpdb->postmeta map_lng ON p.ID = map_lng.post_id
    WHERE 1 = 1
    AND p.post_type = 'listing'
    AND p.post_status = 'publish'
    AND map_lat.meta_key = 'q_loc_map_lat'
    AND map_lng.meta_key = 'q_loc_map_lng'
    HAVING distance < %s
    ORDER BY distance ASC",
    $earth_radius,
    $near_lat,
    $near_lng,
    $near_lat,
    $search_radius
);

// Uncomment to echo, paste into phpMyAdmin, and debug.
//echo $sql; die();

$location_results = $wpdb->get_results( $sql );
$details = array();
//print_r($location_results); die();

Wordpress and Haversine Formula

2 Answers2