从数据库获取落在标记半径内的结果 [英] Get results that fall within marker radiuses from database
问题描述
我想再次提出这个问题，并提供新的赏金，以寻求可靠，良好的解决方案.似乎只有解决方案( shubhansh的答案)现在无法有效工作.我会解释原因.
I would like to raise this question again, offering with a new bounty for a solid, good solution. It seems that only the solution (shubhansh's answer) does not effectively work now. I will explain why.
首先，这是我拥有的半径和人员实时地图，半径在 red 中，人员在 blue 中.
Firstly, this is the live map I have with radiuses and people, the radiuses are in red and the people are in blue.
如您所见，此地图中有 two 个半径为 eight 的人，基本上我只会得到 Person A ，但是我没有得到 Person B ，我猜想SQL无法正确地拾取它，因此我需要从人的半径和角度来精确和准确地拾取它标记半径.
As you can see, there are two people in this map with eight radiuses, basically I am getting only the person which is Person A, but I am not getting Person B, I'm guessing that the SQL is not correctly picking it up which I need it to be precise and accurate from the person's radius and the marker radiuses.
看起来被拾取的东西是在半径之内，而不是那些重叠半径的东西，我需要它能够拾取任何相互重叠的半径的结果.
It looks like what is picked up are are inside the radiuses, not those who overlap a radius, I need it to be able to pick up any results for any radiuses that overlap each other.
我正在寻找一种比shubhansh的答案更精确的SQL.您可以在下面阅读，以详细了解我需要该查询如何采取行动并找到准确的人.
I am looking for a precise and accurate SQL than shubhansh's answer. You may read below to read how exactly I need the query to act and pick up accurate people.
数据，人:
+-----------+-----------+--------+
| latitude | longitude | radius |
+-----------+-----------+--------+
| 51.517395 | -0.053129 | 5.6 |
| 51.506607 | -0.116129 | 0.7 |
+-----------+-----------+--------+
请注意，radius以公里为单位.
Please note that radius is in kilometers.
+-----------+-----------+-----+
| latitude | longitude | km |
+-----------+-----------+-----+
| 51.502117 | -0.103340 | 0.3 |
| 51.498913 | -0.120850 | 0.7 |
| 51.496078 | -0.108919 | 0.7 |
| 51.496506 | -0.095873 | 0.7 |
| 51.503399 | -0.090723 | 0.7 |
| 51.508049 | -0.100336 | 0.7 |
| 51.508797 | -0.112610 | 0.7 |
| 51.505535 | -0.125227 | 0.7 |
| 51.502331 | -0.108061 | 0.7 |
+-----------+-----------+-----+
我使用的当前SQL:
SELECT ppl.latitude,
ppl.longitude,
ppl.radius
FROM
(
people ppl
),
(
SELECT latitude, longitude
FROM radiuses
) AS radius
WHERE (POW((ppl.longitude - radius.longitude) * 111.12 * COS(ppl.latitude), 2) + POW((ppl.longitude - radius.longitude) * 111.12, 2)) <= 4
GROUP BY ppl.id
可用于测试查询的MySQL数据
The data for MySQL which you can use to test your query,
INSERT INTO radiuses (id, latitude, longitude, km) VALUES ('1', '51.502117', '-0.103340', '0.3'), ('2', '51.498913', '-0.120850', '0.7'), ('3', '51.496078', '-0.108919', '0.7'), ('4', '51.496506', '-0.095873', '0.7'), ('5', '51.503399', '-0.090723', '0.7'), ('6', '51.508049', '-0.100336', '0.7'), ('7', '51.508797', '-0.112610', '0.7'), ('8', '51.505535', '-0.125227', '0.7'), ('9', '51.502331', '-0.108061', '0.7');
INSERT INTO people (id, latitude, longitude, radius) VALUES ('1', '51.517395', '-0.053129', '5.6'), ('2', '51.506607', '-0.116129', '0.7');
旧摘要
注意:所有纬度和经度都是随机产生的.
Old summary
Note: all the latitudes and longitudes are just randomly made.
我有一个地图小程序，用户可以在地图小程序中放置半径为1公里的经/纬度位置的半径.
I have a map applet which a user can place his radius of a lat/lng location, with a 1km radius.
现在，还有另一个用户可以将其半径放置在地图上的任意位置，每个半径1km(与上面的用户相同).
Now, there is another user that can put his radiuses, at any location on the map, each with 1km radius (same as the user above).
就像用户A 是红色和用户B 是蓝色一样.
Like this User A is red and User B is blue.
基本上用户A 将其半径存储在如下表中:
Basically User A stores his radiuses in a table that looks like this:
+-----------+---------+-----------+-----------+
| radius_id | user_id | latitude | longitude |
+-----------+---------+-----------+-----------+
| 1 | 1 | 81.802117 | -1.110035 |
| 2 | 1 | 81.798272 | -1.144196 |
| 3 | 1 | 81.726782 | -1.135919 |
+-----------+---------+-----------+-----------+
用户B 将其半径存储在另一个如下表中-(注意:每个帐户只能存储1个坐标):
And User B stores his radius in another table that looks like this - (note: they can only store 1 coordinates per account):
+---------+-----------+-----------+
| user_id | latitude | longitude |
+---------+-----------+-----------+
| 6 | 81.444126 | -1.244910 |
+---------+-----------+-----------+
我希望能够找到地图图片中落入定义半径范围内的那些用户，即使半径圆圈在触摸.只有标记C可以拾取单个半径，而A和B则不可以.
I want to be able to pick up those users that fall within the defined radiuses, even if the radius circles are touching, in the map picture. Only marker C would be able to pick up the single radius, when A and B do not.
我肯定这是可能的，但是我不知道如何在MySQL中提出这种系统.
I'm sure this is possible, but I do not know how to come up with this kind of system in MySQL.
我在Google Developers网站上发现了这一点，但它并不只是我需要的功能.
I found this on the Google Developers site it's close but not just what it performs I need.
EDIT :我找到了一个更好的方法，它非常接近，但仍不是我想要的，因为当我在一个表中有多个时，它使用1个经纬度坐标
EDIT: I have found a better one, this is very close, but still not what I am looking for, since it uses 1 bound of latitude and longitude coordinates when I have multiple in a table.
推荐答案
几何的基本要点是，如果两个圆心之间的距离小于其半径之和，则两个圆重叠.由于我们正在进行比较，因此可以使用距离的平方，因为这样可以避免平方根运算.在原始图中，每个半径固定为1，两个半径的和为2，和的平方为4.
The essential point of your geometry is that two circles overlap if the distance between their centers is less than the sum of their radii. Since we're doing a comparison, we can use the square of the distance, since that avoids a square root operation. In the original, each radius is fixed at 1, the sum of the two radii is 2, and the square of the sum is 4.
原始问题和新问题之间存在很大差异.在第一个中，您具有固定半径的圆，第二个中，您具有可变半径的圆.比较表达式[...distance^2...] <= 4中的常量4需要替换，因为这是原始对象固定半径的伪像.要实现此目的，请将km字段添加到查询中.正如您应该检查的那样，您没有在WHERE过滤器中使用ppl.radius，因此更改该值不会更改您的查询结果也就不足为奇了.
There's a big difference between the original question and the new question. In the first you've got circles of fixed radius and the second you've got circles of varying radius. The constant 4 in the comparison expression [...distance^2...] <= 4 needs to be replaced, since that's an artifact of the fixed radius of the original. To implement this, add the km field into the query. And as you should check, you weren't using ppl.radius in the WHERE filter, so it's hardly surprising that varying that value didn't change your query results.
SELECT ppl.latitude, ppl.longitude, ppl.radius
FROM
( people ppl ),
( SELECT latitude, longitude, km FROM radiuses ) AS B
WHERE [...distance^2...] <= POW( ppl.radius + B.km, 2)
我应该说，这个问题花费的时间比应该理解的要长得多，因为当您确实拥有应有的财产时，您就将非人的实体称为半径"在两个不同的实体上被称为半径".所以给其他实体起个描述性的作用.
I should say that this question took far longer to understand than it should have, because you're calling the entity-that's-not-a-person a "radius", when really you've got a property that ought to be called 'radius' on two different entities. So name that other entity something descriptive.
这篇关于从数据库获取落在标记半径内的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
