WordPress 在共享主機上最佳化 Proximity-based 儲存位置搜尋？

問題描述

我有一個專案，我需要為客戶建立一個商店定位器。

我使用自定義的帖子型別 「restaurant-location」，並且我已經編寫了程式碼，以使用 Google Geocoding API(儲存 geocodes the US White House in JSON 和我已經將緯度和緯度儲存在自定義欄位中的連結) 對儲存在 postmeta 中的地址進行地理編碼。

我已經寫了一個 get_posts_by_geo_distance()函式，它返回一個帖子列表，按照這些位置在地理上最接近的位置使用我在這篇文章幻燈片中找到的公式。你可能會這樣呼叫我的函式 (我從一個固定的”source” lat /long 開始)：

include "wp-load.php";

$source_lat = 30.3935337;
$source_long = -86.4957833;

$results = get_posts_by_geo_distance(
    'restaurant-location',
    'geo_latitude',
    'geo_longitude',
    $source_lat,
    $source_long);

echo '<ul>';
foreach($results as $post) {
    $edit_url = get_edit_url($post->ID);
    echo "<li>{$post->distance}: <a href="http://$edit_url" target="_blank">{$post->location}</a></li>";
}
echo '</ul>';
return;

這裡是 get_posts_by_geo_distance()本身的功能：

function get_posts_by_geo_distance($post_type,$lat_key,$lng_key,$source_lat,$source_lng) {
    global $wpdb;
    $sql =<<<SQL
SELECT
    rl.ID,
    rl.post_title AS location,
    ROUND(3956*2*ASIN(SQRT(POWER(SIN(({$source_lat}-abs(lat.lat))*pi()/180/2),2)+
    COS({$source_lat}*pi()/180)*COS(abs(lat.lat)*pi()/180)*
    POWER(SIN(({$source_lng}-lng.lng)*pi()/180/2),2))),3) AS distance
FROM
    wp_posts rl
    INNER JOIN (SELECT post_id,CAST(meta_value AS DECIMAL(11,7)) AS lat FROM wp_postmeta lat WHERE lat.meta_key='{$lat_key}') lat ON lat.post_id = rl.ID
    INNER JOIN (SELECT post_id,CAST(meta_value AS DECIMAL(11,7)) AS lng FROM wp_postmeta lng WHERE lng.meta_key='{$lng_key}') lng ON lng.post_id = rl.ID
WHERE
    rl.post_type='{$post_type}' AND rl.post_name<>'auto-draft'
ORDER BY
    distance
SQL;
    $sql = $wpdb->prepare($sql,$source_lat,$source_lat,$source_lng);
    return $wpdb->get_results($sql);
}

我擔心的是，SQL 可以像你一樣，就像 non-optimized 一樣。由於源地理位置是可更改的，並且沒有一組有限的源地理資料快取，所以 MySQL 不能由任何可用的索引進行排序。目前，我很高興能夠最佳化它。

考慮到我已經做了這個問題是：你將如何最佳化這個 use-case？

如果一個更好的解決方案會讓我丟擲來，我保留我做的一切並不重要。除了需要像安裝 Sphinx 伺服器或需要自定義 MySQL 配置的任何東西之外，我可以考慮幾乎任何解決方案。基本上，解決方案需要能夠在任何簡單的香草 WordPress 安裝上工作。 (也就是說，如果有人想為其他可能能夠獲得更高階和後代的人列出替代解決方案，那將是巨大的。)

找到資源

FYI，我做了一些研究，而不是再次做研究，而不是你釋出任何這些連結作為答案，我會繼續包括他們。

• http://jebaird.com/blog/calculating-distance-miles-latitude-and-longitude

• http://wordpress.org/extend/plugins/geolocation/screenshots/

• http://code.google.com/apis/maps/articles/phpsqlsearch.html

• http://www.rooftopsolutions.nl/blog/229

• http://planet.mysql.com/entry/?id=18085

• http://blog.peoplesdns.com/archives/24

• http://www.petefreitag.com/item/622.cfm

