htlee
2e9361ee58
- CollectDB 다중 신호 수집 → S&P Global AIS API 단일 수집으로 전환 - sig_src_cd + target_id 이중 식별자 → mmsi(VARCHAR) 단일 식별자 - t_vessel_latest_position → t_ais_position 테이블 전환 - 레거시 배치/유틸 ~30개 클래스 삭제 (VesselAggregationJobConfig, ShipKindCodeConverter 등) - AisTargetCacheManager 기반 캐시 이중 구조 (최신위치 + 트랙 버퍼) - CacheBasedVesselTrackDataReader + CacheBasedTrackJobListener 신규 추가 - VesselStaticStepConfig: 정적정보 CDC 변경 검출 + hourly job 편승 - SignalKindCode enum: vesselType/extraInfo 기반 선종 자동 분류 - WebSocket/STOMP 전체 mmsi 전환 (StompTrackStreamingService ~40곳) - 모니터링/성능 최적화 코드 mmsi 기반 전환 - DataSource 설정 통합 (snpdb 단일 DB) - AreaBoundaryCache Polygon→Geometry 캐스트 수정 (MULTIPOLYGON 지원) - ConcurrentHashMap 적용 (VesselTrackStepConfig 동시성 버그 수정) Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
270 lines
10 KiB
SQL
270 lines
10 KiB
SQL
-- ========================================
|
|
-- 실제 데이터로 즉시 테스트 (변수 없음)
|
|
-- 최근 데이터 자동 선택
|
|
-- ========================================
|
|
|
|
-- 1. 최근 1시간 내 데이터가 있는 선박 자동 선택
|
|
WITH recent_vessel AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
DATE_TRUNC('hour', MIN(time_bucket)) as hour_bucket
|
|
FROM signal.t_vessel_tracks_5min
|
|
WHERE time_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
|
|
AND track_geom IS NOT NULL
|
|
AND public.ST_NPoints(track_geom) > 0
|
|
GROUP BY sig_src_cd, target_id, DATE_TRUNC('hour', time_bucket)
|
|
HAVING COUNT(*) >= 2
|
|
ORDER BY DATE_TRUNC('hour', MIN(time_bucket)) DESC
|
|
LIMIT 1
|
|
)
|
|
SELECT
|
|
'=== AUTO SELECTED VESSEL ===' as section,
|
|
sig_src_cd,
|
|
target_id,
|
|
hour_bucket,
|
|
hour_bucket + INTERVAL '1 hour' as hour_end
|
|
FROM recent_vessel;
|
|
|
|
-- 2. 선택된 선박의 5분 데이터 확인
|
|
WITH recent_vessel AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
DATE_TRUNC('hour', MIN(time_bucket)) as hour_bucket
|
|
FROM signal.t_vessel_tracks_5min
|
|
WHERE time_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
|
|
AND track_geom IS NOT NULL
|
|
AND public.ST_NPoints(track_geom) > 0
|
|
GROUP BY sig_src_cd, target_id, DATE_TRUNC('hour', time_bucket)
|
|
HAVING COUNT(*) >= 2
|
|
ORDER BY DATE_TRUNC('hour', MIN(time_bucket)) DESC
|
|
LIMIT 1
|
|
)
|
|
SELECT
|
|
'=== 5MIN DATA ===' as section,
|
|
t.sig_src_cd,
|
|
t.target_id,
|
|
t.time_bucket,
|
|
public.ST_NPoints(t.track_geom) as points,
|
|
public.ST_IsValid(t.track_geom) as is_valid,
|
|
LENGTH(public.ST_AsText(t.track_geom)) as wkt_length,
|
|
substring(public.ST_AsText(t.track_geom) from 'M \\((.+)\\)') as extracted_coords
|
|
FROM signal.t_vessel_tracks_5min t
|
|
INNER JOIN recent_vessel rv ON t.sig_src_cd = rv.sig_src_cd AND t.target_id = rv.target_id
|
|
WHERE t.time_bucket >= rv.hour_bucket
|
|
AND t.time_bucket < rv.hour_bucket + INTERVAL '1 hour'
|
|
AND t.track_geom IS NOT NULL
|
|
AND public.ST_NPoints(t.track_geom) > 0
|
|
ORDER BY t.time_bucket;
|
|
|
|
-- 3. string_agg 테스트
|
|
WITH recent_vessel AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
DATE_TRUNC('hour', MIN(time_bucket)) as hour_bucket
|
|
FROM signal.t_vessel_tracks_5min
|
|
WHERE time_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
|
|
AND track_geom IS NOT NULL
|
|
AND public.ST_NPoints(track_geom) > 0
|
|
GROUP BY sig_src_cd, target_id, DATE_TRUNC('hour', time_bucket)
|
|
HAVING COUNT(*) >= 2
|
|
ORDER BY DATE_TRUNC('hour', MIN(time_bucket)) DESC
|
|
LIMIT 1
|
|
)
|
|
SELECT
|
|
'=== STRING_AGG RESULT ===' as section,
|
|
t.sig_src_cd,
|
|
t.target_id,
|
|
string_agg(
|
|
substring(public.ST_AsText(t.track_geom) from 'M \\((.+)\\)'),
|
|
','
|
|
ORDER BY t.time_bucket
|
|
) FILTER (WHERE t.track_geom IS NOT NULL) as all_coords,
|
|
COUNT(*) as track_count,
|
|
LENGTH(string_agg(
|
|
substring(public.ST_AsText(t.track_geom) from 'M \\((.+)\\)'),
|
|
','
|
|
ORDER BY t.time_bucket
|
|
) FILTER (WHERE t.track_geom IS NOT NULL)) as coords_total_length
|
|
FROM signal.t_vessel_tracks_5min t
|
|
INNER JOIN recent_vessel rv ON t.sig_src_cd = rv.sig_src_cd AND t.target_id = rv.target_id
|
|
WHERE t.time_bucket >= rv.hour_bucket
|
|
AND t.time_bucket < rv.hour_bucket + INTERVAL '1 hour'
|
|
AND t.track_geom IS NOT NULL
|
|
AND public.ST_NPoints(t.track_geom) > 0
|
|
GROUP BY t.sig_src_cd, t.target_id;
|
|
|
|
-- 4. Geometry 생성 테스트
|
|
WITH recent_vessel AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
DATE_TRUNC('hour', MIN(time_bucket)) as hour_bucket
|
|
FROM signal.t_vessel_tracks_5min
|
|
WHERE time_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
|
|
AND track_geom IS NOT NULL
|
|
AND public.ST_NPoints(track_geom) > 0
|
|
GROUP BY sig_src_cd, target_id, DATE_TRUNC('hour', time_bucket)
|
|
HAVING COUNT(*) >= 2
|
|
ORDER BY DATE_TRUNC('hour', MIN(time_bucket)) DESC
|
|
LIMIT 1
|
|
),
|
|
merged_coords AS (
|
|
SELECT
|
|
t.sig_src_cd,
|
|
t.target_id,
|
|
string_agg(
|
|
substring(public.ST_AsText(t.track_geom) from 'M \\((.+)\\)'),
|
|
','
|
|
ORDER BY t.time_bucket
|
|
) FILTER (WHERE t.track_geom IS NOT NULL) as all_coords
|
|
FROM signal.t_vessel_tracks_5min t
|
|
INNER JOIN recent_vessel rv ON t.sig_src_cd = rv.sig_src_cd AND t.target_id = rv.target_id
|
|
WHERE t.time_bucket >= rv.hour_bucket
|
|
AND t.time_bucket < rv.hour_bucket + INTERVAL '1 hour'
|
|
AND t.track_geom IS NOT NULL
|
|
AND public.ST_NPoints(t.track_geom) > 0
|
|
GROUP BY t.sig_src_cd, t.target_id
|
|
)
|
|
SELECT
|
|
'=== GEOMETRY CREATION TEST ===' as section,
|
|
sig_src_cd,
|
|
target_id,
|
|
all_coords IS NOT NULL as has_coords,
|
|
LENGTH(all_coords) as coords_length,
|
|
public.ST_GeomFromText('LINESTRING M(' || all_coords || ')', 4326) as merged_geom,
|
|
public.ST_NPoints(public.ST_GeomFromText('LINESTRING M(' || all_coords || ')', 4326)) as merged_points,
|
|
public.ST_IsValid(public.ST_GeomFromText('LINESTRING M(' || all_coords || ')', 4326)) as is_valid
|
|
FROM merged_coords;
|
|
|
|
-- 5. 전체 집계 쿼리 실행 (실제 HourlyTrackProcessor와 동일)
|
|
WITH recent_vessel AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
DATE_TRUNC('hour', MIN(time_bucket)) as hour_bucket
|
|
FROM signal.t_vessel_tracks_5min
|
|
WHERE time_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
|
|
AND track_geom IS NOT NULL
|
|
AND public.ST_NPoints(track_geom) > 0
|
|
GROUP BY sig_src_cd, target_id, DATE_TRUNC('hour', time_bucket)
|
|
HAVING COUNT(*) >= 2
|
|
ORDER BY DATE_TRUNC('hour', MIN(time_bucket)) DESC
|
|
LIMIT 1
|
|
),
|
|
ordered_tracks AS (
|
|
SELECT t.*
|
|
FROM signal.t_vessel_tracks_5min t
|
|
INNER JOIN recent_vessel rv ON t.sig_src_cd = rv.sig_src_cd AND t.target_id = rv.target_id
|
|
WHERE t.time_bucket >= rv.hour_bucket
|
|
AND t.time_bucket < rv.hour_bucket + INTERVAL '1 hour'
|
|
AND t.track_geom IS NOT NULL
|
|
AND public.ST_NPoints(t.track_geom) > 0
|
|
ORDER BY t.time_bucket
|
|
),
|
|
merged_coords AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
string_agg(
|
|
substring(public.ST_AsText(track_geom) from 'M \\((.+)\\)'),
|
|
','
|
|
ORDER BY time_bucket
|
|
) FILTER (WHERE track_geom IS NOT NULL) as all_coords
|
|
FROM ordered_tracks
|
|
GROUP BY sig_src_cd, target_id
|
|
),
|
|
merged_tracks AS (
|
|
SELECT
|
|
mc.sig_src_cd,
|
|
mc.target_id,
|
|
rv.hour_bucket as time_bucket,
|
|
public.ST_GeomFromText('LINESTRING M(' || mc.all_coords || ')', 4326) as merged_geom,
|
|
(SELECT MAX(max_speed) FROM ordered_tracks WHERE sig_src_cd = mc.sig_src_cd AND target_id = mc.target_id) as max_speed,
|
|
(SELECT SUM(point_count) FROM ordered_tracks WHERE sig_src_cd = mc.sig_src_cd AND target_id = mc.target_id) as total_points,
|
|
(SELECT MIN(time_bucket) FROM ordered_tracks WHERE sig_src_cd = mc.sig_src_cd AND target_id = mc.target_id) as start_time,
|
|
(SELECT MAX(time_bucket) FROM ordered_tracks WHERE sig_src_cd = mc.sig_src_cd AND target_id = mc.target_id) as end_time,
|
|
(SELECT start_position FROM ordered_tracks WHERE sig_src_cd = mc.sig_src_cd AND target_id = mc.target_id ORDER BY time_bucket LIMIT 1) as start_pos,
|
|
(SELECT end_position FROM ordered_tracks WHERE sig_src_cd = mc.sig_src_cd AND target_id = mc.target_id ORDER BY time_bucket DESC LIMIT 1) as end_pos
|
|
FROM merged_coords mc
|
|
CROSS JOIN recent_vessel rv
|
|
),
|
|
calculated_tracks AS (
|
|
SELECT
|
|
*,
|
|
public.ST_Length(merged_geom::geography) / 1852.0 as total_distance,
|
|
CASE
|
|
WHEN public.ST_NPoints(merged_geom) > 0 THEN
|
|
public.ST_M(public.ST_PointN(merged_geom, public.ST_NPoints(merged_geom))) -
|
|
public.ST_M(public.ST_PointN(merged_geom, 1))
|
|
ELSE
|
|
EXTRACT(EPOCH FROM
|
|
CAST(end_pos->>'time' AS timestamp) - CAST(start_pos->>'time' AS timestamp)
|
|
)
|
|
END as time_diff_seconds
|
|
FROM merged_tracks
|
|
)
|
|
SELECT
|
|
'=== FULL AGGREGATION RESULT ===' as section,
|
|
sig_src_cd,
|
|
target_id,
|
|
time_bucket,
|
|
public.ST_NPoints(merged_geom) as merged_points,
|
|
public.ST_IsValid(merged_geom) as is_valid,
|
|
total_distance,
|
|
CASE
|
|
WHEN time_diff_seconds > 0 THEN
|
|
CAST(LEAST((total_distance / (time_diff_seconds / 3600.0)), 9999.99) AS numeric(6,2))
|
|
ELSE 0
|
|
END as avg_speed,
|
|
max_speed,
|
|
total_points,
|
|
start_time,
|
|
end_time,
|
|
time_diff_seconds
|
|
FROM calculated_tracks;
|
|
|
|
-- 6. 에러 발생 가능성 체크
|
|
WITH recent_vessel AS (
|
|
SELECT
|
|
sig_src_cd,
|
|
target_id,
|
|
DATE_TRUNC('hour', MIN(time_bucket)) as hour_bucket
|
|
FROM signal.t_vessel_tracks_5min
|
|
WHERE time_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
|
|
AND track_geom IS NOT NULL
|
|
AND public.ST_NPoints(track_geom) > 0
|
|
GROUP BY sig_src_cd, target_id, DATE_TRUNC('hour', time_bucket)
|
|
HAVING COUNT(*) >= 2
|
|
ORDER BY DATE_TRUNC('hour', MIN(time_bucket)) DESC
|
|
LIMIT 1
|
|
)
|
|
SELECT
|
|
'=== ERROR CHECK ===' as section,
|
|
COUNT(*) as total_tracks,
|
|
COUNT(CASE WHEN track_geom IS NULL THEN 1 END) as null_geom_count,
|
|
COUNT(CASE WHEN NOT public.ST_IsValid(track_geom) THEN 1 END) as invalid_geom_count,
|
|
COUNT(CASE WHEN public.ST_NPoints(track_geom) = 0 THEN 1 END) as zero_points_count,
|
|
COUNT(CASE WHEN public.ST_NPoints(track_geom) = 1 THEN 1 END) as single_point_count,
|
|
COUNT(CASE WHEN
|
|
substring(public.ST_AsText(track_geom) from 'M \\((.+)\\)') IS NULL
|
|
THEN 1 END) as regex_fail_count
|
|
FROM signal.t_vessel_tracks_5min t
|
|
INNER JOIN recent_vessel rv ON t.sig_src_cd = rv.sig_src_cd AND t.target_id = rv.target_id
|
|
WHERE t.time_bucket >= rv.hour_bucket
|
|
AND t.time_bucket < rv.hour_bucket + INTERVAL '1 hour';
|
|
|
|
-- ========================================
|
|
-- 사용 방법:
|
|
-- 1. 그냥 전체 스크립트 실행
|
|
-- 2. 자동으로 최근 선박 선택됨
|
|
-- 3. 각 섹션별 결과 확인
|
|
--
|
|
-- 에러 발생시 확인 사항:
|
|
-- - "ERROR CHECK" 섹션에서 이상값 확인
|
|
-- - "STRING_AGG RESULT"에서 all_coords 확인
|
|
-- - "GEOMETRY CREATION TEST"에서 is_valid 확인
|
|
-- ========================================
|