如何在CakePHP 3中使用postGIS

Question

We're running CakePHP v3.x with a Postgres database. I need to select some records whose latitude and longitude are within X distance from another point. PostGIS has a function that does just this but it seems that I have to right raw SQL queries in order to use it.

I'm not asking for help writing the raw query, but I am looking for confirmation as to whether the raw query approach is the correct way to use this extension while making best use of the framework. I searched and haven't found any libraries to extend the CakePHP ORM to include this. Perhaps there's a third option I haven't thought of.

[NOTE: This does not work...]

public function fetch()
{
    $maxMetersAway = 10 * 1000;
    $lat = $this->request->query['lat'];
    $lng = $this->request->query['lng'];

    $stops = $this->Stops->find('all')
        ->where("ST_DWithin( POINT($lng,$lat), POINT(Stops.lng,Stops.lat), $maxMetersAway")
        ->toArray();

    $this->set(['stops'=>$stops, '_serialize' => true]);
}

Answer 1

I got the CakePHP query working with this:

$stops = $this->Stops->find('all')
        ->where(['ST_DWithin( ST_SetSRID(ST_MakePoint(' . $longitude . ', ' . $latitude . '),4326)::geography, ST_SetSRID(ST_MakePoint(Stops.longitude, Stops.latitude),4326)::geography, ' . $maxMetersAway . ')'])
        ->toArray();

Updated: Original didn't actually work with the $maxMetersAway properly.