• http://www.phpro.org/tutorials/Geo-Targetting-With-PHP-And-MySQL.html

• http://forum.geonames.org/gforum/posts/list/692.page

• http://forums.mysql.com/list.php?23

• http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

• http://developer.yahoo.com/maps/rest/V1/geocode.html

• http://geocoder.us/

關於獅身人面像搜尋

• http://sphinxsearch.com/

• https://launchpad.net/wp-sphinx-plugin

• http://forums.site5.com/showthread.php?t=28981

• http://wordpress.org/extend/plugins/wordpress-sphinx-plugin/

• http://wordpress.org/extend/plugins/sphinx-search/

• http://www.mysqlperformanceblog.com/2008/02/15/mysql-performance-blog-now-uses-sphinx-for-site-search/

最佳解決方案

你需要什麼精度？如果它是一個國家/全國範圍的搜尋，也許你可以做一個 lat-lon 來壓縮查詢，並預先計算距離從 zip 區域到餐廳的拉鍊區域。如果您需要準確的距離，那將不是一個好的選擇。

你應該研究一個 Geohash 解決方案，在維基百科的文章中有一個連結到一個 PHP 庫來編碼解碼長度到 geohashs 。

在這裡，您有一個很好的文章，解釋 Google App Engine 中的原因及其使用方法 (Python 程式碼，但易於遵循) 由於需要在 GAE 中使用 geohash，您可以找到一些很好的 python 庫和示例。

正如這篇部落格文章解釋的那樣，使用 geohash 的優點是可以在該欄位的 MySQL 表上建立一個索引。

次佳解決方案

這可能對你來說太晚了，但是我也要用 a similar answer as I gave to this related question 來回復，所以未來的訪問者可以參考這兩個問題。

我不會將這些值儲存在帖子後設資料表中，或者至少不僅在這裡。你想要一個表與 post_id，lat，lon 列，所以你可以放置一個索引的 lat, lon 和查詢。這不應該是太難以保持最新的掛鉤儲存和更新。

查詢資料庫時，您可以在起始點周圍定義一個邊界框，因此您可以對該框的 North-South 和 East-West 邊框之間的所有 lat, lon 對進行有效的查詢。

得到這個減少的結果後，您可以進行更先進的 (圓形或實際的行車路線) 距離計算，以過濾出邊界角落中的位置，並進一步遠離您的需要。

在這裡，您可以找到一個在管理區域工作的簡單程式碼示例。您需要自己建立額外的資料庫表。程式碼是從大多數到最不重要的。

<?php
/*
Plugin Name: Monkeyman geo test
Plugin URI: http://www.monkeyman.be
Description: Geolocation test
Version: 1.0
Author: Jan Fabry
*/

class Monkeyman_Geo
{
    public function __construct()
    {
        add_action('init', array(&$this, 'registerPostType'));
        add_action('save_post', array(&$this, 'saveLatLon'), 10, 2);

        add_action('admin_menu', array(&$this, 'addAdminPages'));
    }

    /**
     * On post save, save the metadata in our special table
     * (post_id INT, lat DECIMAL(10,5), lon DECIMAL (10,5))
     * Index on lat, lon
     */
    public function saveLatLon($post_id, $post)
    {
        if ($post->post_type != 'monkeyman_geo') {
            return;
        }
        $lat = floatval(get_post_meta($post_id, 'lat', true));
        $lon = floatval(get_post_meta($post_id, 'lon', true));

        global $wpdb;
        $result = $wpdb->replace(
            $wpdb->prefix . 'monkeyman_geo',
            array(
                'post_id' => $post_id,
                'lat' => $lat,
                'lon' => $lon,
            ),
            array('%s', '%F', '%F')
        );
    }

    public function addAdminPages()
    {
        add_management_page( 'Quick location generator', 'Quick generator', 'edit_posts', __FILE__  . 'generator', array($this, 'doGeneratorPage'));
        add_management_page( 'Location test', 'Location test', 'edit_posts', __FILE__ . 'test', array($this, 'doTestPage'));

    }

