Optimiser une recherche d'emplacement de magasin basée sur la proximité sur un hôte Web partagé?

11

J'ai un projet pour lequel j'ai besoin de créer un localisateur de magasins pour un client.

J'utilise un type de message personnalisé " restaurant-location " et j'ai écrit le code pour géocoder les adresses stockées dans postmeta à l'aide de API Google Geocoding (voici le lien géocode la Maison Blanche aux États-Unis en JSON et j'ai enregistré la latitude et la longitude dans des champs personnalisés.

J'ai écrit une fonction get_posts_by_geo_distance() qui renvoie une liste d'articles dans l'ordre de ceux qui sont géographiquement les plus proches en utilisant la formule que j'ai trouvée dans le diaporama de cet article . Vous pouvez appeler ma fonction comme suit (je commence par une "source" fixe 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;

Voici la fonction get_posts_by_geo_distance() elle-même:

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);
}

Ce qui me préoccupe, c'est que le code SQL est aussi optimisé que possible. MySQL ne peut classer par aucun index disponible, car la source geo est modifiable et il n’existe pas de jeu fini de géos sources à mettre en cache. Actuellement, je suis perplexe sur les moyens de l'optimiser.

Compte tenu de ce que j'ai déjà fait, la question est la suivante: Comment allez-vous optimiser l'optimisation de ce cas d'utilisation?

Il n’est pas important que je garde ce que j’ai fait si une meilleure solution me permettait de le rejeter. Je suis prêt à envisager presque toutes les solutions , à l'exception de celles qui nécessitent l'installation d'un serveur Sphinx ou de tout autre élément nécessitant une configuration MySQL personnalisée. Fondamentalement, la solution doit pouvoir fonctionner sur n'importe quelle installation WordPress simple et intuitive. (Cela dit, ce serait formidable si quelqu'un souhaite énumérer d'autres solutions pour ceux qui pourraient être en mesure de devenir plus avancés et pour la postérité.)

Ressources trouvées

Pour votre information, j'ai fait quelques recherches à ce sujet, alors plutôt que de les refaire ou plutôt que de poster l'un de ces liens en guise de réponse, je vais les inclure et les inclure.

À propos de la recherche dans le Sphinx

posée MikeSchinkel 18.08.2010 - 01:04

4 réponses

6

De quelle précision avez-vous besoin? si c'est un état / national large recherche peut-être que vous pourriez faire un lat-lon pour rechercher zip et avoir la distance précalculée de la zone zip à la zone zip du restaurant. Si vous avez besoin de distances précises, cela ne sera pas une bonne option.

Vous devriez envisager une solution Geohash , dans l'article de Wikipedia, un lien est disponible. dans une bibliothèque PHP pour encoder le décodage lat long de geohashs.

un bon article expliquant pourquoi et comment ils l'utilisent. dans Google App Engine (code Python mais facile à suivre.) En raison de la nécessité d’utiliser geohash dans GAE, vous pouvez trouver quelques bonnes bibliothèques et exemples Python.

Comme le cet article de blog , explique l'avantage de l'utilisation de geohashes que vous pouvez créer un index sur la table MySQL sur ce champ.

    
réponse donnée Chedar 20.08.2010 - 17:45
9

C'est peut-être trop tard pour vous, mais je vais quand même répondre, avec , afin que les futurs visiteurs puissent se reporter aux deux questions.

Je ne voudrais pas stocker ces valeurs dans la table de métadonnées post, ou du moins pas uniquement . Vous voulez une table avec post_id , lat , lon colonnes, afin de pouvoir placer un index de lat, lon et interroger celui-ci. Cela ne devrait pas être trop difficile de rester à jour avec un accrochage après la sauvegarde et la mise à jour.

Lorsque vous interrogez la base de données, vous définissez un cadre de sélection autour du point de départ. Vous pouvez ainsi interroger efficacement lat, lon les paires situées entre les limites nord-sud et est-ouest de la boîte.

Après avoir obtenu ce résultat réduit, vous pouvez effectuer un calcul de distance plus avancé (directions de conduite réelles ou circulaires) pour filtrer les emplacements situés dans les coins du cadre de sélection et par conséquent plus éloignés que vous le souhaitez.

Vous trouverez ici un exemple de code simple qui fonctionne dans la zone d'administration. Vous devez créer vous-même la table de base de données supplémentaire. Le code est classé du plus au moins intéressant.

<?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();
    
1

Je suis en retard pour la soirée sur ce sujet, mais en y repensant, get_post_meta est vraiment le problème ici, plutôt que la requête SQL que vous utilisez.

J'ai récemment eu à faire une recherche géo similaire sur un site que je suis en train d'utiliser, et plutôt que d'utiliser la table meta pour stocker lat et lon (ce qui nécessite au mieux deux jointures pour rechercher et, si vous utilisez get_post_meta deux requêtes de base de données supplémentaires par emplacement), j’ai créé une nouvelle table avec un type de données POINT de géométrie indexée spatialement.

Ma requête ressemblait beaucoup à la vôtre, avec MySQL qui faisait beaucoup de travail lourd (j'ai laissé de côté les fonctions trigonométriques et tout simplifié dans un espace à deux dimensions, parce que c'était assez proche pour mes besoins):

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 );
}

où $ client_location est une valeur renvoyée par un service public de recherche IP géo (j’ai utilisé geoio.com, mais il en existe un certain nombre.)

Cela peut sembler difficile à manier, mais en le testant, il a toujours renvoyé les 5 emplacements les plus proches sur un tableau de 80 000 lignes en moins de 0,4 seconde.

Tant que MySQL n’a pas déployé la fonction DISTANCE proposée, cela semble être le meilleur moyen que j’ai trouvé d’implémenter les recherches d’emplacement.

EDIT: l'ajout de la structure de la table pour cette table particulière. C’est un ensemble d’énumérations de propriétés, il peut donc être semblable ou non à tout autre cas d’utilisation.

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))
)

La colonne geolocation est la seule chose pertinente pour les besoins ici; il se compose de coordonnées x (lon), y (lat) que je viens de rechercher à partir de l'adresse lors de l'importation de nouvelles valeurs dans la base de données.

    
réponse donnée goldenapples 25.02.2011 - 22:49
0

Pré-calculez simplement les distances entre toutes les entités. Je stockerais cela dans une table de base de données, avec la possibilité d’indexer des valeurs.

    
réponse donnée hakre 18.08.2010 - 08:40

Lire d'autres questions sur les étiquettes