sql查询,根据与用户的接近程度提供广告服务

jyztefdp  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(397)

好的,所以我在我的网站上实现了一个广告系统。我现在根据注册时输入的城市来存储成员的纬度/经度坐标。每个广告也将提供地理坐标的选择,并指定一个以英里为单位的半径(虽然我也希望有一个以公里为单位指定的选项)。

Sample tables
--------------users
user_id = 7
name = Charles
lat = xxxxxx.xx
lon = xxxxxx.xx

--------------ads
ad_id = 1121
advertiser_id = 42
ad_type = 728x90
proximity = 50
lat = xxxxxx.xx
lon = xxxxxx.xx

我有一段代码可以搜索我的用户数据库并返回在指定距离内的用户。我想我可以编辑它,这样它就可以满足我的需要。但是,我不想显示指定距离内的所有用户,而是要显示广告本身指定距离内的广告,因为一个广告客户可以指定与另一个不同的接近度,等等。
以下是我使用的原始用户接近查询:

SELECT users.user_id
     , ( 3959 * acos( cos( radians('".$lat."') ) * 
                      cos( radians( users.lat ) ) * 
                      cos( radians( users.lng ) - 
                           radians('".$lng."') ) + 
                      sin( radians('".$lat."') ) * 
                      sin( radians( users.lat ) ) 
                    ) ) AS distance 
  FROM users 
 WHERE account_type = '1' 
   AND `active` = '1' 
HAVING distance <= '".$dist."'"

所以我想,如果我首先创建一个查询来获取符合区域和邻近性要求的广告,然后选择他们的纬度/经度并选择当前用户的地理坐标,然后。。。。。。我好像不知道该怎么办。然后,我将执行几个计算的基础上,不同的接近设置每个广告匹配区域。如果一个广告想显示100英里以内的人,那么计算结果将不同于那些只想让20英里以内的人看到他的广告的人。可能有更简单的方法。这是一个查询生成器应用程序可以帮助创建的东西吗?
也许是这样的?

SELECT ads.ad_id
     , ( 3959 * acos( cos( radians('".$user_lat."') ) * 
                      cos( radians( ads.lat ) ) * 
                      cos( radians( ads.lng ) - 
                           radians('".$user_lng."') ) + 
                      sin( radians('".$user_lat."') ) * 
                      sin( radians( ads.lat ) ) 
                    )) AS distance 
  FROM ads 
 WHERE ad_type = '728x90' 
   AND `active` = '1' 
HAVING distance <= ads.proximity"
2hh7jdfx

2hh7jdfx1#

我提出的最终查询解决方案是:

SELECT ads.ad_id
     , proximity AS P, ( 3959 * acos( cos( radians('".$usr_lat."') ) * 
                                      cos( radians( ads.lat ) ) * 
                                      cos( radians( ads.lon ) - 
                                           radians('".$usr_lon."') ) + 
                                      sin( radians('".$usr_lat."') ) * 
                                      sin( radians( ads.lat ) ) 
                                    ) ) AS distance 
FROM ads 
WHERE ads.type = '728x90' 
AND ads.active = '1' 
HAVING distance <= P 
OR P='0' 
ORDER BY distance ASC

我添加了or p='0'来提供没有指定邻近性的广告。

ltskdhd1

ltskdhd12#

你还可以在做所有的数学运算之前进一步限制你必须计算的数据量。
当将纬度/经度值限制为附近的值时,可以限制必须计算距离的数据范围(来自这些幻灯片的公式(第12ff页)当你按英里计算时,这是有效的。。。否则,必须将“69”调整为正确的公里值。

1° of latitude ~= 69 miles 
1° of longitude ~= cos(latitude)*69

当您要计算较小矩形的经度和纬度时,可以使用(使用上面的变量):

$lon1 = $lon - $dist / abs(cos(deg2rad($lat))*69);
$lon2 = $lon + $dist / abs(cos(deg2rad($lat))*69);
$lat1 = $lat - ($dist / 69);
$lat2 = $lat + ($dist / 69);

然后修改查询,如下所示:

SELECT ads.ad_id
     , proximity AS P, ( 3959 * acos( cos( radians('".$usr_lat."') ) * 
                                      cos( radians( ads.lat ) ) * 
                                      cos( radians( ads.lon ) - 
                                           radians('".$usr_lon."') ) + 
                                      sin( radians('".$usr_lat."') ) * 
                                      sin( radians( ads.lat ) ) 
                                    ) ) AS distance 
FROM ads 
WHERE ads.type = '728x90' 
AND ads.active = '1' 
AND ads.lon BETWEEN $lon1 AND $lon2 
AND ads.lat BETWEEN $lat1 AND $lat2
HAVING distance <= P 
OR P='0' 
ORDER BY distance ASC

这将大大提高整体速度,因为您只使用所有坐标的一个子集。
还有一个备注:如果将lat/lon/distance作为数字字段,则不应在值周围使用“”,因为这会触发隐式转换,这可能也需要一些时间。。。

相关问题