Sql: Complicated Select Statement And Returned Radius

This query is driving me insane and I honestly just don't know how to accomplish it. I need to grab all USERS and SHOPS longitude and latitude that are within a certain radius, and

Solution 1:

You can get this information by using self-joins:

SELECT
    table.id,
    bid_user_lat.meta_value,
    bid_user_lng.meta_value
FROM
    table
JOIN 
    table bid_user_lat
    ON table.id = bid_user_lat.id
    AND table.meta_key = "bid_user_lat"
JOIN
    table bid_user_lng
    ON table.id = bid_user_lng.id
    AND table.meta_key = "bid_user_lng"
WHERE
    { distance clause }

...the distance clause is where you ensure that it's within the proper distance. You can use bid_user_lat.meta_value as the bid_user_lat value, and bid_user_lng.meta_value as the bid_user_lng value in your calculation.

If you want to return the User results in the same query as the Shop results, then you can do the same query as the above one (using Shops instead of Users, of course), and connect them with a UNION or UNION ALL.

Side note:

If you have any control over the database, you may want to re-visit the design. The solution above is not ideal by any means... If a User always has a bid_user_lat and a bid_user_lng field, or even if it's just a common attribute of User, then it really should have its own column in the User table.

Correction:

I was JOINing incorrectly. The ON clause should use bid_user_lng.meta_key = "bid_user_lat" instead of wp_usermeta.meta_key = "bid_user_lat". You also need to add a GROUP BY clause so that you don't have duplicate records. Applying these to your query above, you get:

SET@lat='48.453541';
SET@lng='-123.491765';
SET@radius='10000';

SELECT
    wp_usermeta.user_id,
    bid_user_lat.meta_value,
    bid_user_lng.meta_value,

( 3959*acos( cos( radians( @lat ) ) *cos( radians( bid_user_lat.meta_value ) ) *cos( radians( bid_user_lng.meta_value ) - radians( @lng ) ) +sin( radians( @lat ) ) *sin( radians( bid_user_lat.meta_value ) ) ) )  AS distance 

FROM
    wp_usermeta
JOIN 
    wp_usermeta bid_user_lat
    ON wp_usermeta.user_id = bid_user_lat.user_id
    AND bid_user_lat.meta_key = "bid_user_lat"
JOIN
    wp_usermeta bid_user_lng
    ON wp_usermeta.user_id = bid_user_lng.user_id
    AND bid_user_lng.meta_key = "bid_user_lng"

GROUPBY wp_usermeta.user_id

HAVING distance <@radiusORDERBY distance LIMIT 0 , 20;

Here's confirmation that it works:

mysql>CREATETABLE wp_usermeta (user_id INTEGER UNSIGNED, meta_key VARCHAR(255), meta_value VARCHAR(255));
Query OK, 0rows affected (0.25 sec)

mysql>INSERTINTO wp_usermeta (user_id, meta_key, meta_value)
    ->VALUES-> (1, "bid_user_lat", "45.000"), (1, "bid_user_lng", "-150.000"),
    -> (2, "bid_user_lat", "20.000"), (2, "bid_user_lng", "20.000"),
    -> (3, "bid_user_lat", "-300.000"), (3, "bid_user_lng", "70.000");
Query OK, 6rows affected (0.16 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>SELECT->     wp_usermeta.user_id,
    ->     bid_user_lat.meta_value,
    ->     bid_user_lng.meta_value,
    ->-> ( 3959*acos( cos( radians( @lat ) ) *cos( radians( bid_user_lat.meta_value ) ) *cos( radians( bid_user_lng.meta_value ) - radians( @lng ) ) +sin( radians( @lat ) ) *sin( radians( bid_user_lat.meta_value ) ) ) )  AS distance 
    ->->FROM->     wp_usermeta
    ->JOIN->     wp_usermeta bid_user_lat
    ->ON wp_usermeta.user_id = bid_user_lat.user_id
    ->AND bid_user_lat.meta_key = "bid_user_lat"
    ->JOIN->     wp_usermeta bid_user_lng
    ->ON wp_usermeta.user_id = bid_user_lng.user_id
    ->AND bid_user_lng.meta_key = "bid_user_lng"
    ->->GROUPBY wp_usermeta.user_id
    ->->HAVING distance <@radiusORDERBY distance LIMIT 0 , 20;
+---------+------------+------------+--------------------+| user_id | meta_value | meta_value | distance           |+---------+------------+------------+--------------------+|1|45.000|-150.000|1271.3329043047352||3|-300.000|70.000|4905.4310014631055||2|20.000|20.000|7198.549954690863|+---------+------------+------------+--------------------+3rowsinset (0.05 sec)