如何在WordPress中通过MySQL关联查询结合地理编码按距离找门店？

Alright, let's figure out how to find nearby stores based on the user's location using your WordPress setup. You've got a custom post type store with latitude/longitude stored in wp_postmeta as store_lat and store_lng—perfect, here are two solid approaches to implement this:

If you need direct control over the query (great for performance with large datasets), use the Haversine formula to calculate the distance between the user's coordinates and each store. This formula accounts for the Earth's spherical shape, giving accurate distance measurements.

Here's the query you can use:

SELECT 
    p.ID,
    p.post_title,
    pm_lat.meta_value AS store_latitude,
    pm_lng.meta_value AS store_longitude,
    -- Calculate distance in kilometers (replace 6371 with 3956 for miles)
    6371 * 2 * ASIN(SQRT(
        POWER(SIN(({USER_LAT} - CAST(pm_lat.meta_value AS DECIMAL(10,6))) * PI()/180 / 2), 2) +
        COS({USER_LAT} * PI()/180) * COS(CAST(pm_lat.meta_value AS DECIMAL(10,6)) * PI()/180) *
        POWER(SIN(({USER_LNG} - CAST(pm_lng.meta_value AS DECIMAL(10,6))) * PI()/180 / 2), 2)
    )) AS distance_km
FROM 
    wp_posts p
LEFT JOIN 
    wp_postmeta pm_lat ON p.ID = pm_lat.post_id AND pm_lat.meta_key = 'store_lat'
LEFT JOIN 
    wp_postmeta pm_lng ON p.ID = pm_lng.post_id AND pm_lng.meta_key = 'store_lng'
WHERE 
    p.post_type = 'store'
    AND p.post_status = 'publish'
    AND pm_lat.meta_value IS NOT NULL
    AND pm_lng.meta_value IS NOT NULL
HAVING 
    distance_km <= {MAX_DISTANCE} -- Replace with your max distance (e.g., 10 for 10km)
ORDER BY 
    distance_km ASC;

Notes for this query: #

• Replace {USER_LAT}, {USER_LNG}, and {MAX_DISTANCE} with actual values from your user's location.
• We cast meta_value to DECIMAL(10,6) to ensure numerical calculations work correctly (since postmeta stores values as strings by default).
• Adjust the Earth radius (6371 km / 3956 miles) based on your preferred unit of measurement.

For better integration with WordPress's ecosystem (and to avoid writing raw SQL directly), you can hook into the posts_clauses filter to modify a standard WP_Query and add distance calculations.

Here's a complete example:

// Define user's location and max distance
$user_latitude = 40.7128; // Example: New York City latitude
$user_longitude = -74.0060; // Example: New York City longitude
$max_distance_km = 10; // Max distance to search (10km)

// Base WP_Query arguments
$store_query_args = array(
    'post_type' => 'store',
    'post_status' => 'publish',
    'posts_per_page' => -1, // Get all matching stores (adjust as needed)
    'meta_query' => array(
        'relation' => 'AND',
        array('key' => 'store_lat', 'compare' => 'EXISTS'),
        array('key' => 'store_lng', 'compare' => 'EXISTS')
    )
);

// Filter the query to add distance calculations
add_filter('posts_clauses', function($clauses, $query) use ($user_latitude, $user_longitude, $max_distance_km) {
    global $wpdb;

    // Join postmeta twice to get latitude and longitude values
    $clauses['join'] .= " LEFT JOIN {$wpdb->postmeta} pm_lat ON {$wpdb->posts}.ID = pm_lat.post_id AND pm_lat.meta_key = 'store_lat'";
    $clauses['join'] .= " LEFT JOIN {$wpdb->postmeta} pm_lng ON {$wpdb->posts}.ID = pm_lng.post_id AND pm_lng.meta_key = 'store_lng'";

    // Add distance calculation to the SELECT fields
    $clauses['fields'] .= ", 6371 * 2 * ASIN(SQRT(
        POWER(SIN(({$user_latitude} - CAST(pm_lat.meta_value AS DECIMAL(10,6))) * PI()/180 / 2), 2) +
        COS({$user_latitude} * PI()/180) * COS(CAST(pm_lat.meta_value AS DECIMAL(10,6)) * PI()/180) *
        POWER(SIN(({$user_longitude} - CAST(pm_lng.meta_value AS DECIMAL(10,6))) * PI()/180 / 2), 2)
    )) AS distance_km";

    // Filter stores by max distance
    $clauses['having'] = "distance_km <= {$max_distance_km}";

    // Sort results by closest first
    $clauses['orderby'] = "distance_km ASC";
    $clauses['order'] = ""; // Override default ordering

    return $clauses;
}, 10, 2);

// Run the query
$nearby_stores = new WP_Query($store_query_args);

// Remove the filter to avoid affecting other queries
remove_filter('posts_clauses', '__return_false', 10);

// Loop through results
if ($nearby_stores->have_posts()) {
    while ($nearby_stores->have_posts()) {
        $nearby_stores->the_post();
        // Access the calculated distance directly from the post object
        $distance = round($nearby_stores->post->distance_km, 2);
        echo "<div class='store-item'>";
        echo "<h3>" . get_the_title() . "</h3>";
        echo "<p>Distance: {$distance} km</p>";
        echo "</div>";
    }
    wp_reset_postdata();
} else {
    echo "<p>No nearby stores found.</p>";
}

Key Tips for Production: #

• Performance Optimization: If you have hundreds/thousands of stores, consider adding a database index on wp_postmeta.meta_key and wp_postmeta.meta_value (for numerical values) to speed up the query.
• Validation: Ensure that store_lat and store_lng are stored as valid numerical values (latitude: -90 to 90, longitude: -180 to 180) during the geocoding step.
• User Location: To get the user's actual location, you can use IP-based geocoding (server-side) or browser-based geolocation (client-side via JavaScript), then pass those coordinates to your query.

内容的提问来源于stack exchange，提问作者Subrata Sarkar