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(
                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)
              )
            )
          )
        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 2016
Viewed 1,202 times
Voted 9
Answered 1 times

Search




Leave an answer