0

MySQL, I need to get the nearest location for each organization. Between organizations and locations is a one-to-many relation. One organization has many locations. The GPS coordinates are stored in the locations table.

I am using this query to get minimum distance for every location. But I still need the closest location name and other fields. I also have to but a radius condition.

    SELECT
        `organizations`.`id`,
        `organizations`.`id` AS `organization_id`,
        `organizations`.`name` AS `organization_name`,
        COUNT(DISTINCT `locations`.`id`) AS locations_count,
        `locations`.`name` AS location_name,
        CONCAT(`locations`.`street`, ', ',`locations`.`city`, ', ', `locations`.`state`) as location_address,
        MIN(ROUND(ACOS(SIN(RADIANS(45.537463)) * SIN(RADIANS(locations.latitude)) + 
                COS(RADIANS(45.537463)) * COS(RADIANS(locations.latitude)) * 
                COS(RADIANS(locations.longitude) - RADIANS(- 122.616951))) * 3959,
            2)) AS location_distance
    FROM
        `organizations`
            INNER JOIN
        `locations` ON `locations`.`organization_id` = `organizations`.`id`

    GROUP BY `organizations`.`id`
    HAVING `location_distance` < '150'
    ORDER BY `organizations`.`name`

For now I have to make another query to get the closest location for each organization by organizations.id.

Do you think is possible to it in a single query without subqueries in the SELECT statement. It will really slow the query. Thanks!

2
  • That's effectively a "groupwise max" problem; I blog about it here. – Rick James Aug 29 '15 at 22:49
  • Great article. There are some very good methods there. Unfortunately I am constrained by the server side programming language so, I can not use variables or multiple queries. I ended up using two queries. One to get the organizations and one to get all the locations for each organization sorted by distance. After that I keep the closest location and remove the others from the result. Thanks Rick! – Robert Gabriel Sep 2 '15 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.