SET @lat = 33;
SET @lon = -98;
SELECT count(*) FROM pecker_db.d_zip_lookup
where MBRContains(
LineString
(
Point
(
@lon + 100 / ( 111.1 / COS(RADIANS(@lat))),
@lat + 100 / 111.1
),
Point (
@lon - 100 / ( 111.1 / COS(RADIANS(@lat))),
@lat - 100 / 111.1
)
),
my_point)
使用mbrcontains方法,需要自己my_point blob位置属性
实际上是一个矩形,较慢
SET @lat = 33;
SET @lon = -98;
SELECT (6371 * acos( cos( radians(lat_loc) ) * cos( radians( @lat ) ) * cos( radians( @lon ) - radians(lng_loc) ) + sin( radians(lat_loc) ) * sin( radians( @lat ) ) ) ) as distance
FROM pecker_db.d_zip_lookup
where lat_loc between @lat-2 and @lat+2
and lng_loc between @lon-2 and @lon+2
having distance<100;
速度快,关键的地方在于划定区域来计算
参考http://geography.about.com/library/faq/blqzdistancedegree.htm
No comments:
Post a Comment