查找用户附近的事件位置

Question

I built a mobile app that needs to locate events going on around them based on their current latitude and longitude with a 10 mile radius. The user clicks on an event type and the API needs to search for all events within 10 miles of them. The way my database is set up each event belongs to a venue and that is where the latitude and longitude is stored. Here is the SQL that I have so far:

$sql = "SELECT events.*, (3959 * acos(cos(radians(78.3232)) * cos(radians($latitude)) * cos(radians($longitude) - radians(65.3234)) + sin(radians(78.3232)) * sin(radians($latitude)))) AS distance
    FROM events 
        INNER JOIN venues ON events.venue_id = venues.id 
        INNER JOIN promoters ON events.promoter_id = promoters.id 
    WHERE type = $type AND end > NOW()
    ORDER BY distance";

The events table stores information about an event (name, description, start, end, etc.) while the venues table stores information about a venue (name, address, latitude, longitude, etc.). How would I join these tables to make sure that I retrieve the events that I want based on the user's location?

Answer 1

I edited the query to include table prefixes which will allow the math to pull the lat and long columns from the venues table. I checked this against my database and it works just fine as well.

Bou're not including a distance parameter in your criteria, using HAVING lets you include the distance column in your query criteria. Your query would presumably return all events of $type that haven't happened yet. I checked this against a user table I have with lat and long and it works just fine.

It looks like you'd need something more like this (assuming you actually need all the columns from all three tables):

SELECT *, acos(cos(radians($userlat)) * cos(radians(`v`.`lat`)) * cos(radians(`v`.`long`) - radians($userlong)) + sin(radians($userlat)) * sin(radians(`v`.`lat`))) * 3959 AS `distance` 
    FROM `events` AS `e` 
        INNER JOIN `venues` AS `v` ON `e`.`venue_id` = `v`.`id`  
        INNER JOIN `promoters` AS `p` ON `e`.`promoter_id` = `p`.`id`  
    WHERE `e`.`type` = $type
    AND `e`.`end` > NOW() 
    HAVING `distance` < 25 
    ORDER BY `distance` ASC 
    LIMIT 20;