一种解决经度跨越 180° 时的查询方法
如上图所示,有紫色、红色、黄色三个矩形区域,其中紫色区域完全在东半球,黄色区域完全在西半球,而红色区域一半在东半球一半在西半球,即它跨越了 180°(-180°) 经线。我们用左上/右下两个点来表示一个矩形区域,因此紫色区域表示为 (147.66, 36.31) / (159.71, 30.88)
,红色区域表示为 (173.53, 36.49) / (-173.32, 31.17)
,黄色区域表示为 (-160.49, 36.31) / (-147.24, 30.98)
。在每一个区域中分布着一些点,现在需要一种把这些点查询出来的方法。
为了实现这个查询,我们需要创建一张存储这些点的表,其中 P1/P2 在紫色区域,P3/P4/P5 在红色区域,P6/P7/P8/P9 在黄色区域
1 | CREATE TABLE points |
要查询每个区域都有哪些点在里面,我们结合左上/右下定义区域的特点使用下面的过滤条件
1 | SELECT * |
当区域整个都在东半球或西半球时,即紫色区域和黄色区域,我们可以使用 longitude >= upper_left_longitude AND longitude <= lower_right_longitude
这样的表达式。但是对于一半在东半球一半在西半球的区域,即红色区域,来说就不能使用这个表达式了。我们可以把红色区域的经度带入表达式看看 longitude >= 173.53 AND longitude <= -173.32
,此时不会有任何结果。
我们知道经度的范围为 [-180°, 180°]
,而 180° 经线和 -180° 经线是同一条经线,对于跨 180°(-180°) 经线的区域来说,一个点要么在东半球要么在西半球,当左上/右下两个点的经度的差的绝对值超过 180° 时应该拆分为两个查询 longitude >= upper_left_longitude AND longitude <= 180
(表示在东半球)和 longitude >= -180 AND longitude <= lower_right_longitude
(表示在西半球)的组合,即
1 | -- 表示在东半球 |
为了方便查询三种区域类型的情况,我们需要把查询写到一个函数里面
1 | CREATE OR REPLACE FUNCTION fn_get_points(upper_left_longitude NUMERIC, upper_left_latitude NUMERIC, lower_right_longitude NUMERIC, lower_right_latitude NUMERIC) |
现在下面的三个查询都可以得到想要的结果
1 | -- 紫色 |
上面的函数还是有点复杂,可以使用在 PostgreSQL 函数中使用可选条件中的方法简化 IF ... ELSE ... END IF
表达式。而表达一个点要么在东半球要么在西半球还可以使用 (longitude >= upper_left_longitude AND longitude <= 180) OR (longitude >= -180 AND longitude <= lower_right_longitude)
简化 UNION ALL
查询
1 | CREATE OR REPLACE FUNCTION fn_get_points(upper_left_longitude NUMERIC, upper_left_latitude NUMERIC, lower_right_longitude NUMERIC, lower_right_latitude NUMERIC) |
因为在跨 180°(-180°) 经线时 (longitude >= upper_left_longitude AND longitude <= 180) OR (longitude >= -180 AND longitude <= lower_right_longitude)
与 longitude >= upper_left_longitude OR longitude <= lower_right_longitude
又是等价的,因此可以进一步简化为
1 | CREATE OR REPLACE FUNCTION fn_get_points(upper_left_longitude NUMERIC, upper_left_latitude NUMERIC, lower_right_longitude NUMERIC, lower_right_latitude NUMERIC) |
这种简化其实是利用了经度的特点,没有经度大于 180° 的点,也没有经度小于 -180° 的点,当经度大于 180° 或者小于 -180° 时可以用另一个半球的经度来表示这个点。
完~