서울시도보네트워크 데이터 구축

[anpaul0615]

https://data.seoul.go.kr/dataList/OA-21208/S/1/datasetView.do

• [x] 원천데이터 다운로드 및 원시자료형태 확인
• [x] 도보 노드/링크 테이블 설계
• [x] ETL
• [x] EDA
• [ ] 누락데이터 확인 및 보간

[anpaul0615]

원천데이터 다운로드 후 csv파일 확인

[anpaul0615]

노드링크 테이블 설계

---

mysql

-- auto-generated definition
create table node_data
(
    geom point not null comment '노드 GEOM',
    node_id int not null comment '노드 ID'
        primary key,
    node_code int not null comment '노드 유형 코드 (0:일반노드, 1:지하철출입구, 2:버스정류장, 3:지하보도출입구)',
    bjd_cd varchar(10) not null comment '법정동코드',
    sgg_nm varchar(255) not null comment '시군구명',
    emd_nm varchar(255) not null comment '읍면동명',
    tp_sw tinyint(1) default 0 null comment '육교',
    tp_cw tinyint(1) default 0 null comment '횡단보도'
);

create index node_data_bjd_cd_index
    on node_data (bjd_cd);

create index node_wkt
    on node_data (geom(26));

-- auto-generated definition
create table link_data
(
    geom linestring not null comment '링크 GEOM',
    link_id int not null comment '링크 ID'
        primary key,
    link_code int not null comment '링크 유형 코드 (bit-flag: 보행자/차량/자전거/PM)',
    strt_node_id int not null comment '시작노드 ID',
    end_node_id int not null comment '종료노드 ID',
    link_len decimal(10,4) null comment '링크 길이',
    bjd_cd varchar(10) not null comment '법정동코드',
    sgg_nm varchar(255) not null comment '시군구명',
    emd_nm varchar(255) not null comment '읍면동명',
    tp_hw tinyint(1) default 0 null comment '고가도로',
    tp_uw tinyint(1) default 0 null comment '지하철네트워크',
    tp_br tinyint(1) default 0 null comment '교량',
    tp_tn tinyint(1) default 0 null comment '터널',
    tp_sw tinyint(1) default 0 null comment '육교',
    tp_cw tinyint(1) default 0 null comment '횡단보도',
    tp_pk tinyint(1) default 0 null comment '공원,녹지',
    tp_in tinyint(1) default 0 null comment '건물내'
);

create index link_data_bjd_cd_index
    on link_data (bjd_cd);

create index link_data_end_node_id_strt_node_id_index
    on link_data (end_node_id, strt_node_id);

create index link_data_strt_node_id_end_node_id_index
    on link_data (strt_node_id, end_node_id);

create index link_wkt
    on link_data (geom(26));

---

postgresql

DROP TABLE IF EXISTS zbdraw.temp.pedestrian_node;
CREATE TABLE zbdraw.temp.pedestrian_node
(
    geom GEOMETRY(Point, 4326) NOT NULL,
    node_id INT PRIMARY KEY,
    node_code INT NOT NULL,
    bjd_cd VARCHAR(10) NOT NULL,
    sgg_nm VARCHAR(255) NOT NULL,
    emd_nm VARCHAR(255) NOT NULL,
    tp_sw BOOLEAN DEFAULT FALSE,
    tp_cw BOOLEAN DEFAULT FALSE
);

COMMENT ON COLUMN zbdraw.temp.pedestrian_node.geom IS '노드 GEOM';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.node_id IS '노드 ID';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.node_code IS '노드 유형 코드 (0:일반노드, 1:지하철출입구, 2:버스정류장, 3:지하보도출입구)';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.bjd_cd IS '법정동코드';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.sgg_nm IS '시군구명';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.emd_nm IS '읍면동명';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.tp_sw IS '육교';
COMMENT ON COLUMN zbdraw.temp.pedestrian_node.tp_cw IS '횡단보도';

CREATE INDEX idx_pedestrian_node_bjd_cd ON zbdraw.temp.pedestrian_node (bjd_cd);
CREATE INDEX idx_pedestrian_node_geom ON zbdraw.temp.pedestrian_node USING GIST (geom);

DROP TABLE IF EXISTS zbdraw.temp.pedestrian_link;
CREATE TABLE zbdraw.temp.pedestrian_link
(
    geom GEOMETRY(LineString, 4326) NOT NULL,
    link_id INT PRIMARY KEY,
    link_code INT NOT NULL,
    from_node_id INT NOT NULL,
    to_node_id INT NOT NULL,
    link_len DECIMAL(10,4),
    bjd_cd VARCHAR(10) NOT NULL,
    sgg_nm VARCHAR(255) NOT NULL,
    emd_nm VARCHAR(255) NOT NULL,
    tp_hw BOOLEAN DEFAULT FALSE,
    tp_uw BOOLEAN DEFAULT FALSE,
    tp_br BOOLEAN DEFAULT FALSE,
    tp_tn BOOLEAN DEFAULT FALSE,
    tp_sw BOOLEAN DEFAULT FALSE,
    tp_cw BOOLEAN DEFAULT FALSE,
    tp_pk BOOLEAN DEFAULT FALSE,
    tp_in BOOLEAN DEFAULT FALSE
);

