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

jyztefdp 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(285)

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

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"

mysql php Distance GeoLocation proximity

来源：https://stackoverflow.com/questions/53045142/sql-query-to-serve-ads-based-on-proximity-to-user

2条答案

按热度按时间

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'来提供没有指定邻近性的广告。

赞(0）回复(0）举报 2021-06-18

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作为数字字段，则不应在值周围使用“”，因为这会触发隐式转换，这可能也需要一些时间。。。

赞(0）回复(0）举报 2021-06-18

我来回答

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

2条答案

相关问题

热门标签

最新问答