Doctrine2 DISTANCE function

gistfile1.aw

<?php

namespace Myako\Geographical\Functions;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;

/**
 * "DISTANCE" "(" LatitudeFrom, LongitudetFrom, LatitudeTo, LongitudeTo ")"
 *
 * @author Konstantin.Myakshin <[email protected]>
 */
class DistanceFunction extends FunctionNode
{
	protected $fromLat;
	protected $fromLng;
	protected $toLat;
	protected $toLng;

	public function parse(\Doctrine\ORM\Query\Parser $parser)
	{
		$parser->match(Lexer::T_IDENTIFIER);
		$parser->match(Lexer::T_OPEN_PARENTHESIS);

		$this->fromLat = $parser->ArithmeticPrimary();
		$parser->match(Lexer::T_COMMA);

		$this->fromLng = $parser->ArithmeticPrimary();
		$parser->match(Lexer::T_COMMA);

		$this->toLat = $parser->ArithmeticPrimary();
		$parser->match(Lexer::T_COMMA);

		$this->toLng = $parser->ArithmeticPrimary();

		$parser->match(Lexer::T_CLOSE_PARENTHESIS);
	}

	public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
	{
		$earthDiameterInKM = 1.609344 * 3956 * 2;

		$sql = '('.$earthDiameterInKM.' * ASIN(SQRT(POWER(' .
			'SIN(('.$this->fromLat->dispatch($sqlWalker).' - ABS('.$this->toLat->dispatch($sqlWalker).')) * PI() / 180 / 2), 2) + ' .
			'COS('.$this->fromLat->dispatch($sqlWalker).' * PI() / 180) * COS(ABS('.$this->toLat->dispatch($sqlWalker).') * PI() / 180) * ' .
			'POWER(SIN(('.$this->fromLng->dispatch($sqlWalker).' - '.$this->toLng->dispatch($sqlWalker).') * PI() / 180 / 2), 2) ' .
			')))';

		return $sql;
	}
}

Thank you very much! Very helpful.

To register the function use:

# app/config/config.yml
doctrine:
    orm:
        # [...]
        dql:
            numeric_functions:
                DISTANCE: Myako\Geographical\Functions\DistanceFunction

Assuming that you have an entity YourBundle:City with a name, latitude, and longitude attributes you could query all cities in the proximity of 100 km around the point 53.66047260, 9.74414190 with:

app/console doctrine:query:dql 'select city.name, distance(city.latitude, city.longitude, 53.66047260, 9.74414190) from YourBundle:City city where distance(city.latitude, city.longitude, 53.66047260, 9.74414190) < 100'

This will trigger the following error in conjunction with doctrine's spatial library:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

i think you have to use ArithmeticExpression() instead of ArithmeticPrimary()

@see https://github.com/craue/CraueGeoBundle/blob/master/Doctrine/Query/Mysql/GeoDistance.php#L38

Important: This function is currently wrong and won't work for any negative latitudes. The ABS-functions need to be removed and then it seems to work correctly. I compared it with the original formula for this calculation and there were no ABS-functions used. I am not sure if there is a good reason why you did use them, but after trying a lot of different geo positions and searching for this error for a few hours now I found out that removing them solves the issue.