ST_WITHIN（）不返回任何结果

Question

I'm working on the problem of finding all customers within so many miles of a specific lon/lat and returning a list of those customers in an array. I'm now pretty versed in the Haversine formula and the old way of doing that with boundaries in MySQL. However, updating to version 5.6 of MySQL, I'm now attempting to use the function ST_WITHIN() to produce the same result.

I'm following a tutorial posted here:

http://rutweb.com/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

My code so far looks like so:

$lat = 40.2808380;
$lon = -111.6573890;
$distance = $row['RadialMiles']; // radius of bounding circle in miles

$rlon1 = $lon - $distance/abs(cos(deg2rad($lat))*69);
$rlon2 = $lon + $distance/abs(cos(deg2rad($lat))*69);
$rlat1 = $lat - ($distance/69);
$rlat2 = $lat + ($distance/69);

$query = "SELECT astext(Position), Firstname, Lastname FROM Account
 WHERE systemAccountID = '$systemAccountID'
 AND st_within(Position, envelope(linestring(point(:rlon1, :rlat1), point(:rlon2, :rlat2))))";

    try {
        $stmt = $con->prepare($query);
        $stmt->execute(array('rlon1' => $rlon1,'rlon2' => $rlon2,'rlat1' => $rlat1,'rlat2' => $rlat2));
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        throw $e;
    }

    print_r($result);

Right now the array produces an empty result, when I know it should return 2 results.

FYI, you can just assume my table has 3 columns: Firstname, Lastname, and Position. Position is type point and contains something like: POINT(38.8026085 -116.4193878)

I've been working with this all day and using the spatial functions has been... frustrating for me. Any help would be appreciated.

Answer 1

For all those who want to use this tutorial for mysql's ST spacial functions, just know that the tutorial lists out the points in a (lon, lat) format instead of (lat, lon). This is the reason I had trouble getting a returned value.