COMMENT ON COLUMN zbdraw.temp.pedestrian_link.geom IS '링크 GEOM';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.link_id IS '링크 ID';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.link_code IS '링크 유형 코드 (bit-flag: 보행자/차량/자전거/PM)';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.from_node_id IS '시작노드 ID';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.to_node_id IS '종료노드 ID';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.link_len IS '링크 길이';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.bjd_cd IS '법정동코드';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.sgg_nm IS '시군구명';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.emd_nm IS '읍면동명';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_hw IS '고가도로';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_uw IS '지하철네트워크';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_br IS '교량';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_tn IS '터널';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_sw IS '육교';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_cw IS '횡단보도';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_pk IS '공원,녹지';
COMMENT ON COLUMN zbdraw.temp.pedestrian_link.tp_in IS '건물내';

CREATE INDEX idx_pedestrian_link_bjd_cd ON zbdraw.temp.pedestrian_link (bjd_cd);
CREATE INDEX idx_pedestrian_link_from_to ON zbdraw.temp.pedestrian_link (from_node_id, to_node_id);
CREATE INDEX idx_pedestrian_link_to_from ON zbdraw.temp.pedestrian_link (to_node_id, from_node_id);
CREATE INDEX idx_pedestrian_link_geom ON zbdraw.temp.pedestrian_link USING GIST (geom);

[anpaul0615]

데이터 검토

select count(*) from raw.node_data -- 212066
select count(*) from raw.link_data -- 279016

select l.*
from raw.link_data l
    left join raw.node_data n
    on n.node_id = l.strt_node_id
where n.node_id is null
-- 1793 (start node 누락건)

select l.*
from raw.link_data l
    left join raw.node_data n
    on n.node_id = l.end_node_id
where n.node_id is null
-- 2162 (end node 누락건)

select distinct node_code from raw.node_data
# +---------+
# |node_code|
# +---------+
# |0        |
# |1        |
# |2        |
# |3        |
# +---------+
select distinct link_code from raw.link_data
# +---------+
# |link_code|
# +---------+
# |0        |
# |11       |
# |100      |
# |101      |
# |111      |
# |1000     |
# |1010     |
# |1011     |
# |1100     |
# |1110     |
# |1111     |
# +---------+

select * from raw.link_data where link_code = '0'
-- 99건 (주로 공사중인 지역으로 추정)
select * from raw.link_data where bjd_cd = '1129010100'
select * from raw.link_data where bjd_cd like '11290%'
select * from raw.link_data where emd_nm = '정릉동'
select * from raw.link_data where bjd_cd in ('1129010100', '1129013300')

select * from raw.link_data where link_code = '11'
-- 6건 (주로 녹지, 다리 등)

select * from raw.link_data where link_code = '100'
-- 109건 (지형과 맞지않는 길)
select * from raw.link_data where bjd_cd in ('1117012500', '1117012300') -- (청파로22길, 차도가 보도로 표시되는 케이스)

select * from raw.link_data where link_code = '101' -- 1건 (?)
select * from raw.link_data where link_code = '111' -- 38건 (? 청계천로)
select * from raw.link_data where link_code = '1000' -- 15131건 (건물내도로 케이스가 많음)
select * from raw.link_data where link_code = '1010' -- 29건 (?)
select * from raw.link_data where link_code = '1011' -- 26975건 (? 아파트내.. 주택내...?)
select * from raw.link_data where link_code = '1100' -- 49건 (?)
select * from raw.link_data where link_code = '1110' -- 4건 (? 등산로..?)
select * from raw.link_data where link_code = '1111' -- 236575건 (? 일반도로..?)

select *, st_astext(geom) from raw.node_data where sgg_nm = '강동구' ANd emd_nm = '둔촌동'
select *, st_astext(geom) from raw.node_data where sgg_nm = '송파구' ANd emd_nm = '가락동'
select * from raw.link_data where sgg_nm = '송파구' ANd emd_nm = '가락동'

select
    st_geometryfromtext('POINT(127.10707227471924 37.497342677715395)'),
    st_geometryfromtext('POINT(127.10818228454814 37.49772786017471)'),
    ST_Distance_Sphere(
        st_geometryfromtext('POINT(127.10707227471924 37.497342677715395)'),
        st_geometryfromtext('POINT(127.10818228454814 37.49772786017471)')
    ),
    st_buffer(
        st_geometryfromtext('POINT(127.10707227471924 37.497342677715395)'),
        0.001)

select
    *
from raw.link_data
where ST_Distance_Sphere(
        st_geometryfromtext('POINT(127.10707227471924 37.497342677715395)'),
        st_centroid(geom)
   ) <= 500