Doctrine order by driving distance

Question

I have some places with coords in my database that I retrieve by using GEO function to filter the nearest.

But it is not precise enough because it's not driving distances.

I already use HERE Maps API to get Routing informations but it's so heavy to load the entire list and call the API after that.

Is there any way to sort or filter the places with Doctrine and HERE API combined or any other way ?

Thanks

Answer 1

I had this exact problem on a similar project a year or so ago. The only difference is that I used Google's Distance Matrix API to determine real driving mileage.

The short answer is no, you can't do this at query level, because the database wouldn't have knowledge of routing information. (Nor could you feed it such.) Even if you did build some preemptive routing information you'd still need to query every entity to find its location to do that, plus you would also be putting enormous strain on the maps API likely causing you to hit usage quotas (and be charged for it) very quickly!

The closest you can realistically get, if the entity you're querying stores coordinates, is to first filter/sort your data by direct distance to a more workable set (say 50). Then in your controller use your maps API to sort the resulting array by driving distance. This should reduce how much you need to query the maps API too.

You can query with direct distance with the following example:

public function findClostestTo(float $latitude, float $longitude, float $milesLimit = null, int $resultsLimit = 1): ?array
{
    $qb = $this->createQueryBuilder("entity");

    // Find and sort records by their direct distance
    $qb
        ->addSelect("DEGREES(ACOS((SIN(RADIANS(:latitude)) * SIN(RADIANS(entity.latitude))) + (COS(RADIANS(:latitude)) * COS(RADIANS(entity.latitude)) * COS(RADIANS(:longitude - entity.longitude))))) * :radius AS distanceMiles")
        // ->addSelect("(distanceMiles * 1.609344) AS distanceKilometres")
        // ->addSelect("(distanceMiles * 0.868976) AS distanceNauticalMiles")
        ->setParameter("latitude", $latitude)
        ->setParameter("longitude", $longitude)
        ->setParameter("radius", (60 * 1.1515))
        ->addOrderBy("distanceMiles", "ASC")
    ;

    // Optional: Clamp results to a direct distance
    if (is_numeric($milesLimit) && $milesLimit > 0.0) {
        $qb
            ->andWhere("distanceMiles < :milesLimit")
            ->setParameter("milesLimit", $milesLimit)
        ;
    }

    // Limit quantity of direct distance results, important to reduce route distance API call count later
    $qb->setMaxResults(max(min($resultsLimit, 50), 1));

    return $qb->getQuery()->getResult();
}

Input $latitude and $longitude for the origin/destination point you want to calculate distance from, e.g. the user's location doing the search

Bare in mind that each result in the array from this repository function will not be a direct instance of each entity. It will be split into 2 keys:

• 0: The target entity instance
• distanceMiles: The extra pseudo field used to limit and sort by distance

In your controller you would then need to iterate the results array to query your maps API and get the exact driving distance. Personally I'd have that add another key drivingMiles sibling to 0 and distanceMiles, of which you can then use in a usort() to sort the whole result set by to build your final result set.

This should work quite well for the majority of cases, however there will always be some edge situations whereby certain results are further than the initial distance limit, due to strange bends/directions in roads looping back towards the source and such. You can still filter these out of your final array though after usort()ing it, then just increase the query limit based on direct distance gradually if you find your final result sets are becoming too small.