WordPress和半正矢公式

t98cgbkg  于 2023-03-29  发布在  WordPress
关注(0)|答案(2)|浏览(120)

我试图在WordPress中使用半正矢公式,使用自定义文章类型“商店”,自定义字段包含latitudelongitudestreet_address仅用于显示。我一直使用this tutorial作为指导。
这是我试图与值得查询...

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

但这个回来了...

Unknown column 'latitude' in 'field list'

我也尝试过使用query here的一个变体,但是我得到了关于“未知列”的相同错误。
任何建议都非常感谢!

fykwrbwg

fykwrbwg1#

尝试在公式中使用原始列名而不是新别名,如latitude =>pm2.meta_valuelatitude=>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

编辑

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
pdsfdshx

pdsfdshx2#

假设您有一个名为listings的自定义帖子类型,并且您已经为lat/lng q_loc_map_latq_loc_map_lng添加了 meta值,并且正在从googleMaps API嵌入式搜索表单将一些搜索变量传递到url中...

// 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();

相关问题