# Developers Planet

Vince February 2016

### Result grouped by resolution with minimum distance from coordinate

I have a query to find the nearest latitude/longitude from a given coordinate :

`public function findClosestByLatitudeLongitude(\$latitude, \$longitude, \$distanceUnit = 111.045, \$radius = 150)`

``````\$stmt = \$this->db->prepare('SELECT
f.fcst_latitude,
f.fcst_longitude,
f.fcst_resolution,
:distance_unit * DEGREES(
ACOS(
COS(
) * COS(
) * COS(
) + SIN(
) * SIN(
)
)
) AS distance
FROM t_fcst_data_coord AS f
WHERE
f.fcst_latitude BETWEEN :latitude  - (:radius / :distance_unit)
AND :latitude + (:radius / :distance_unit)
AND f.fcst_longitude BETWEEN :longitude - (
:distance_unit * COS(
)
)
)
AND :longitude + (
:distance_unit * COS(
)
)
)
ORDER BY distance ASC
LIMIT 100
');
``````

The result is an array ordered by distance, and containing the resolution of the forecast, like this :

``````(
[0] => Array
(
[fcst_latitude] => 46.295396
[fcst_longitude] => 6.854558
[fcst_resolution] => 9.0
[distance] => 1.2113482186062683
)

[1] => Array
(
[fcst_latitude] => 46.313622
[fcst_longitude] => 6.843681
[fcst_resolution] => 3.0
[distance] => 1.4198633375521186
)

[2] => Array
(
[fcst_latitu        ``````
``` ```
``` ```
``` Answers Kickstart February 2016 Couple of ways to do it. Normal way is to use a sub query to get the MIN distance for each resolution, and then join that back against your query to get the full rows for each resolution / distance. Another trick is to GROUP BY the resolution, and then use GROUP_CONCAT for each of the other fields, ordering by the distance. Then use SUBSTRING_INDEX to chop off the first occurance of each field from the result of the GROUP_CONCATs. Possible issues if anything contains NULL, or if any field returns a value containing a comma. \$stmt = \$this->db->prepare(' SELECT SUBSTRING_INDEX(GROUP_CONCAT(sub0.fcst_latitude ORDER BY sub0.distance ), ',', 1) AS fcst_latitude, SUBSTRING_INDEX(GROUP_CONCAT(sub0.fcst_longitude ORDER BY sub0.distance ), ',', 1) AS fcst_longitude, sub0.fcst_resolution, SUBSTRING_INDEX(GROUP_CONCAT(sub0.distance ORDER BY sub0.distance ), ',', 1) AS distance, FROM (SELECT f.fcst_latitude, f.fcst_longitude, f.fcst_resolution, :distance_unit * DEGREES( ACOS( COS( RADIANS(:latitude) ) * COS( RADIANS(f.fcst_latitude) ) * COS( RADIANS(:longitude) - RADIANS(f.fcst_longitude) ) + SIN( RADIANS(:latitude) ) * SIN( RADIANS(f.fcst_latitude) ) ) ) AS distance FROM t_fcst_data_coord AS f WHERE f.fcst_latitude BETWEEN :latitude - (:radius / :distance_unit) AND :latitude + (:radius / :distance_unit) AND f.fcst_longitude BETWEEN :longitude - ( :radius / ( :distance_unit * COS( RADIANS(:latitude) ) ) ) AND :longitude + ( :radius / ( :distance_unit * COS( RADIANS(:latitude) ) ) ```
``` Post Status Asked in February 2016Viewed 1,202 timesVoted 9Answered 1 times Search Leave an answer ```
``` ```
``` ```
``` Quote of the day: live life .btn-primary{ background-color: #f44336 !important; border-color: #f44336 !important; } Devs Planet ® 2014-2016 www.devsplanet.com Devs Planet © all rights reserved Quick Actions Search // Used to toggle the menu on small screens when clicking on the menu button function myFunction() { var x = document.getElementById("navDemo"); if (x.className.indexOf("w3-show") == -1) { x.className += " w3-show"; } else { x.className = x.className.replace(" w3-show", ""); } } ```