CODEWITHSUNDEEP
mysqlsymfonygeospatialdql
Amine Jallouli
Amine Jallouli

Reputation: 3959

DQL query for getting geo spatial points st_within a rectangle

I would like to write DQL query to select all points being within a rectangle in the map. The rectangle is defined by the top left point and bottom right point. See this link for more details.

To do so, I wrote this DQL query:

$qb = $this->em->createQueryBuilder()
    ->select("m")
    ->from($this->getEntityClassName(), "m")
    ->where("ST_Within(m.geom, envelope(linestring(:topleft, :bottomright)))")
    ->setParameter(":topleft", $topleft)
    ->setParameter(":bottomright", $bottomright)
    ->orderBy("m.date", "DESC");

The error I am getting is:

[Syntax Error] line 0, col 110: Error: Expected =, <, <=, <>, >, >=, !=, got 'ORDER'

For your information, entity I am querying has a geom attribute defined as follows:

/**
 * @var point $geom
 * @ORM\Column(type="point", nullable=true)
 */
protected $geom;

For your information, the SQL query is working perfectly. It is as follows:

SELECT *FROM MotorsAds WHERE
st_within(point(lng, lat),         
envelope(linestring(point(10.090792984008772,36.83717099338201 ), 
point(10.310519546508772,36.749467295867646 ))))

The library I am using for DQL numeric functions (such us st_within) is creof/doctrine2-spatial.

Ready to take any proposal to fix that.

Thanks,

Update of the question

I tried the solution you proposed as follows:

    $qb = $this->em->createQueryBuilder()
        ->select("m")
        ->from($this->getEntityClassName(), "m")
        ->where(
            $this->em->createQueryBuilder()->expr()->eq(
                    "ST_Within(m.geom, envelope(linestring(:topleft, :bottomright)))",
                    $this->em->createQueryBuilder()->expr()->literal(true)
                )
        )
        ->setParameter(":topleft", $topleft)
        ->setParameter(":bottomright", $bottomright)
        ->orderBy("m.date", "DESC");

So, the error I got is:

An exception occurred while executing 'SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_0 FROM (SELECT m0_.id AS id_0, m0_.price AS price_1, m0_.year AS year_2, m0_.km AS km_3, m0_.slug AS slug_4, m0_.title AS title_5, m0_.description AS description_6, m0_.address AS address_7, m0_.isPublished AS isPublished_8, m0_.delegation AS delegation_9, m0_.lat AS lat_10, m0_.lng AS lng_11, m0_.date AS date_12, m0_.count AS count_13, AsBinary(m0_.geom) AS geom_14 FROM MotorsAds m0_ WHERE ST_Within(m0_.geom, Envelope(LineString(?, ?))) = 1 ORDER BY m0_.date DESC) dctrn_result) dctrn_table' with params [{}, {}]:

SQLSTATE[22007]: Invalid datetime format: 1367 Illegal non geometric ''10.090792984009 36.837170993382'' value found during parsing

Upvotes: 2

Views: 392

Answers (2)

Amine Jallouli
Amine Jallouli

Reputation: 3959

The solution below will prevent you the problem of conversion a PHP point to DQL query point. 

    $qb = $this->em->createQueryBuilder()
        ->select("m")
        ->from($this->getEntityClassName(), "m")
        ->where(
            $this->em->createQueryBuilder()->expr()->eq(
                    sprintf("ST_Within(m.geom, envelope(linestring(point(:topleftX,:topleftY), point(:bottomrightX,:bottomrightY ))))"),
                    $this->em->createQueryBuilder()->expr()->literal(true)
                )
        )
        ->setParameter(":topleftX", $topleftX)
        ->setParameter(":topleftY", $topleftY)
        ->setParameter(":bottomrightX", $bottomrightX)
        ->setParameter(":bottomrightY", $bottomrightY)
        ->orderBy("m.date", "DESC");

Upvotes: 1

Miro
Miro

Reputation: 1899

Try this:

$qb = $this->em->createQueryBuilder()
    ->select("m")
    ->from($this->getEntityClassName(), "m")
    ->where(
        $queryBuilder->expr()->eq(
                "ST_Within(m.geom, envelope(linestring(:topleft, :bottomright)))",
                $queryBuilder->expr()->literal(true)
            )
    )
    ->setParameter(":topleft", $topleft)
    ->setParameter(":bottomright", $bottomright)
    ->orderBy("m.date", "DESC");

I took it from documentation: https://github.com/creof/doctrine2-spatial/blob/master/doc/index.md

Upvotes: 2

Related Questions