    /**
     * Simple test page with a location and a distance
     */
    public function doTestPage()
    {
        if (!array_key_exists('search', $_REQUEST)) {
            $default_lat = ini_get('date.default_latitude');
            $default_lon = ini_get('date.default_longitude');

            echo <<<EOF
<form action="" method="post">
    <p>Center latitude: <input size="10" name="center_lat" value="{$default_lat}"/>
        <br/>Center longitude: <input size="10" name="center_lon" value="{$default_lon}"/>
        <br/>Max distance (km): <input size="5" name="max_distance" value="100"/></p>
    <p><input type="submit" name="search" value="Search!"/></p>
</form>
EOF;
            return;
        }
        $center_lon = floatval($_REQUEST['center_lon']);
        $center_lat = floatval($_REQUEST['center_lat']);
        $max_distance = floatval($_REQUEST['max_distance']);

        var_dump(self::getPostsUntilDistanceKm($center_lon, $center_lat, $max_distance));
    }

    /**
     * Get all posts that are closer than the given distance to the given location
     */
    public static function getPostsUntilDistanceKm($center_lon, $center_lat, $max_distance)
    {
        list($north_lat, $east_lon, $south_lat, $west_lon) = self::getBoundingBox($center_lat, $center_lon, $max_distance);

        $geo_posts = self::getPostsInBoundingBox($north_lat, $east_lon, $south_lat, $west_lon);

        $close_posts = array();
        foreach ($geo_posts as $geo_post) {
            $post_lat = floatval($geo_post->lat);
            $post_lon = floatval($geo_post->lon);
            $post_distance = self::calculateDistanceKm($center_lat, $center_lon, $post_lat, $post_lon);
            if ($post_distance < $max_distance) {
                $close_posts[$geo_post->post_id] = $post_distance;
            }
        }
        return $close_posts;
    }

    /**
     * Select all posts ids in a given bounding box
     */
    public static function getPostsInBoundingBox($north_lat, $east_lon, $south_lat, $west_lon)
    {
        global $wpdb;
        $sql = $wpdb->prepare('SELECT post_id, lat, lon FROM ' . $wpdb->prefix . 'monkeyman_geo WHERE lat < %F AND lat > %F AND lon < %F AND lon > %F', array($north_lat, $south_lat, $west_lon, $east_lon));
        return $wpdb->get_results($sql, OBJECT_K);
    }

    /* Geographical calculations: distance and bounding box */

    /**
     * Calculate the distance between two coordinates
     * http://stackoverflow.com/questions/365826/calculate-distance-between-2-gps-coordinates/1416950#1416950
     */
    public static function calculateDistanceKm($a_lat, $a_lon, $b_lat, $b_lon)
    {
        $d_lon = deg2rad($b_lon - $a_lon);
        $d_lat = deg2rad($b_lat - $a_lat);
        $a = pow(sin($d_lat/2.0), 2) + cos(deg2rad($a_lat)) * cos(deg2rad($b_lat)) * pow(sin($d_lon/2.0), 2);
        $c = 2 * atan2(sqrt($a), sqrt(1-$a));
        $d = 6367 * $c;

        return $d;
    }

    /**
     * Create a box around a given point that extends a certain distance in each direction
     * http://www.colorado.edu/geography/gcraft/warmup/aquifer/html/distance.html
     *
     * @todo: Mind the gap at 180 degrees!
     */
    public static function getBoundingBox($center_lat, $center_lon, $distance_km)
    {
        $one_lat_deg_in_km = 111.321543; // Fixed
        $one_lon_deg_in_km = cos(deg2rad($center_lat)) * 111.321543; // Depends on latitude

        $north_lat = $center_lat + ($distance_km / $one_lat_deg_in_km);
        $south_lat = $center_lat - ($distance_km / $one_lat_deg_in_km);

        $east_lon = $center_lon - ($distance_km / $one_lon_deg_in_km);
        $west_lon = $center_lon + ($distance_km / $one_lon_deg_in_km);

        return array($north_lat, $east_lon, $south_lat, $west_lon);
    }

