CCTV_ver_4.1. 데이터 분석 시스템

9 minute read

➡ 위 이미지를 클릭하면 Repository로 이동합니다.

과제 목표

리눅스 서버 환경 구축
데이터 베이스 분석 시스템(분석 table생성, 이벤트 생성, 이상값 탐지)
영상팀에서 호출할 수 있는 API 생성(인증절차 추후 적용 예정)

1. 리눅스 서버 환경 구축

리눅스 서버에 mysql설치 및 접속설정

mysql 설치 완료 , DB생성
사용자 계정 생성 및 DB권한 부여
외부 접속 허용 3306 포트 allow

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cctv               |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

mysql> show grants;
+-------------------------------------------------+
| Grants for malab@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `malab`@`%`               |
| GRANT ALL PRIVILEGES ON `cctv`.* TO `malab`@`%` |
+-------------------------------------------------+
2 rows in set (0.01 sec)

Node.js 설치

PPA 로 node.js와 npm설치
$ node -v
v14.17.4
$ npm -v
6.14.14

MySQL table 생성

외래키를 설정하여 rid로 참조될 수 있도록 함

mysql> show tables;
+------------------------+
| Tables_in_cctv         |
+------------------------+
| bicycle_pos            |
| bicycle_speed          |
| bus_pos                |
| bus_speed              |
| car_pos                |
| car_speed              |
| cctv_bbox              | 
| cctv_data              | < --- 기본 전체 데이터 (1분당 1 row)
| electric_scooter_pos   |
| electric_scooter_speed |
| motocycle_pos          |
| motocycle_speed        |
| person_pos             |
| person_speed           |
| truck_pos              |
| truck_speed            |
+------------------------+
16 rows in set (0.00 sec)

mysql> DESC cctv_data;
+--------------------+------------+------+-----+---------+----------------+
| Field              | Type       | Null | Key | Default | Extra          |
+--------------------+------------+------+-----+---------+----------------+
| rid                | int        | NO   | PRI | NULL    | auto_increment |
| date_time          | timestamp  | YES  |     | NULL    |                |
| n_person           | int        | YES  |     | NULL    |                |
| n_car              | int        | YES  |     | NULL    |                |
| n_truck            | int        | YES  |     | NULL    |                |
| n_bus              | int        | YES  |     | NULL    |                |
| n_bicycle          | int        | YES  |     | NULL    |                |
| n_motorcycle       | int        | YES  |     | NULL    |                |
| n_electric_scooter | int        | YES  |     | NULL    |                |
| image              | mediumblob | YES  |     | NULL    |                |
+--------------------+------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> desc person_pos;
+-----------+-----------+------+-----+---------+-------+
| Field     | Type      | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| rid       | int       | NO   | PRI | NULL    |       |
| date_time | timestamp | YES  |     | NULL    |       |
| loc_0     | int       | YES  |     | NULL    |       |
| loc_1     | int       | YES  |     | NULL    |       |
| loc_2     | int       | YES  |     | NULL    |       |
| loc_3     | int       | YES  |     | NULL    |       |
| loc_4     | int       | YES  |     | NULL    |       |
| loc_5     | int       | YES  |     | NULL    |       |
| loc_6     | int       | YES  |     | NULL    |       |
| loc_7     | int       | YES  |     | NULL    |       |
| loc_8     | int       | YES  |     | NULL    |       |
+-----------+-----------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql> desc person_speed;
+-----------+-----------+------+-----+---------+-------+
| Field     | Type      | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| rid       | int       | NO   | PRI | NULL    |       |
| date_time | timestamp | YES  |     | NULL    |       |
| loc_0     | float     | YES  |     | NULL    |       |
| loc_1     | float     | YES  |     | NULL    |       |
| loc_2     | float     | YES  |     | NULL    |       |
| loc_3     | float     | YES  |     | NULL    |       |
| loc_4     | float     | YES  |     | NULL    |       |
| loc_5     | float     | YES  |     | NULL    |       |
| loc_6     | float     | YES  |     | NULL    |       |
| loc_7     | float     | YES  |     | NULL    |       |
| loc_8     | float     | YES  |     | NULL    |       |
+-----------+-----------+------+-----+---------+-------+
11 rows in set (0.00 sec)

