Truffle

KNU 방재전문대학원
일단 뭐든 해보자고.

11 분 소요

👨‍💻표준노드링크와 PostgreSQL을 활용한 길 찾기 스크립트 작성

0. 용어 정리

0.1. 📌표준노드링크

표준노드링크는 교통정보 교환을 위한 교통정보 네트워크의 국가표준단위, 교통정보 실시간 교환에 기본이 되는 전국단위 표준 노드/링크를 통해 ITS 호환성과 상호연계 운용 효율성을 확보하고, 대국민 교통정보 제공 편의증진을 위해 국토 교통부에서 제공하는 지리정보데이터셋이다.

0.1.1. 표준노드

노드정보는 그래프데이터의 포인트, 버텍스에 해당되는 정보로서 위치에 대한 개념으로 링크를 통해 연결할 객체를 나타낸다.

0.1.2. 표준링크

링크정보는 그래프데이터의 라인,에지에 해당되는 정보로서 노드간의 연결관계를 나타낸다.

0.2. 📌PostgreSQL

PostgreSQL은 객체-관계형 데이터베이스 관리 시스템(ORDBMS)의 하나로, 객체-관계형 데이터 모델을 사용한다. 특히, 객체-관계형 데이터 모델은 객체지향 프로그래밍 언어의 객체 개념을 데이터베이스에 적용한 것이다. PostgreSQL은 객체-관계형 데이터 모델을 사용하는 데이터베이스 관리 시스템 중 가장 널리 사용되고 있다.

0.2.1. Postgis

PostGIS는 PostgreSQL 데이터베이스에 GIS 기능을 추가하는 오픈소스 확장 모듈이다. PostGIS는 공간 데이터를 저장하고, 검색하고, 조작하고, 분석하는데 사용할 수 있는 다양한 기능을 제공한다.

0.2.2. pgRouting

pgRouting은 PostGIS / PostgreSQL 지리 공간 데이터베이스를 확장하여 지리 공간 라우팅 및 기타 네트워크 분석 기능을 제공한다.

1. 개발환경 준비

개발환경구축을 위해 사용한 포스트의 링크를 참고해 Postgresql과 Postgis를 설치한다.

https://projooni.tistory.com/entry/GIS-%EA%B0%9C%EB%B0%9C%ED%99%98%EA%B2%BD-%EC%84%B8%ED%8C%85-3-PostgreSQL-PostGIS-%EC%84%A4%EC%B9%98

2. 데이터베이스 생성

2.1. 표준 노드링크 다운로드

다음 링크를 통해 표준노드링크 데이터셋을 다운로드 받는다.

https://www.its.go.kr/nodelink/nodelinkRef

2.2. 데이터베이스 생성

위의 포스트를 따라 PostgreSQL을 설치하면 같이 설치되는 pgAdmin을 통해 DB를 확인해보면 기본으로 생성된 postgres db 외에 postgis로 시작하는 이름의 db가 생성된다. (위 포스트에서는 postgis로 생성되었지만 필자는 postgis_33_sample로 생성됨) 이 db를 사용하거나 새로운 db를 생성하여 사용한다.

2.3. 표준노드링크를 데이터베이스에 적재

위의 포스트를 따라 설치한 ‘PostGIS PostGIS Bundle 3 for PostgreSQL x64 14 Shapefile and DBF Loader Exporter’ 를 실행한다.
기본적인 설치 경로로 설치했다면 다음과 같은 경로에 설치된다.

C:\Program Files\PostgreSQL\14\bin\postgisgui\shp2pgsql-gui.exe

설치 경로를 확인하고 다음과 같이 실행한다.

Connection을 위한 참고자료  
Username postgres(참고 포스트를 따라 설치했다면 기본값)
Password 설치시 등록한 비밀번호
Server Host localhost 5432(참고 포스트를 따라 설치했다면 기본값)
Database 2번에서 생성한 db 이름
  1. ‘Add File’을 사용해 1에서 다운로드 받은 표준노드링크 shp 파일 2개를 추가한다.
  2. 표준노드링크의 좌표계는 국토지리원표준 EPSG5186을 따르므로 SRID를 5186으로 설정한다.
  3. Table의 이름은 파일명을 그대로 유지한다.(moct_link, moct_node)
  4. import버튼을 눌러 DB에 로드한다.

3. SQL작성

3.1. 확장기능 설치, 테이블 검토

