使用以下URL中的设置,我构建了下面的查询。(请注意,我正在使用codeIgnitor查询数据库)
http://howto-use-mysql-spatial-ext.blogspot.com/2007/11/using-circular-area-selection.html
function getRadius($point="POINT(-29.8368 30.9096)", $radius=2)
{
$km = 0.009;
$center = "GeomFromText('$point')";
$radius = $radius*$km;
$bbox = "CONCAT('POLYGON((',
X($center) - $radius, ' ', Y($center) - $radius, ',',
X($center) + $radius, ' ', Y($center) - $radius, ',',
X($center) + $radius, ' ', Y($center) + $radius, ',',
X($center) - $radius, ' ', Y($center) + $radius, ',',
X($center) - $radius, ' ', Y($center) - $radius, '
))')";
$query = $this->db->query("
SELECT id, AsText(latLng) AS latLng, (SQRT(POW( ABS( X(latLng) - X({$center})), 2) + POW( ABS(Y(latLng) - Y({$center})), 2 )))/0.009 AS distance
FROM crime_listing
WHERE Intersects( latLng, GeomFromText($bbox) )
AND SQRT(POW( ABS( X(latLng) - X({$center})), 2) + POW( ABS(Y(latLng) - Y({$center})), 2 )) < $radius
ORDER BY distance
");
if($query->num_rows()>0){
return($query->result());
}else{
return false;
}
}