Table 생성 코드

   create table cctv_data(
	  rid int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      date_time TIMESTAMP ,
      n_person int ,
      n_car int,
      n_truck int,
      n_bus int,
      n_bicycle int,
      n_motorcycle int,
      n_electric_scooter int
  );    
   
   create table person_pos(
	  rid int NOT NULL PRIMARY KEY,
      date_time TIMESTAMP ,
	  loc_0 int,
      loc_1 int,
      loc_2 int,
      loc_3 int,
      loc_4 int,
      loc_5 int,
      loc_6 int,
      loc_7 int,
      loc_8 int,
      foreign key (rid) references cctv_data (rid)
  );  
   
     create table person_speed(
	  rid int NOT NULL primary key,
      date_time TIMESTAMP ,
	  loc_0  float,
      loc_1  float,
      loc_2  float,
      loc_3  float,
      loc_4  float,
      loc_5  float,
      loc_6  float,
      loc_7  float,
      loc_8  float,
      foreign key (rid) references cctv_data (rid)
  );  
   

타임존 설정 및 확인

이상하게 UTC시간이 현재 한국시간으로 나오고, 서버의 KST시간이 현재한국시간보다 +9 시간으로 나온다…..
그래서 다시 UTC 기준으로 변경하였다.

우분투 서버

  ~$ timedatectl
               Local time: 토 2021-08-21 22:38:11 UTC --------현재 한국시간
           Universal time: 토 2021-08-21 22:38:11 UTC
                 RTC time: 토 2021-08-21 22:38:11
                Time zone: UTC (UTC, +0000)
System clock synchronized: no
              NTP service: active
          RTC in local TZ: no

mysql
mysql도 마찬가지로 +9 시간으로 나와서 SYSTEM시간으로 돌려주었다.

mysql> select @@global.time_zone, @@session.time_zone,@@system_time_zone;
+--------------------+---------------------+--------------------+
| @@global.time_zone | @@session.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM             | SYSTEM              | UTC                |
+--------------------+---------------------+--------------------+
1 row in set (0.04 sec)

2. 데이터 베이스 분석 시스템

by. 서현, 가희

1. 데이터베이스 분석 테이블 생성 및 이벤트 스케줄러 생성

- 데이터베이스 테이블 생성

Table 생성 코드

  # 일 별 테이블 생성
  create table day_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _date date,
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
   desc day_stats;

  # 주 별 테이블 생성
  create table week_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date,
      end_date date,
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월 별 테이블 생성
  create table month_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
   
 # 연도별 테이블 생성
  create table year_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, #년도
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  

  # 월간 요일별 테이블 생성 (월요일)
  create table monthly_mon_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성(화요일)
  create table monthly_tue_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성(수요일)
  create table monthly_wed_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성(목요일)
  create table monthly_thu_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성 (금요일)
  create table monthly_fri_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성 (토요일)
  create table monthly_sat_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 월간 요일별 테이블 생성 (일요일)
  create table monthly_sun_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      start_date date, #월 포맷이 없어서 그냥 해당 달의 첫날로함
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  show tables;

  # 연간 요일별 테이블 생성 (월요일)
  create table yearly_mon_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 연간 요일별 테이블 생성 (화요일)
  create table yearly_tue_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (수요일)
  create table yearly_wed_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (목요일)
  create table yearly_thu_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (금요일)
  create table yearly_fri_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );
  # 연간 요일별 테이블 생성 (토요일)
  create table yearly_sat_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

  # 연간 요일별 테이블 생성 (일요일)
  create table yearly_sun_stats(
    _id INT PRIMARY KEY AUTO_INCREMENT,
      _year year, 
      avg_people float,
      std_people float,
    avg_vehicle float,
      std_vehicle float
  );

- MySQl Event Scheduler

💢 주의
CCTV 데이터는 현재 로컬 시간에 대한 UTC시간으로 저장될 것
본 Event는 UTC시간을 기준으로 스케줄러가 작동됨
따라서 데이터 조회 시, 사용자로부터 입력받은 로컬시간을 UTC시간으로 변환하여 조회해야함

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 `day_stats` 테이블에 저장

