先根据经纬度和距离计算一个矩形,然后筛选经纬度在矩形范围内的数据。
/**
*
* @param Select $select
* @param string $columnName
* @param string $longitude
* @param string $latitude
* @param int $distance
* metre
* @return string
*/
static function ST_MBRWithinRectangle(Select $select, $columnName,
$longitude, $latitude, $distance)
{
list ($min, $max) = self::rectangle($longitude, $latitude, $distance);
$where = "$columnName!=PointFromText('Point(0 0)') and $columnName!='' and";
$where .= " ST_MBRWithin(`$columnName`,ST_GeomFromText('Polygon(($min[0] $min[1],";
$where .= "$min[0] $max[1],$max[0] $max[1],$max[0] $min[1],$min[0] $min[1]))'))";
$select->where($where);
}
/**
* get rectangle longitude and latitude
*
* @param float $longitude
* @param float $latitude
* @param integer $distance
* meter
* @return array
*/
static function rectangle($longitude, $latitude, $distance)
{
$radius = 6371 * 1000;
// latitude boundaries
$maxlat = $latitude + rad2deg($distance / $radius);
$minlat = $latitude - rad2deg($distance / $radius);
// longitude boundaries (longitude gets smaller when latitude increases)
$maxlng = $longitude +
rad2deg($distance / $radius / cos(deg2rad($latitude)));
$minlng = $longitude -
rad2deg($distance / $radius / cos(deg2rad($latitude)));
return array(
array(
'lng' => $minlng,
'lat' => $minlat
),
array(
'lng' => $maxlng,
'lat' => $maxlat
)
);
}