[oracle] 버스 노선 추가

1. EXCEL파일로 온 데이터 확인한다.

 아래의 순서가 맞아야 한다.

 col1/col2/col3/col4/col5/col6/col7/col8/col9/col10/col1/col2/col13/col14

 버스ID/정류장ID/원정류장ID/다음정류장ID/지역코드/버스번호/순번/정류장명/거리/소요시간/위도/경도/수정여부

 

 

2. TMP_UPLOAD_COL13_TBL 테이블에 EXCEL파일 데이터 INSERT한다.

 원래 TMP_UPLOAD_COL13_TBL 테이블에 데이터가 있으면 삭제한 후, INSERT 한다.

 # 데이터가 제대로 들어오지 않으면 .csv로 파일 변경 후, INSERT 하도록 한다.

 

이전의 SP_UPDATE_BUS_DATA_ZP 로 테스트를 해본다.

* SP_UPDATE_BUS_DATA와 SP_UPDATE_BUS_DATA_ZP 차이

BUS_LINE_INFO, ZP_BUS_LINE_INFO 테이블에 INSERT.

로직은 같고 대상 테이블만 다르다.

ZP_BUS_LINE_INFO 테이블은 테스트용이다.

 

 

3. INSERT한 테이블에 데이터를 확인한 후, SP_UPDATE_BUS_DATA 프로시져를 실행한다.

 

 

4. 들어 온 데이터를 확인하고, 시퀀스 넘버순으로 정렬한다.

// 데이터 확인

SELECT LI.BUSID, LI.SEQNO,  LI.STATNID, LI.NXTSTATNID, LI.ORISTATNNM, SM.LONGITUDE, SM.LATITUDE, SM.ORILONGITUDE, SM.ORILATITUDE, SM.PROC_KIND

FROM ZP_BUS_LINE_INFO LI, ZP_BUS_STATION_MST SM

WHERE SM.STATNID = LI.STATNID AND LI.BUSID=?

ORDER BY LI.SEQNO;

 

// 데이터 확인 - 시퀀스 넘버순으로 정렬

SELECT *

FROM ZP_BUS_LINE_INFO

WHERE BUSID=버스ID

ORDER BY SEQNO;

 

// ZP_BUS_LINE_INFO 테이블 지우고 다시 생성

DROP TABLE ZP_BUS_LINE_INFO;

CREATE TABLE ZP_BUS_LINE_INFO AS SELECT * FROM BUS_LINE_INFO;

SELECT * FROM ZP_BUS_LINE_INFO;

 

// ZP_BUS_STATION_MST 테이블 지우고 다시 생성

DROP TABLE ZP_BUS_STATION_MST;

CREATE TABLE ZP_BUS_STATION_MST AS SELECT * FROM BUS_STATION_MST;

SELECT * FROM ZP_BUS_STATION_MST;

 

// 프로시저 실행

EXEC SP_UPDATE_BUS_DATA;

 

// 시퀀스 넘버순으로 잘 들어왔는 지 확인

SELECT BUSID, BLI.STATNID, NXTSTATNID, BLI.AREACD,  LEVEL

FROM ZP_BUS_LINE_INFO BLi

WHERE  BUSID = 버스ID

        AND      BLI.AREACD = 지역코드

        CONNECT BY BLI.STATNID =   PRIOR   BLI.NXTSTATNID

        AND BUSID = 버스ID

        AND BLI.AREACD = 지역코드

        AND LEVEL <= ( SELECT COUNT(*) FROM ZP_BUS_LINE_INFO

                              WHERE BUSID = 버스ID

                              AND AREACD = 지역코드)

        START WITH BUSID = 버스ID AND SEQNO = 1     ;

 

// 시퀀스 넘버순으로 실행

EXEC SP_MAKE_SEQNO_BUS_LINE_INFO(버스ID, 지역코드);

 

//  DRIVEDIST 계산해서 저장

UPDATE ZP_BUS_LINE_INFO

SET DRIVEDIST = FN_GET_DRIVEDIST(정류장ID, 다음정류장ID)

WHERE BUSID = 버스ID

AND DRIVEDIST = 0;

 

// EXPDRIVETIME 계산해서 저장

UPDATE ZP_BUS_LINE_INFO

SET EXPDRIVETIME = ROUND(DRIVEDIST/3*10, 2)

WHERE BUSID = 버스ID;

 

// EXPDRIVETIME  계산해서 저장

UPDATE ZP_BUS_LINE_INFO

SET EXPDRIVETIME = ROUND(DRIVEDIST/3*10, 2)

WHERE PROC_KIND IN ('1', '2', '3');

 

 

5. 확인된 데이터가 맞으면 실제 실행 테이블에 저장한다.

 

 

6. 21번 서버에서 ctl를 추가한다.

 

/home/oracle/upload/fastbusline에서 savedata_?.txt 에 맞게 fastbusline_?.ctl를 추가한다.

안의 내용도 수정해야한다.

생성하는 쿼리에 문장 중에 fastbusline_1.ctl 데이터 빼고 replace는 append로 수정한다.

(나머지 데이터들은 바꾸는 게 아니라 추가해주는 것이다.)

savedata_?.txt 가 맞게 있으면 생성해주지 않아도 된다.

 

 

7. putty에서 21번 서버의 oracle로 접속하여

생성한 ctl 데이터가 있는 지 확인한 후,

 

cat fastbusline.sh

./fastbusline.sh

 