CREATE EVENT `day_stats`
    ON SCHEDULE EVERY 1 Day 
    STARTS CURDATE() #오늘부터 시작 (표준시간 기준임, 한국시간은 +9 hour 해줘야 함)
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of day'
    DO 
	INSERT INTO day_stats(_date, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT  DATE_ADD(CURDATE(),INTERVAL -1 day)  , CONVERT(AVG(people), float), CONVERT(AVG(people)+1.5*STDDEV(people), float),
				CONVERT(AVG(vehicle), float), CONVERT(AVG(vehicle)+1.5*STDDEV(vehicle), float)
                FROM test  WHERE DATE(start_date) =  DATE_ADD(CURDATE(), INTERVAL -1 day) #어제 데이터

2). 매주 월요일에 `day_stats` table의 일주일 치(7개의 row) 평균을 `week_stats` 테이블에 저장

  CREATE EVENT `week_stats`
    ON SCHEDULE
	EVERY 1 WEEK
	STARTS CURRENT_DATE + INTERVAL 7- WEEKDAY(CURRENT_DATE) DAY #가장 가까운 미래의 월요일
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of week'
    DO 
	INSERT INTO week_stats(start_date, end_date, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT CURRENT_DATE + INTERVAL -7- WEEKDAY(CURRENT_DATE) DAY,#지난 주 월요일
			CURRENT_DATE + INTERVAL -1 - WEEKDAY(CURRENT_DATE) DAY, #지난 주 일요일
			CONVERT(AVG(avg_people), float), CONVERT(AVG(std_people), float),
			CONVERT(AVG(avg_vehicle), float), CONVERT(AVG(std_vehicle), float)
			FROM day_stats WHERE _date BETWEEN DATE_ADD(NOW(),INTERVAL -1 WEEK ) AND NOW() #오늘(매주 월요일 0시) 기준으로 지난 일주일 간

3). 매달 1일에 `day_stats` table의 한달 치 평균을 `month_stats` 테이블에 저장

 CREATE EVENT `month_stats`
    ON SCHEDULE
	EVERY 1 MONTH
	STARTS LAST_DAY(NOW()) + interval 1 DAY #가장 가까운 1일부터 시작
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of week'
    DO 
	INSERT INTO month_stats(start_date, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT LAST_DAY(NOW() - interval 2 month) + interval 1 DAY, #지난 달의 1일
			CONVERT(AVG(avg_people), float), CONVERT(AVG(std_people), float),
			CONVERT(AVG(avg_vehicle), float), CONVERT(AVG(std_vehicle), float)
			FROM day_stats WHERE _date BETWEEN DATE_ADD(NOW(),INTERVAL -1 WEEK ) AND NOW()
                            

4). 매년 1월1일에 `month_stats` table의 일년 치 평균(12개의 row)을 `year_stats` 테이블에 저장

 CREATE EVENT `year_stats`
    ON SCHEDULE
	EVERY 1 YEAR
	STARTS LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH))+ interval 1 DAY #가장 가까운 1월 1일부터 시작
	ON COMPLETION NOT PRESERVE ENABLE
    COMMENT 'average and reference value of year'
    DO 
	INSERT INTO year_stats(_year, avg_people, std_people, avg_vehicle, std_vehicle) 
		SELECT YEAR(CURDATE()-interval 1 DAY), # 지난 년도
			CONVERT(AVG(avg_people), float), CONVERT(AVG(std_people), float),
			CONVERT(AVG(avg_vehicle), float), CONVERT(AVG(std_vehicle), float)
			FROM month_stats where start_date BETWEEN DATE_ADD(NOW(),INTERVAL -1 YEAR ) AND NOW() #지난 일년

2. 이상값 탐지 (MySQL Trigger)

DELIMITER //
CREATE TRIGGER detection 
	AFTER INSERT 
    ON test 
	FOR EACH ROW
BEGIN