--확장기능이 설치되어있는지 확인
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
--테이블이 잘 로드되었는지 확인
select * from moct_link;
select * from moct_node;

3.2. 인덱스 생성

--link의 경우 select column_name from information_schema.columns  where TABLE_NAME = 'moct_link'를 통해 컬럼명 확인후 진행 이때 오류발생시 건너뛰기(geom칼럼과 같이 데이터가 큰 경우 발생)
--node의 경우 select column_name from information_schema.columns  where TABLE_NAME = 'moct_node' 를 통해 컬럼명 확인

create index moctlink_gid_idx on public.moct_link(gid);
create index moctlink_lanes_idx on public.moct_link(lanes);
create index moctlink_max_spd_idx on public.moct_link(max_spd);
create index moctlink_rest_w_idx on public.moct_link(rest_w);
create index moctlink_rest_h_idx on public.moct_link(rest_h);
create index moctlink_length_idx on public.moct_link(length);
create index moctlink_road_no_idx on public.moct_link(road_no);
create index moctlink_road_name_idx on public.moct_link(road_name);
create index moctlink_road_use_idx on public.moct_link(road_use);
create index moctlink_multi_link_idx on public.moct_link(multi_lin)k;
create index moctlink_connect_idx on public.moct_link(connect);
create index moctlink_remark_idx on public.moct_link(remark);
create index moctlink_link_id_idx on public.moct_link(link_id);
create index moctlink_f_node_idx on public.moct_link(f_node);
create index moctlink_t_node_idx on public.moct_link(t_node);
create index moctlink_rest_veh_idx on public.moct_link(rest_veh);
create index moctlink_road_rank_idx on public.moct_link(road_rank);
create index moctlink_road_type_idx on public.moct_link(road_type);

create index moctnode_gid_idx on public.moct_node(gid);
create index moctnode_geom_idx on public.moct_node(geom);
create index moctnode_node_type_idx on public.moct_node(node_type);
create index moctnode_turn_p_idx on public.moct_node(turn_p);
create index moctnode_remark_idx on public.moct_node(remark);
create index moctnode_node_name_idx on public.moct_node(node_name);
create index moctnode_node_id_idx on public.moct_node(node_id);

3.3. 최고속도로 link의 경로를 통과할때의 최소시간 산정

--min_cost산정 및 인덱스 생성
ALTER TABLE moct_link ADD COLUMN min_cost real;
UPDATE moct_link SET min_cost=(length/(max_spd::real/36)/10); 
create index moctlink_min_cost_idx on public.moct_link(min_cost);

3.4. 도로명 기준으로 geom과 id검색

--검색
select l.f_node,l.geom from moct_link as l where l.road_name like '강원대%';

--검색된 결과의 gom상 node번호를 사용하는 세션 변수 지정
set session  my.vars.START_POINT =  2250008800;
set session  my.vars.END_POINT= 2500012800;

3.5. dijkstra 최단경로 알고리즘을 통한 경로 탐색 결과를 dijkstra_result테이블에 저장

--dijkstra수행
drop table if exists dijkstra_result;
create table dijkstra_result as
select l.road_name, l.max_spd, l.length, l.min_cost, l.geom, d.path_seq, d.agg_cost
FROM moct_link AS l 
inner join (
	select edge::varchar(10) , seq, path_seq , node , cost, agg_cost
	FROM pgr_dijkstra(
		'SELECT link_id::bigint as id, 
			f_node::bigint as source, 
			t_node::bigint as target, 
			length::bigint as cost
		FROM moct_link'
		,current_setting('my.vars.START_POINT')::bigint, current_setting('my.vars.END_POINT')::bigint
	)
) as d
on l.link_id=d.edge;

3.6. 산정된 테이블을 통한 경로 세부정보 출력

--주행테이블
select * from dijkstra_result dr  order by path_seq asc;

--연장
select max(agg_cost)/1000 as "cost(km)" from dijkstra_result;

--최소 통과 시간: len을 max_speed로 주행해 통과하는 시간
select sum(min_cost)/60 as "cost(minute)" from dijkstra_result;

4. 활용예시

-다음과 같이 준비된 쿼리를 가지고 Django를 활용해 기초적인 길찾기 어플리케이션을 구현하였다. -교통량정보가 반영되지않기에 네이버지도와 비교했을때는 6분정도의 차이가 있음을 확인하였다.

댓글남기기

참고