    /* Below this it's not interesting anymore */

    /**
     * Generate some test data
     */
    public function doGeneratorPage()
    {
        if (!array_key_exists('generate', $_REQUEST)) {
            $default_lat = ini_get('date.default_latitude');
            $default_lon = ini_get('date.default_longitude');

            echo <<<EOF
<form action="" method="post">
    <p>Number of posts: <input size="5" name="post_count" value="10"/></p>
    <p>Center latitude: <input size="10" name="center_lat" value="{$default_lat}"/>
        <br/>Center longitude: <input size="10" name="center_lon" value="{$default_lon}"/>
        <br/>Max distance (km): <input size="5" name="max_distance" value="100"/></p>
    <p><input type="submit" name="generate" value="Generate!"/></p>
</form>
EOF;
            return;
        }
        $post_count = intval($_REQUEST['post_count']);
        $center_lon = floatval($_REQUEST['center_lon']);
        $center_lat = floatval($_REQUEST['center_lat']);
        $max_distance = floatval($_REQUEST['max_distance']);

        list($north_lat, $east_lon, $south_lat, $west_lon) = self::getBoundingBox($center_lat, $center_lon, $max_distance);


        add_action('save_post', array(&$this, 'setPostLatLon'), 5);
        $precision = 100000;
        for ($p = 0; $p < $post_count; $p++) {
            self::$currentRandomLat = mt_rand($south_lat * $precision, $north_lat * $precision) / $precision;
            self::$currentRandomLon = mt_rand($west_lon * $precision, $east_lon * $precision) / $precision;

            $location = sprintf('(%F, %F)', self::$currentRandomLat, self::$currentRandomLon);

            $post_data = array(
                'post_status' => 'publish',
                'post_type' => 'monkeyman_geo',
                'post_content' => 'Point at ' . $location,
                'post_title' => 'Point at ' . $location,
            );

            var_dump(wp_insert_post($post_data));
        }
    }

    public static $currentRandomLat = null;
    public static $currentRandomLon = null;

    /**
     * Because I didn't know how to save meta data with wp_insert_post,
     * I do it here
     */
    public function setPostLatLon($post_id)
    {
        add_post_meta($post_id, 'lat', self::$currentRandomLat);
        add_post_meta($post_id, 'lon', self::$currentRandomLon);
    }

    /**
     * Register a simple post type for us
     */
    public function registerPostType()
    {
        register_post_type(
            'monkeyman_geo',
            array(
                'label' => 'Geo Location',
                'labels' => array(
                    'name' => 'Geo Locations',
                    'singular_name' => 'Geo Location',
                    'add_new' => 'Add new',
                    'add_new_item' => 'Add new location',
                    'edit_item' => 'Edit location',
                    'new_item' => 'New location',
                    'view_item' => 'View location',
                    'search_items' => 'Search locations',
                    'not_found' => 'No locations found',
                    'not_found_in_trash' => 'No locations found in trash',
                    'parent_item_colon' => null,
                ),
                'description' => 'Geographical locations',
                'public' => true,
                'exclude_from_search' => false,
                'publicly_queryable' => true,
                'show_ui' => true,
                'menu_position' => null,
                'menu_icon' => null,
                'capability_type' => 'post',
                'capabilities' => array(),
                'hierarchical' => false,
                'supports' => array(
                    'title',
                    'editor',
                    'custom-fields',
                ),
                'register_meta_box_cb' => null,
                'taxonomies' => array(),
                'permalink_epmask' => EP_PERMALINK,
                'rewrite' => array(
                    'slug' => 'locations',
                ),
                'query_var' => true,
                'can_export' => true,
                'show_in_nav_menus' => true,
            )
        );
    }
}

$monkeyman_Geo_instance = new Monkeyman_Geo();

參考文獻

• Optimizing a Proximity-based Store Location Search on a Shared Web Host?

注：本文內容整合自 Google/Baidu/Bing 輔助翻譯的英文資料結果。如果您對結果不滿意，可以加入我們改善翻譯效果：薇曉朵技術論壇。