从数据库中查找给定纬度和经度500英里内的所有结果

时间:2018-01-03 作者:Mohsin

我陷入了这个问题中,主要要求是从数据库中查找距离给定纬度和经度500英里以内的所有结果。我已经将纬度和经度以及这些纬度和经度的邮政编码存储到post\\u meta中,现在的想法是,当用户搜索任何邮政编码并从下拉列表中选择英里数时,所有匹配该查询的结果都应该出现。

用户进行如下查询

51310的500英里

查询应返回距离邮政编码51310 500英里以内的所有结果。

以下是我到目前为止所做的。这是实际对数据库进行查询的主要功能。

if (isset($_GET[\'s\']) && !empty($_GET[\'s\'])) {

        $searchterm = isset($q->query_vars[\'s\']) ? sanitize_text_field($q->query_vars[\'s\']) : \'\';

        // we have to remove the "s" parameter from the query, because it will prevent the posts from being found
        $q->query_vars[\'s\'] = \'\';

        if ($searchterm != \'\') {

            $search_radius = $this->geocode($searchterm);

            if (!$search_radius) {
                return false;
            }

            $lat = $search_radius[\'lat\']; // get the lat of the requested address
            $lng = $search_radius[\'lng\']; // get the lng of the requested address
            // we\'ll want everything within, say, 30km distance
            $distance = isset($_GET[\'within\']) && !empty($_GET[\'within\']) ? floatval($_GET[\'within\']) : 50;

            // earth\'s radius in km = ~6371
            $radius = auto_listings_metric() == \'yes\' ? 6371 : 3950;

            // latitude boundaries
            $maxlat = $lat + rad2deg($distance / $radius);
            $minlat = $lat - rad2deg($distance / $radius);

            // longitude boundaries (longitude gets smaller when latitude increases)
            $maxlng = $lng + rad2deg($distance / $radius / cos(deg2rad($lat)));
            $minlng = $lng - rad2deg($distance / $radius / cos(deg2rad($lat)));

            // build the meta query array
            $radius_array = array(
                \'relation\' => \'AND\',
            );

            $radius_array[] = array(
                \'key\' => \'_al_listing_lat\',
                \'value\' => array($minlat, $maxlat),
                \'type\' => \'DECIMAL(10,5)\',
                \'compare\' => \'BETWEEN\',
            );
            $radius_array[] = array(
                \'key\' => \'_al_listing_lng\',
                \'value\' => array($minlng, $maxlng),
                \'type\' => \'DECIMAL(10,5)\',
                \'compare\' => \'BETWEEN\',
            );

            return apply_filters(\'auto_listings_search_radius_args\', $radius_array);
为了找到地理编码,我编写了这个函数。

$address = urlencode(esc_html($address));

    // google map geocode api url
    $url = auto_listings_google_geocode_maps_url($address);

    $arrContextOptions = array(
        "ssl" => array(
            "verify_peer" => false,
            "verify_peer_name" => false,
        ),
    );

    // get the json response
    $resp_json = file_get_contents($url, false, stream_context_create($arrContextOptions));

    // decode the json
    $resp = json_decode($resp_json, true);

    //pp( $resp );
    // response status will be \'OK\', if able to geocode given address 
    if ($resp[\'status\'] == \'OK\') {

        // get the lat and lng
        $lat = $resp[\'results\'][0][\'geometry\'][\'location\'][\'lat\'];
        $lng = $resp[\'results\'][0][\'geometry\'][\'location\'][\'lng\'];

        // verify if data is complete
        if ($lat && $lng) {

            return array(
                \'lat\' => $lat,
                \'lng\' => $lng,
            );
        } else {
            return false;
        }
    } else {
        return false;
    }
如果我没有选择“内”字段,此代码将返回特定邮政编码的准确结果,这很好,但当我选择“内”字段时,它将不显示任何内容。有人能告诉我在这段代码中我做错了什么吗。

1 个回复
最合适的回答,由SO网友:Mohsin 整理而成

问题是,当我将查询发送到数据库时,它正在更改我的数组级别,而不是“s”值不为空,因此它阻止了我的记录被提取,第三个也是最重要的问题是我通过更改函数进行查询的方式解决了我的问题。

以下是此逻辑的更新代码,以防其他人需要实现此类功能。

The main function where all the data is gathering up and making a query

public function pre_get_posts($q) {

    // check if the user is requesting an admin page 
    if (is_admin() || !$q->is_main_query())
        return;

    if (!is_post_type_archive(\'auto-listing\'))
        return;

    if (!is_search())
        return;

    $meta_query = array();

    $year_query[] = $this->year_query();
    $model_query[] = $this->model_query();
    $condition_query[] = $this->condition_query();
    $odometer_query[] = $this->odometer_query();
    $price_query[] = $this->price_meta_query();
    $body_type_query = $this->body_type_query();
    $country_query = $this->country_query();
    $transmission_query = $this->transmission_query();
    $radius_query[] = $this->radius_query($q);

    $query_1 = array_merge($country_query, $year_query, $model_query, $condition_query, $price_query, $odometer_query, $transmission_query);
    // if our radius query fails, fall back to keyword searching
    // will fail with no map API key
    if (empty($radius_query[0]) || !$radius_query[0]) {
        $keyword_query[] = $this->keyword_query($q);
        $query_2 = $keyword_query;
    } else {
        $query_2 = $radius_query;
    }

    // if no keyword
    if (empty($_GET[\'s\'])) {
        $query_1[\'relation\'] = \'AND\';
        $meta_query[] = $query_1;
    }

    // if keyword
    if (!empty($_GET[\'s\'])) {
        $query_2[\'relation\'] = \'OR\';
        $meta_query[] = $query_1;
        $meta_query[] = $query_2;
        $meta_query[\'relation\'] = \'AND\';
    }

    $q->set(\'meta_query\', $meta_query);

    $q->set(\'tax_query\', $body_type_query);

    $q->set(\'post_type\', \'auto-listing\');

}

Radius query function that was actually doing the magic

public function radius_query($q) {

    if (isset($_GET[\'s\']) && !empty($_GET[\'s\'])) {

        $searchterm = isset($q->query_vars[\'s\']) ? sanitize_text_field($q->query_vars[\'s\']) : \'\';

        // we have to remove the "s" parameter from the query, because it will prevent the posts from being found
        $q->query_vars[\'s\'] = \'\';

        if ($searchterm != \'\') {

            $search_radius = $this->geocode($searchterm);

            if (!$search_radius)
                return false;

            $lat = $search_radius[\'lat\']; // get the lat of the requested address
            $lng = $search_radius[\'lng\']; // get the lng of the requested address
            // we\'ll want everything within, say, 30km distance
            $distance = isset($_GET[\'within\']) && !empty($_GET[\'within\']) ? floatval($_GET[\'within\']) : 0.1;

            // earth\'s radius in km = ~6371
            $radius = auto_listings_metric() == \'yes\' ? 6371 : 3950;

            // latitude boundaries
            $maxlat = $lat + rad2deg($distance / $radius);
            $minlat = $lat - rad2deg($distance / $radius);

            // longitude boundaries (longitude gets smaller when latitude increases)
            $maxlng = $lng + rad2deg($distance / $radius / cos(deg2rad($lat)));
            $minlng = $lng - rad2deg($distance / $radius / cos(deg2rad($lat)));

            // build the meta query array
            $radius_array = array(
                \'relation\' => \'AND\',
            );

            $radius_array[] = array(
                \'key\' => \'_al_listing_lat\',
                \'value\' => array($minlat, $maxlat),
                \'type\' => \'DECIMAL(10,5)\',
                \'compare\' => \'BETWEEN\',
            );
            $radius_array[] = array(
                \'key\' => \'_al_listing_lng\',
                \'value\' => array($minlng, $maxlng),
                \'type\' => \'DECIMAL(10,5)\',
                \'compare\' => \'BETWEEN\',
            );

            return apply_filters(\'auto_listings_search_radius_args\', $radius_array);
        }
    }
}

And now the final function that was fetching the lat and long from the zip code that user enters

private function geocode($address) {
    // url encode the address
    $address = urlencode(esc_html($address));

    // google map geocode api url
    $url = auto_listings_google_geocode_maps_url($address);

    $arrContextOptions = array(
        "ssl" => array(
            "verify_peer" => false,
            "verify_peer_name" => false,
        ),
    );

    // get the json response
    $resp_json = file_get_contents($url, false, stream_context_create($arrContextOptions));

    // decode the json
    $resp = json_decode($resp_json, true);

    //pp( $resp );
    // response status will be \'OK\', if able to geocode given address 
    if ($resp[\'status\'] == \'OK\') {

        // get the lat and lng
        $lat = $resp[\'results\'][0][\'geometry\'][\'location\'][\'lat\'];
        $lng = $resp[\'results\'][0][\'geometry\'][\'location\'][\'lng\'];

        // verify if data is complete
        if ($lat && $lng) {

            return array(
                \'lat\' => $lat,
                \'lng\' => $lng,
            );
        } else {
            return false;
        }
    } else {
        return false;
    }
}
为了根据此答案查找post,必须将邮政编码存储到post\\u元表中,以获取lati和longi,代码中给出的用于查找最大和最小边界的公式将获得lat和lng本身。我希望我给出了详细的答案,如果没有请随时写评论。

结束

相关推荐

如何加快wp_Query的速度,花了更多的5秒才能与10万条帖子竞争

我使用了一个非常简单的wp\\u查询,在站点上检索了10篇帖子,超过10万篇。此查询在wp多站点上运行,对同一网络(同一db服务器)中的小站点执行相同的查询需要很少的毫秒(0.032888 | 0.021731 | 0.020796 | 0.02127)主要问题是什么时候尝试在大型网站上运行它(增加10万个帖子)。wp\\U查询转储为:print_r($args); // output Array ( [post_type] => post