How to Get Nearby Locations from MySQL Database

If you have a MySQL table with locations (latitude and longitude columns) you can get a list of locations near a specific point using the following query:

SELECT *, ((ACOS(SIN(<latitude> * PI() / 180) * SIN(latitude * PI() / 180) + COS(<latitude> * PI() / 180) * COS(latitude * PI() / 180) * COS((<longitude> - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) as distance FROM locations HAVING distance <= <distance> ORDER BY distance ASC

In this query we have noted the latitude of the reference point with <latitude>, its longitude with <longitude> and the maximum distance to search for with <distance> (in kilometers).

To make this calculate the distance in miles, just remove the * 1.609344 from the end of the calculation:

SELECT *, ((ACOS(SIN(<latitude> * PI() / 180) * SIN(latitude * PI() / 180) + COS(<latitude> * PI() / 180) * COS(latitude * PI() / 180) * COS((<longitude> - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) as distance FROM locations HAVING distance <= <distance> ORDER BY distance ASC

You can test the results using Google Maps. To measure distances in Google Maps you need to right-click and choose “Measure distance” from the popup menu.

How to Get Nearby Locations in Symfony with Doctrine

If you’re like me and use Symfony a lot, here’s how you can add a repository method for your entity to return objects ordered by distance from a specific location:

<?php
namespace AppBundle\Entity;
class LocationRepository extends \Doctrine\ORM\EntityRepository
{
    public function findClosest($lat, $lng) {
        $qb = $this->createQueryBuilder('l');
        $qb->addSelect('((ACOS(SIN(:lat * PI() / 180) * SIN(l.latitude * PI() / 180) + COS(:lat * PI() / 180) * COS(l.latitude * PI() / 180) * COS((:lng - l.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) as HIDDEN distance');
        $qb->orderBy('distance');
        $qb->setParameter('lat', $lat);
        $qb->setParameter('lng', $lng);
        
        return $qb->getQuery()->getResult();
    }
}

To make this work we need to add the ACOS, SIN, COS and PI functions to Doctrine’s DQL as it does not have them by default.

First create 4 new classes in a new AppBundle/DQL folder, one for each function we need to define.

AppBundle/DQL/Acos.php

<?php
namespace ApiBundle\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;
class Acos extends FunctionNode
{
    public $arithmeticExpression;
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'ACOS(' . $sqlWalker->walkSimpleArithmeticExpression(
                $this->arithmeticExpression
        ) . ')';
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->arithmeticExpression = $parser->SimpleArithmeticExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

AppBundle/DQL/Sin.php

<?php
namespace ApiBundle\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;
class Sin extends FunctionNode
{
    public $arithmeticExpression;
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'SIN(' . $sqlWalker->walkSimpleArithmeticExpression(
                $this->arithmeticExpression
        ) . ')';
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->arithmeticExpression = $parser->SimpleArithmeticExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

AppBundle/DQL/Cos.php

<?php
namespace ApiBundle\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;
class Cos extends FunctionNode
{
    public $arithmeticExpression;
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'COS(' . $sqlWalker->walkSimpleArithmeticExpression(
                $this->arithmeticExpression
        ) . ')';
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->arithmeticExpression = $parser->SimpleArithmeticExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

AppBundle/DQL/Pi.php

<?php
namespace ApiBundle\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;
class Pi extends FunctionNode
{
    public $arithmeticExpression;
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'PI()';
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

The next and the last step is to add the above definitions to your app’s config.yml file:

doctrine:
    dbal:
        # ...
    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        dql:
            numeric_functions:
                acos: ApiBundle\DQL\Acos
                sin: ApiBundle\DQL\Sin
                cos: ApiBundle\DQL\Cos
                pi: ApiBundle\DQL\Pi

That’s it. Now it will all work as expected. If you need to add more specific functions to DQL, just look at the above classes and make your own.