如何通过空间数据库检测AIS信号是否进出AWS Redshift存储的地理围栏

1. 数据预处理 #

首先确保两张表的字段和格式符合空间计算要求：

• 表A（地理围栏表）至少需包含fence_id（围栏唯一ID）、fence_geometry（围栏空间几何对象，推荐使用Redshift支持的GEOGRAPHY类型适配WGS84经纬度坐标系，避免投影误差）、围栏名称等业务属性；如果围栏当前存储为WKT字符串，可通过ST_GeogFromText(fence_wkt)转换为GEOGRAPHY类型
• 表B（AIS日志表）至少需包含mmsi（船舶唯一识别码）、signal_time（信号产生时间戳）、lon（经度）、lat（纬度），计算前需先将经纬度转换为空间点对象：ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography as point_geo

2. 围栏关联计算（判断AIS点是否在围栏内） #

万级数据量下直接用Redshift原生空间函数关联计算即可，性能足够支撑：

WITH ais_with_geo AS (
    SELECT 
        mmsi,
        signal_time,
        ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography AS point_geo
    FROM table_b
    -- 每次仅处理单小时增量数据，缩小计算范围
    WHERE signal_time >= date_trunc('hour', current_timestamp - interval '1 hour')
      AND signal_time < date_trunc('hour', current_timestamp)
)
SELECT 
    a.mmsi,
    a.signal_time,
    b.fence_id,
    b.fence_name,
    ST_Covers(b.fence_geometry, a.point_geo) AS is_in_fence
FROM ais_with_geo a
CROSS JOIN table_a b
-- 仅计算距离围栏1km以内的点，过滤无效计算大幅提升性能
WHERE ST_DWithin(b.fence_geometry, a.point_geo, 1000)

注：ST_Covers函数对边界点的判定比ST_Contains更友好，不会漏判刚好落在围栏边界上的AIS信号。

3. 进出事件判定 #

基于相邻信号的围栏状态对比，判断是进入还是离开事件：

WITH point_fence_status AS (
    -- 此处可直接嵌套上一步的关联计算逻辑
    SELECT mmsi, signal_time, fence_id, is_in_fence
    FROM fence_join_result
),
status_with_prev AS (
    SELECT 
        *,
        -- 按船舶、围栏分组，取同维度上一个信号的围栏状态
        LAG(is_in_fence, 1) OVER (PARTITION BY mmsi, fence_id ORDER BY signal_time) AS prev_is_in_fence
    FROM point_fence_status
)
SELECT 
    mmsi,
    fence_id,
    signal_time AS event_time,
    CASE 
        WHEN is_in_fence = true AND (prev_is_in_fence = false OR prev_is_in_fence IS NULL) THEN 'enter'
        WHEN is_in_fence = false AND prev_is_in_fence = true THEN 'exit'
        ELSE NULL
    END AS event_type
FROM status_with_prev
WHERE event_type IS NOT NULL

如果要避免GPS单点漂移导致的误判，可以调整规则为连续2个信号状态一致时才判定为有效事件。

4. 生产环境落地建议 #

• 增量计算：无需全量扫描历史数据，每小时定时调度任务，仅处理上一小时的AIS增量数据，关联近2小时的历史状态做判定即可，单次计算耗时通常在秒级
• 性能优化：给地理围栏表创建空间索引，AIS表按时间字段做分区，可进一步降低计算开销
• 结果存储：单独创建进出事件结果表，存储所有检测到的enter/exit事件，方便后续业务查询、统计、告警使用

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