给定纬度和经度，找到对该位置感兴趣的用户

Question

给定一个表，

+-----+---------+---------+---------+---------+---------+
| user| min_lat | max_lat | min_lng | max_lng |
+-----+---------+---------+---------+---------+---------+
| a   |    46   |     407 |       6 |     367 | 
| b   |   226   |     227 |     186 |     188 | 

和一个 Point(x, y) 查找该点位于用户的最小和最大纬度和经度范围内的用户（其中​​最小和最大长和纬度 = 当前位置减去或加上半径）。

最小值可以小于 0，最大值可以大于 360，查询需要考虑这些。

例如，使用Point(7,5)进行过滤也应该返回用户A，因为367-360=7。

不确定我是否正确，但希望有人能给我一些见解。

Answer 1

我建议限制纬度和范围表中存储的经度值范围为 [0, 360]。在插入和更新之前创建触发器以强制 mod 360 等效性，并且如果 max-min > 360，将最小值和最大值分别设置为 0 和 360。例如：

delimiter ;;

CREATE TRIGGER normalize_inserted_ranges BEFORE INSERT 
  ON table
  FOR EACH ROW BEGIN
    IF NEW.max_lat - NEW.min_lat >= 360 THEN
        SET NEW.min_lat=0;
        SET NEW.max_lat=360;
    ELSE
        SET NEW.min_lat = NEW.min_lat % 360;
        SET NEW.max_lat = NEW.max_lat % 360;
    END IF;
    IF NEW.max_lng - NEW.min_lng >= 360 THEN
        SET NEW.min_lng=0;
        SET NEW.max_lng=360;
    ELSE
        SET NEW.min_lng = NEW.min_lng % 360;
        SET NEW.max_lng = NEW.max_lng % 360;
    END IF;
  END
;;
delimiter ;

然后您可以使用以下查询：

SELECT user FROM table
  WHERE
      IF(min_lng <= max_lng, 
         @x BETWEEN min_lng AND max_lng, 
         @x <= max_lng OR min_lng <= @x)
    AND
      IF(min_lat <= max_lat,
         @y BETWEEN min_lat AND max_lat, 
         @y <= max_lat OR min_lat <= @y)
;

Answer 2

为了回答您的查询的一部分，如果您说 367 和 7 相同，我同样假设 727 也是相同的。

因此，您想要使用 360 的模数。例如除以 360 的余数。

例如（其中 % 是 C# 和 C++ 语法，您可能会找到 SQL 方法来执行此操作）

7 % 360 = 7
367 % 360 = 7
727 % 360 = 7

看起来像 SELECT b MOD 360 from table; 就是你想要的东西。

Answer 3

我怀疑有一个更优雅的答案，但我认为 SQL where 子句的限制使这变得更加困难。

假设：

• 0 <= x < 360
• 0 <= y < 360
• max_lat - min_lat <= 361
• max_lng - min_lng <= 361

从 user_location WHERE 中选择用户  
((min_lat < 0 AND ((0 <= y AND y <= max_lat) OR (min_lat + 360 <= y AND y < 360))) OR  
 (min_lat >= 0 AND ((min_lat <= y AND y <= max_lat) OR (0 <= y AND y <= max_lat - 360))))  
和  
((min_lng < 0 AND ((0 <= x AND x <= max_lng) OR (min_lng + 360 <= x AND x <= 360))) OR  
 (min_lng >= 0 AND ((min_lng <= x AND x <= max_lng) OR (0 <= x AND x <= max_lng - 360))))

在一维中，如果 min < 0 则 x 必须介于 0 和 max 之间，或者 x 必须介于 min + 360 和 360 之间。类似地，如果 min >= 0 则 x 必须介于 min 和 max 之间，或者它必须介于 0 和 max - 360 之间。

上述格式是为了清楚起见而提出的。如果将 360 的加法和减法移至 x 和 y 参数，比较将变得恒定，并且可能会显着加快查询速度。

Answer 4

使用模来确保所有值都在 0 到 360 之间，查询变得非常简单。假设 {pointLat} 和 {pointLng} 是要过滤的点的坐标。

SELECT *
FROM table
WHERE IF(min_lat < max_lat,
        MOD({pointLat}, 360) BETWEEN MOD(min_lat, 360) AND MOD(max_lat, 360)),
        MOD({pointLat}, 360) NOT BETWEEN MOD(min_lat, 360) AND MOD(max_lat, 360)))
    AND IF(min_lng < max_lng,
        MOD({pointLng}, 360) BETWEEN MOD(min_lng, 360) AND MOD(max_lng, 360)),
        MOD({pointLng}, 360) NOT BETWEEN MOD(min_lng, 360) AND MOD(max_lng, 360)));

虽然这可行，但我强烈建议在 SQL 查询之外执行 MOD 计算或添加具有标准化值的额外列。使用此代码中的 MOD 函数将阻止查询利用这些列上的任何索引。