How to find in the database objects lying in a given polygon on the map?

Question

symfony

There are coordinates of all points of the polygon.
The search is done using MBRContains in MySQL (or are there other solutions?).
How to implement this based on SYNFONY2 and DOCTRINE2?
For now, here's what:

$rsm = new ResultSetMapping();
$coords = '('. implode('),(', $value) .')';
$result = $this->getEntityManager()->createNativeQuery('SET @polygon = GeomFromText("Polygon('. $coords .')");', $rsm);
$this->query->andWhere('MBRContains(@polygon, GeomFromText("Point(offer.latitude, offer.longitude)")) = TRUE');

$value - array of coordinates Resulting
in an error:

Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '@'

Answer 1

After a long torment, he nevertheless did:

<?php

namespace AppBundle\DQL;

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

/**
 * MBRContainsFunction ::= "MBRContains" "(" StringPrimary ", " StringExpression ", " StringExpression ")"
 */
class MBRContainsFunction extends FunctionNode
{
    public $polygonExpression = null;
    public $pointCordOne = null;
    public $pointCordTwo = null;

    public function getSql(SqlWalker $sqlWalker)
    {
        $returnString = 'MBRContains(GeomFromText(' . $this->polygonExpression->dispatch($sqlWalker) . '), '.
            'Point(' . $this->pointCordOne->dispatch($sqlWalker) .', '. $this->pointCordTwo->dispatch($sqlWalker) .'))';

        return $returnString;
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->polygonExpression = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->pointCordOne = $parser->StringExpression();
        $parser->match(Lexer::T_COMMA);
        $this->pointCordTwo = $parser->StringExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Usage:

$coords = '123,123 200,200 123,200 123,123';
$this->createQueryBuilder('offer')
       ->andWhere('MBRContains(:coords, offer.latitude, offer.longitude) = TRUE')
       ->setParameter('coords', 'Polygon(('. $coords .'))');

Answer 2

Ivan, and yet it would be interesting to see.
Now I'm working on a small project, I really need a beautiful algorithm