如何编写查询判断Table2的经纬度是否在Table1指定区域内

核心判断逻辑 #

要判断Table2的坐标是否处于Table1的矩形区域内，需同时满足两个条件：

• 纬度（_Geo_Lat）落在Table1的南边界（MapSouthLat）和北边界（MapNorthLat）之间
• 经度（_Geo_Lon）落在Table1的西边界（MapWestLong）和东边界（MapEastLong）之间

结合你提供的示例数据，西经数值越大越靠东（比如-77.929218比-82.380500更靠东），所以上述经度范围判断逻辑是成立的。

场景1：Table1仅存单个区域记录 #

如果Table1只有一条区域数据，可通过以下两种方式实现判断：

方式1：带区域标记的全量查询

SELECT 
    t2.*,
    CASE 
        WHEN t2._Geo_Lat BETWEEN t1.MapSouthLat AND t1.MapNorthLat
             AND t2._Geo_Lon BETWEEN t1.MapWestLong AND t1.MapEastLong
        THEN '在区域内'
        ELSE '不在区域内'
    END AS is_in_area
FROM Table2 t2
CROSS JOIN Table1 t1;

方式2：仅判断是否匹配区域

SELECT 
    t2.*,
    EXISTS (
        SELECT 1 
        FROM Table1 t1
        WHERE t2._Geo_Lat BETWEEN t1.MapSouthLat AND t1.MapNorthLat
              AND t2._Geo_Lon BETWEEN t1.MapWestLong AND t1.MapEastLong
    ) AS is_in_area
FROM Table2 t2;

场景2：Table1存在多个区域记录 #

如果Table1包含多个区域数据，可通过LEFT JOIN关联，查看坐标所属的具体区域：

SELECT 
    t2.*,
    t1.MapNorthLat,
    t1.MapSouthLat,
    t1.MapEastLong,
    t1.MapWestLong,
    CASE 
        WHEN t2._Geo_Lat BETWEEN t1.MapSouthLat AND t1.MapNorthLat
             AND t2._Geo_Lon BETWEEN t1.MapWestLong AND t1.MapEastLong
        THEN '在区域内'
        ELSE '不在区域内'
    END AS is_in_area
FROM Table2 t2
LEFT JOIN Table1 t1
    ON t2._Geo_Lat BETWEEN t1.MapSouthLat AND t1.MapNorthLat
       AND t2._Geo_Lon BETWEEN t1.MapWestLong AND t1.MapEastLong;

内容的提问来源于stack exchange，提问作者john