MikeSchinkel
  • 0
Гуру

Оптимизация поиска местоположения магазина на основе близости на общем веб-узле?

  • 0

У меня есть проект, в котором мне нужно создать локатор магазина для клиента.

Я использую пользовательский тип сообщения » restaurant-location «, и я написал код для геокодирования адресов, хранящихся в postmeta, с помощью Google Geocoding API (вот ссылка, которая геокодирует Белый дом США в JSON, и я сохранил широту и долготу назад в настраиваемые поля.

Я написал get_posts_by_geo_distance() функцию, которая возвращает список постов в порядке ближайшего географического расположения, используя формулу, которую я нашел в слайд-шоу в этом посте. Вы можете вызвать мою функцию так (я начинаю с фиксированного «источника» 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="{$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 настолько неоптимизирован, насколько это возможно. MySQL не может упорядочивать по какому-либо доступному индексу, так как исходная география изменчива и нет конечного набора исходных гео для кэширования. В настоящее время я в тупике относительно способов его оптимизации.

Принимая во внимание то, что я уже сделал, возникает вопрос: как бы вы оптимизировали этот вариант использования?

Неважно, что я сохраняю все, что я сделал, если лучшее решение заставило бы меня выбросить это. Я открыт для рассмотрения практически любого решения, за исключением того, которое требует установки сервера Sphinx или чего-то еще, требующего индивидуальной конфигурации MySQL. По сути, решение должно работать с любой обычной установкой WordPress. (Тем не менее, было бы здорово, если бы кто-нибудь захотел перечислить альтернативные решения для других, которые могли бы стать более продвинутыми, и для потомков.)

Ресурсы найдены

К вашему сведению, я провел небольшое исследование по этому вопросу, поэтому вместо того, чтобы вы проводили исследование снова или вместо того, чтобы публиковать какие-либо из этих ссылок в качестве ответа, я продолжу и включу их.

Относительно поиска Sphinx

Share
  1. Какая точность вам нужна? если это поиск по штату/национальному масштабу, возможно, вы могли бы выполнить поиск по долготе и почтовому индексу и предварительно вычислить расстояние от области почтового индекса до области почтового индекса ресторана. Если вам нужны точные расстояния, это не будет хорошим вариантом.

    Вам следует изучить решение Geohash, в статье Википедии есть ссылка на библиотеку PHP для кодирования декодирования lat long в geohash.

    Здесь у вас есть хорошая статья, объясняющая, почему и как они используют его в Google App Engine (код Python, но его легко понять). Из-за необходимости использовать геохэш в GAE вы можете найти несколько хороших библиотек Python и примеры.

    Как объясняется в этом сообщении в блоге, преимущество использования геохэшей заключается в том, что вы можете создать индекс в таблице MySQL для этого поля.

    • 0
  2. Это может быть слишком поздно для вас, но я все равно отвечу тем же ответом, что и на этот связанный вопрос, чтобы будущие посетители могли ссылаться на оба вопроса.

    Я бы не стал хранить эти значения в таблице метаданных поста или, по крайней мере, не только там. Вам нужна таблица со столбцами post_id, lat, lon, чтобы вы могли поместить в нее индекс lat, lon и запрос. Это не должно быть слишком сложно поддерживать в актуальном состоянии с помощью функции сохранения и обновления после публикации.

    Когда вы запрашиваете базу данных, вы определяете ограничивающую рамку вокруг начальной точки, поэтому вы можете сделать эффективный запрос для всех 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();
    
    • 0
  3. Я опаздываю на эту вечеринку, но оглядываясь назад, я понимаю, get_post_meta что проблема действительно здесь, а не в SQL-запросе, который вы используете.

    Недавно мне пришлось выполнить аналогичный поиск географических данных на сайте, который я запускаю, и вместо того, чтобы использовать метатаблицу для хранения широты и долготы (что требует в лучшем случае двух объединений для поиска и, если вы используете get_post_meta, две дополнительные базы данных запросов на местоположение), я создал новую таблицу с пространственно-индексированным геометрическим типом данных POINT.

    Мой запрос был очень похож на ваш, с MySQL, выполняющим большую часть тяжелой работы (я не использовал триггерные функции и упростил все до двумерного пространства, потому что это было достаточно близко для моих целей):

    function nearby_property_listings( $number = 5 ) {
        global $client_location, $wpdb;
    
        //sanitize public inputs
        $lat = (float)$client_location['lat'];  
        $lon = (float)$client_location['lon']; 
    
        $sql = $wpdb->prepare( "SELECT *, ROUND( SQRT( ( ( ( Y(geolocation) - $lat) * 
                                                           ( Y(geolocation) - $lat) ) *
                                                             69.1 * 69.1) +
                                                      ( ( X(geolocation) - $lon ) * 
                                                           ( X(geolocation) - $lon ) * 
                                                             53 * 53 ) ) ) as distance
                                FROM {$wpdb->properties}
                                ORDER BY distance LIMIT %d", $number );
    
        return $wpdb->get_results( $sql );
    }
    

    где $client_location — это значение, возвращаемое общедоступной службой поиска IP-адресов (я использовал geoio.com, но есть несколько подобных).

    Это может показаться громоздким, но при тестировании он постоянно возвращал ближайшие 5 местоположений из таблицы с 80 000 строк менее чем за 0,4 секунды.

    Пока MySQL не развернет предлагаемую функцию DISTANCE, это кажется лучшим способом, который я нашел для реализации поиска местоположения.

    РЕДАКТИРОВАТЬ: добавление структуры таблицы для этой конкретной таблицы. Это набор списков свойств, поэтому он может быть или не быть похожим на любой другой вариант использования.

    CREATE TABLE IF NOT EXISTS `rh_properties` (
      `listingId` int(10) unsigned NOT NULL,
      `listingType` varchar(60) collate utf8_unicode_ci NOT NULL,
      `propertyType` varchar(60) collate utf8_unicode_ci NOT NULL,
      `status` varchar(20) collate utf8_unicode_ci NOT NULL,
      `street` varchar(64) collate utf8_unicode_ci NOT NULL,
      `city` varchar(24) collate utf8_unicode_ci NOT NULL,
      `state` varchar(5) collate utf8_unicode_ci NOT NULL,
      `zip` decimal(5,0) unsigned zerofill NOT NULL,
      `geolocation` point NOT NULL,
      `county` varchar(64) collate utf8_unicode_ci NOT NULL,
      `bedrooms` decimal(3,2) unsigned NOT NULL,
      `bathrooms` decimal(3,2) unsigned NOT NULL,
      `price` mediumint(8) unsigned NOT NULL,
      `image_url` varchar(255) collate utf8_unicode_ci NOT NULL,
      `description` mediumtext collate utf8_unicode_ci NOT NULL,
      `link` varchar(255) collate utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (`listingId`),
      KEY `geolocation` (`geolocation`(25))
    )
    

    Столбец geolocation — единственное, что имеет отношение к нашим целям; он состоит из координат x(lon),y(lat), которые я просто ищу по адресу при импорте новых значений в базу данных.

    • 0
  4. Просто предварительно рассчитайте расстояния между всеми объектами. Я бы сохранил это в таблице базы данных отдельно с возможностью индексации значений.

    • 0

Оставить ответ

You must login to add an answer.