Question 166795 in Wordpress Question posted in
The official Wordpress documentation can be found here.

In PhP, WordPress & MySQL, how to find nearest locations on a custom table?

I have the following function that I tweaked because I needed to use a table with less rows and fields. The tables in wordpress that the function was using are wp_posts and wp_postmeta (see below) but it has too many rows and performance is bad. I create a curated table called wp_lat_long with only the information I needed. I am trying to optimize to calculate which Posts_ID are within a radius based on what users ask (radius, location). The issue is that I don’t get any results when using this custom table. The query run on the database works. wp_lat_long table has the following columns: ID, latitude, longitude.

I am new to PHP.

add_filter('houzez_radius_filter', 'houzez_radius_filter_callback', 10, 6);
if( !function_exists('houzez_radius_filter_callback') ) {
    function houzez_radius_filter_callback( $query_args, $search_lat, $search_long, $search_radius, $use_radius, $location ) {

    global $wpdb;

    if ( ! ( $use_radius && $search_lat && $search_long && $search_radius ) || ! $location ) {
        return $query_args;
    }

    $radius_unit = houzez_option('radius_unit');
    if( $radius_unit == 'km' ) {
        $earth_radius = 6371;
    } elseif ( $radius_unit == 'mi' ) {
        $earth_radius = 3959;
    } else {
        $earth_radius = 6371;
    }

    $sql = $wpdb->prepare( "SELECT $wpdb->lat_long.ID,
            ( %s * acos(
                cos( radians(%s) ) *
                cos( radians( $wpdb->lat_long.latitude ) ) *
                cos( radians( $wpdb->lat_long.longitude ) - radians(%s) ) +
                sin( radians(%s) ) *
                sin( radians( $wpdb->lat_long.latitude ) )
            ) )
            AS distance, $wpdb->lat_long.latitude AS latitude, $wpdb->lat_long.longitude AS longitude
            FROM $wpdb->lat_long
            HAVING distance < %s
            ORDER BY distance ASC
            Limit 200",
        $earth_radius,
        $search_lat,
        $search_long,
        $search_lat,
        $search_radius
    );
    $post_ids = $wpdb->get_results( $sql, OBJECT_K );

    if ( empty( $post_ids ) || ! $post_ids ) {
        $post_ids = array(0);
    }

    $query_args[ 'post__in' ] = array_keys( (array) $post_ids );
    return $query_args;
}

}

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Ok, so I have solve it. I needed to pass {$wpdb->base_prefix} instead of $wpdb->


  2. 0

    You forgot to execute your prepared statement

    $sql = $wpdb->prepare( "SELECT $wpdb->lat_long.ID,
        ( %s * acos(
            cos( radians(%s) ) *
            cos( radians( $wpdb->lat_long.latitude ) ) *
            cos( radians( $wpdb->lat_long.longitude ) - radians(%s) ) +
            sin( radians(%s) ) *
            sin( radians( $wpdb->lat_long.latitude ) )
        ) )
        AS distance, $wpdb->lat_long.latitude AS latitude, $wpdb->lat_long.longitude AS longitude
        FROM $wpdb->lat_long
        HAVING distance < %s
        ORDER BY distance ASC
        Limit 200",
    $earth_radius,
    $search_lat,
    $search_long,
    $search_lat,
    $search_radius
);
$wpdb->execute();   <------ this here is missing
$post_ids = $wpdb->get_results( $sql, OBJECT_K );

    but you can use ST_DISTANCE_SPHERE like here https://stackoverflow.com/a/61531608/5193536

    SELECT 
    id,
        ST_DISTANCE_SPHERE(POINT(- 82.337036, 29.645095), POINT(`longitude`, `latitude`)) / 1000 AS distance
FROM
    users u
WHERE
    id <> 1
HAVING distance < 5000
ORDER BY distance DESC
LIMIT 20
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search