Get results that fall within marker radiuses from database(从数据库中获取落在标记半径内的结果)

问题描述

2012 年 11 月 16 日更新

我想再次提出这个问题，为一个可靠的、好的解决方案提供新的奖励.似乎只有解决方案(

编辑:我找到了一个更好的，这非常接近，但仍然不是我想要的，因为当我在一个表中有多个时，它使用 1 个纬度和经度坐标范围.

• 在边界圆内按纬度/经度从数据库中选择点

几何的要点是，如果两个圆的圆心之间的距离小于它们的半径之和，则它们会重叠.由于我们正在进行比较，我们可以使用距离的平方，因为这样可以避免平方根运算.原来，每个半径固定为1，两个半径之和为2，和的平方为4.

原始问题和新问题之间存在很大差异.在第一个中你有固定半径的圆，第二个你有不同半径的圆.比较表达式 [...distance^2...] <= 4 中的常量 4 需要替换，因为这是固定半径的工件原本的.要实现这一点，请将 km 字段添加到查询中.正如您应该检查的那样，您没有在 WHERE 过滤器中使用 ppl.radius，因此改变该值不会改变您的查询结果也就不足为奇了.

SELECT ppl.latitude, ppl.longitude, ppl.radius从(人民群众)，(从半径选择纬度，经度，公里)AS BWHERE [...distance^2...] <= POW( ppl.radius + B.km, 2)

我应该说，理解这个问题所花的时间比它应该的要长得多，因为您将实体(不是人)称为半径"，而实际上您有一个属性应该在两个不同的实体上被称为半径".因此，将其他实体命名为具有描述性的名称.

Update 16th November 2012

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.

Firstly, this is the live map I have with radiuses and people, the radiuses are in red and the people are in blue.

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.

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.

The data, PEOPLE:

+-----------+-----------+--------+
| latitude  | longitude | radius |
+-----------+-----------+--------+
| 51.517395 | -0.053129 | 5.6    |
| 51.506607 | -0.116129 | 0.7    |
+-----------+-----------+--------+

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 |
+-----------+-----------+-----+

The current SQL I use:

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

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.

I have a map applet which a user can place his radius of a lat/lng location, with a 1km radius.

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).

Like this User A is red and User B is blue.

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 |
+-----------+---------+-----------+-----------+

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 |
+---------+-----------+-----------+

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.

I'm sure this is possible, but I do not know how to come up with this kind of system in MySQL.

I found this on the Google Developers site it's close but not just what it performs I need.

• Creating a Store Locator with PHP, MySQL & Google Maps

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.

• Select points from a database by latitude/longitude within a bounding circle

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.

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.

这篇关于从数据库中获取落在标记半径内的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！