# 기준 값 설정
	DECLARE people_ref_val, vehicle_ref_val FLOAT DEFAULT NULL;
    
	IF (SELECT COUNT(_id) FROM year_stats) >=1 THEN 	#년간 데이터가 있을 때
		SET people_ref_val = (SELECT AVG(std_people) FROM year_stats); #N년의 평균값으로 함
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM year_stats);
        
	ELSE IF (SELECT COUNT(_id) FROM month_stats) >=1 THEN
		SET people_ref_val = (SELECT AVG(std_people) FROM month_stats);
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM month_stats);
        
	ELSE IF (SELECT COUNT(_id) FROM week_stats) >=1 THEN
		SET people_ref_val = (SELECT AVG(std_people) FROM week_stats);
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM week_stats);
			
	ELSE IF (SELECT COUNT(_id) FROM day_stats) >=1 THEN
		SET people_ref_val = (SELECT AVG(std_people) FROM day_stats);
		SET vehicle_ref_val = (SELECT AVG(std_vehicle) FROM day_stats);
				END IF;
			END IF;
		END IF;
	END IF;

	# 설정된 기준 값으로 이상값 탐지
    IF  NEW.people > (SELECT @people_ref_val) THEN
		IF  NEW.vehicle > (SELECT @vehicle_ref_val) THEN
			INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier People & Vehicle');
		ELSE 
			INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier People');
		END IF;
	ELSEIF NEW.vehicle > (SELECT @vehicle_ref_val) THEN
		INSERT INTO backup VALUES(NEW.id, NEW.start_date ,NEW.end_date, NEW.people, NEW.vehicle, 'Outlier Vehicle');
    END IF;
    
   
END //
DELIMITER ;

3. 실제 데이터 처리 방법

3. 영상팀에서 호출할 수 있는 API 생성 – 사용 X

by. 태훈

수동으로 입력받는 페이지를 만들었습니다.
token을 통한 보안 인증 절차를 추가하였습니다.

Share on

Twitter Facebook LinkedIn

CCTV_ver_4.1. 데이터 분석 시스템

과제 목표

1. 리눅스 서버 환경 구축

리눅스 서버에 mysql설치 및 접속설정

Node.js 설치

MySQL table 생성

타임존 설정 및 확인

2. 데이터 베이스 분석 시스템

1. 데이터베이스 분석 테이블 생성 및 이벤트 스케줄러 생성

- 데이터베이스 테이블 생성

- MySQl Event Scheduler

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 `day_stats` 테이블에 저장

2). 매주 월요일에 `day_stats` table의 일주일 치(7개의 row) 평균을 `week_stats` 테이블에 저장

3). 매달 1일에 `day_stats` table의 한달 치 평균을 `month_stats` 테이블에 저장

4). 매년 1월1일에 `month_stats` table의 일년 치 평균(12개의 row)을 `year_stats` 테이블에 저장

2. 이상값 탐지 (MySQL Trigger)

3. 실제 데이터 처리 방법

3. 영상팀에서 호출할 수 있는 API 생성 – 사용 X

Share on

Leave a comment

You may also enjoy

NAS 서버에서 DELL 서버로 주기적으로 비디오 전송

실제 데이터 페이지 제작

MySQL Query 모음

그누보드 게시판 적용

과제 목표

1. 리눅스 서버 환경 구축

리눅스 서버에 mysql설치 및 접속설정

Node.js 설치

MySQL table 생성

타임존 설정 및 확인

2. 데이터 베이스 분석 시스템

1. 데이터베이스 분석 테이블 생성 및 이벤트 스케줄러 생성

- 데이터베이스 테이블 생성

- MySQl Event Scheduler

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 day_stats 테이블에 저장

2). 매주 월요일에 day_stats table의 일주일 치(7개의 row) 평균을 week_stats 테이블에 저장

3). 매달 1일에 day_stats table의 한달 치 평균을 month_stats 테이블에 저장

4). 매년 1월1일에 month_stats table의 일년 치 평균(12개의 row)을 year_stats 테이블에 저장

2. 이상값 탐지 (MySQL Trigger)

3. 실제 데이터 처리 방법

3. 영상팀에서 호출할 수 있는 API 생성 – 사용 X

Share on

Leave a comment

You may also enjoy

NAS 서버에서 DELL 서버로 주기적으로 비디오 전송

실제 데이터 페이지 제작

MySQL Query 모음

그누보드 게시판 적용

1). 매일 00:00:00 에 하루치 데이터 평균과 기준값을 `day_stats` 테이블에 저장

2). 매주 월요일에 `day_stats` table의 일주일 치(7개의 row) 평균을 `week_stats` 테이블에 저장

3). 매달 1일에 `day_stats` table의 한달 치 평균을 `month_stats` 테이블에 저장

4). 매년 1월1일에 `month_stats` table의 일년 치 평균(12개의 row)을 `year_stats` 테이블에 저장