I was about to start converting the following into xPDO when I saw this
https://forums.modx.com/thread/32076/subquery post which is using xPDOCriteria()
$sql = "
Select ID, Postcode, Lat, Lon,
acos(sin(:lat)*sin(radians(Lat)) + cos(:lat)*cos(radians(Lat))*cos(radians(Lon)-:lon)) * :R As D
From (
Select ID, Postcode, Lat, Lon
From MyTable
Where Lat Between :minLat And :maxLat
And Lon Between :minLon And :maxLon
) As FirstCut
Where acos(sin(:lat)*sin(radians(Lat)) + cos(:lat)*cos(radians(Lat))*cos(radians(Lon)-:lon)) * :R < :rad
Order by D";
$params = array(
'lat' => deg2rad($lat),
'lon' => deg2rad($lon),
'minLat' => $minLat,
'minLon' => $minLon,
'maxLat' => $maxLat,
'maxLon' => $maxLon,
'rad' => $rad,
'R' => $R,
);
$points = $db->prepare($sql);
$points->execute($params);
Anyone have a suggestion as to which is the best way forward? Translate to xpdo or use xPDOCriteria?
fyi: the sql is from Movable Type's geodesy collection
http://www.movable-type.co.uk/scripts/latlong-db.html