를 실행한다.

 

 

8. sql를 만들어 sqlite에 저장한다.

버스/정류장/지하철 sql을 받아서 sqlite에 import

정류장은 데이터가 많으니 toad에서 직접 다운받는 것이 시간이 덜 걸린다.

 

encoding: utf-8/character: pipe/ascII value:124 맞춰서 bus_station.txt 다운받기

 

db 추출에 사용되는 쿼리 참조는

/home/st/application/controllers/admin/db_download.php

/home/st/application/models/admin/m_db_download.php

 

**bus_station drop/create

  DROP TABLE BUS_STATION_MST;

  CREATE TABLE BUS_STATION_MST

  (

    STATNID       NUMERIC,

    STATNNO       VARCHAR,

    STATNNM       VARCHAR,

    LONGITUDE     VARCHAR,

    LATITUDE      VARCHAR,

    LOCATIONX     NUMERIC,

    LOCATIONY     NUMERIC,

    AREACD        CHAR,

    REMARK        VARCHAR,

    ORILONGITUDE  VARCHAR,

    ORILATITUDE   VARCHAR,

    STATION_BUS   TEXT   

    );

 

SELECT

   B.STATNID, B.STATNNO, B.STATNNM,

   B.LONGITUDE, B.LATITUDE, B.LOCATIONX,

   B.LOCATIONY, B.AREACD, B.REMARK,

   B.ORILONGITUDE, B.ORILATITUDE,

   GET_BUSNM_IN_STATION(B.STATNID) BUS_STATATION

  FROM BUS_STATION_MST B

  ORDER BY B.STATNID ASC;

 

select query에서 나온 데이터가 bus_bus이다.

**bus_bus drop/create

DROP TABLE BUS_BUS_MST;

  CREATE TABLE BUS_BUS_MST

  (

    BUSID                NUMERIC,

    BELONGAREACD         CHAR,

    GOVRNOFFICCD         VARCHAR,

    GOVRNOFFICNM         VARCHAR,

    COMPANYID            NUMERIC,

    BUSNO                VARCHAR,

    BUSNM                VARCHAR,

    BUSKINDCD            VARCHAR,

    STARTSTATNID         NUMERIC,

    ENDSTATNID           NUMERIC,

    STARTSTATNNM         VARCHAR,

    ENDSTATNNM           VARCHAR,

    APPROVEDIST          NUMERIC,

    DRIVTIME             NUMERIC,

    COMMTHOURALLOC       NUMERIC,

    NORMDAYALLOC         NUMERIC,

    UPWARDFIRSTBUS_WK    VARCHAR,

    UPWARDLASTBUS_WK     VARCHAR,

    UPWARDFIRSTBUS_ST    VARCHAR,

    UPWARDLASTBUS_ST     VARCHAR,

    UPWARDFIRSTBUS_SN    VARCHAR,

    UPWARDLASTBUS_SN     VARCHAR,

    DNWARDFIRSTBUS       VARCHAR,

    DNWARDLASTBUS        VARCHAR,

    DRIVINTERVAL_WK_MIN  NUMERIC,

    DRIVINTERVAL_WK_MAX  NUMERIC,

    DRIVINTERVAL_ST_MIN  NUMERIC,

    DRIVINTERVAL_ST_MAX  NUMERIC,

    DRIVINTERVAL_SN_MIN  NUMERIC,

    DRIVINTERVAL_SN_MAX  NUMERIC,

    REMARK               VARCHAR,

    LINESTARTSTATNID     NUMERIC,

    LINEENDSTATNID       NUMERIC,

    OPERATEYN            CHAR,

    REGISTERDT           VARCHAR,

    MODIFYDT             VARCHAR

  );

 

SELECT

     B.BUSID, B.BELONGAREACD, B.GOVRNOFFICCD,

     B.GOVRNOFFICNM, B.COMPANYID, B.BUSNO,

     B.BUSNM, B.BUSKINDCD, B.STARTSTATNID,

     B.ENDSTATNID, B.STARTSTATNNM, B.ENDSTATNNM,

     B.APPROVEDIST, B.DRIVTIME, B.COMMTHOURALLOC,

     B.NORMDAYALLOC, B.UPWARDFIRSTBUS_WK, B.UPWARDLASTBUS_WK,

     B.UPWARDFIRSTBUS_ST, B.UPWARDLASTBUS_ST, B.UPWARDFIRSTBUS_SN,

     B.UPWARDLASTBUS_SN, B.DNWARDFIRSTBUS, B.DNWARDLASTBUS,

     B.DRIVINTERVAL_WK_MIN, B.DRIVINTERVAL_WK_MAX, B.DRIVINTERVAL_ST_MIN,

     B.DRIVINTERVAL_ST_MAX, B.DRIVINTERVAL_SN_MIN, B.DRIVINTERVAL_SN_MAX,

     B.REMARK, B.LINESTARTSTATNID, B.LINEENDSTATNID,

     B.OPERATEYN, B.REGISTERDT, B.MODIFYDT

  FROM BUS_BUS_MST B

  ORDER BY B.BUSID ASC;

 

 

9. 들어온 데이터를 확인한다.

 - sqlite 확인

 - 길찾기 데이터 확인

 

 

10. 파일의 사이즈를 확인한다.

사이즈가 너무 크면 FIREFOX - SQLITE MANAGER에서 DATABASE - COMPACT MANAGER 클릭하여 다시 저장한다.