We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 36804
    • 19 Posts
    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