九州大学伊都キャンパス巡回バス地図

関連する外部ページhttps://sites.google.com/site/kztakemoto/r-seminar-on-igraph---supplementary-information

前準備

使用するソフトウェア

あらかじめ決めておく事項

このページでは,データベースの作成を行いますので, 作成するデータベースのデータベース名を決めておくこと. このページでは,次のように書く.

CSV ファイル

ここで作成する CSV ファイル

nodes データ (node_id, lat, lon, classification, feature, name)

◆ /tmp/nodes.csv を生成する bash プログラム

#!/bin/bash
cat >/tmp/nodes.csv <<-DATA
node_id, lat, lon, classification, feature, name
1,33.59828875,130.2261751,highway,unclassified,""
2,33.59871117,130.2250575,highway,unclassified,""
3,33.59844209,130.2242221,highway,unclassified,""
4,33.59811244,130.2235519,highway,unclassified,""
5,33.59747289,130.2224231,highway,unclassified,""
6,33.59718842,130.2218779,highway,unclassified,""
7,33.59699096,130.2211885,highway,unclassified,""
8,33.59689391,130.2204669,highway,unclassified,""
9,33.59664291,130.2187673,highway,unclassified,""
10,33.59572591,130.217244,highway,unclassified,""
11,33.5942199,130.2160415,highway,unclassified,""
12,33.59378483,130.2157047,highway,unclassified,""
13,33.59321589,130.2151275,highway,unclassified,""
14,33.59294815,130.2140211,highway,unclassified,""
15,33.59311549,130.2132034,highway,unclassified,""
16,33.59542471,130.212081,highway,unclassified,""
17,33.59637517,130.2107822,highway,unclassified,""
18,33.59725535,130.2100607,highway,unclassified,""
19,33.5968002,130.2089062,highway,unclassified,""
20,33.5988852,130.2097079,highway,unclassified,""
21,33.59855923,130.2080243,highway,unclassified,""
22,33.59831793,130.2080564,highway,unclassified,""
23,33.59646553,130.222375,highway,unclassified,""
24,33.59588321,130.2225049,highway,unclassified,""
25,33.59546822,130.2214931,highway,unclassified,""
26,33.59503984,130.2215572,highway,unclassified,""
27,33.59475537,130.2208678,highway,unclassified,""
28,33.59438723,130.2205792,highway,unclassified,""
29,33.59458803,130.219569,highway,unclassified,""
30,33.59435376,130.2189276,highway,unclassified,""
31,33.59418643,130.2184947,highway,unclassified,""
32,33.59391869,130.2183825,highway,unclassified,""
33,33.59371789,130.2176128,highway,unclassified,""
34,33.59311549,130.2172761,highway,unclassified,""
35,33.59328282,130.2165064,highway,unclassified,""
36,33.59308202,130.216314,highway,unclassified,""
37,33.59284775,130.2162018,highway,unclassified,""
38,33.59278082,130.2159132,highway,unclassified,""
39,33.59244615,130.2157047,highway,unclassified,""
40,33.59214494,130.2156727,highway,unclassified,""
41,33.59211148,130.2153039,highway,unclassified,""
42,33.59164294,130.2146785,highway,unclassified,""
43,33.59194414,130.2132675,highway,unclassified,""
44,33.59237921,130.2128025,highway,unclassified,""
45,33.59281428,130.2129308,highway,unclassified,""
46,33.59565898,130.2233066,highway,unclassified,""
47,33.5956824,130.2242735,highway,unclassified,""
48,33.59569914,130.2248074,highway,unclassified,""
49,33.59595014,130.224979,highway,unclassified,""
50,33.59622792,130.2254135,highway,unclassified,""
51,33.59665964,130.2253702,highway,unclassified,""
52,33.59688052,130.2251505,highway,unclassified,""
53,33.59693742,130.2247272,highway,unclassified,""
54,33.5962982,130.2232569,highway,unclassified,""
55,33.59609739,130.223204,highway,unclassified,""
56,33.59576607,130.223871,highway,unclassified,""
57,33.59575938,130.2241976,highway,unclassified,""
58,33.59960808,130.2231446,highway,unclassified,""
59,33.59901906,130.2237187,highway,unclassified,""
60,33.59707128,130.2254664,highway,unclassified,""
61,33.59428683,130.231242,highway,unclassified,""
62,33.59499968,130.2178373,highway,unclassified,""
63,33.59506326,130.2180137,highway,unclassified,""
64,33.5949227,130.218158,highway,unclassified,""
65,33.5948223,130.2179495,highway,unclassified,""
66,33.5949227,130.2178854,highway,unclassified,""
67,33.59551507,130.2232874,building,university,""
68,33.59546822,130.2240179,building,university,""
69,33.59498964,130.2238774,building,university,""
70,33.59514693,130.2232425,building,university,""
71,33.59643876,130.2226877,building,university,""
72,33.59618106,130.222925,building,university,""
73,33.5963919,130.2234637,building,university,""
74,33.59668641,130.2232344,building,university,""
75,33.59388523,130.2152718,building,university,""
76,33.59415296,130.2155604,building,university,""
77,33.59405256,130.2157528,building,university,""
78,33.59375136,130.2154802,building,university,""
79,33.59328282,130.2168592,building,university,""
80,33.59361749,130.217212,building,university,""
81,33.59348362,130.2173563,building,university,""
82,33.59318242,130.2170837,building,university,""
83,33.59708467,130.2203547,building,university,""
84,33.5969508,130.2193124,building,university,""
85,33.59717503,130.2192323,building,university,""
86,33.59733567,130.2203386,building,university,""
DATA

way_nodes データ (way_id, seq, node_id)

◆ /tmp/way_nodes.csv を生成する bash プログラム

#!/bin/bash
cat >/tmp/way_nodes.csv <<-DATA
way_id, seq, node_id
100,1,23
100,2,24
100,3,46
100,4,47
100,5,48
100,6,49
100,7,50
100,8,51
100,9,52
100,10,53
100,11,54
100,12,55
100,13,56
100,14,57
100,15,47
100,16,46
100,17,24
100,18,25
100,19,26
100,20,27
100,21,28
100,22,29
100,23,30
100,24,31
100,25,32
100,26,33
100,27,34
100,28,35
100,29,36
100,30,37
100,31,38
100,32,39
100,33,40
100,34,41
100,35,42
100,36,43
100,37,44
100,38,45
100,39,14
100,40,15
100,41,16
100,42,17
100,43,18
100,44,19
100,45,18
100,46,20
100,47,21
100,48,22
100,49,21
100,50,20
100,51,18
100,52,19
100,53,18
100,54,17
100,55,16
100,56,15
100,57,14
100,58,13
100,59,12
100,60,11
100,61,10
100,62,62
100,63,63
100,64,64
100,65,65
100,66,66
100,67,62
100,68,10
100,69,9
100,70,8
100,71,7
100,72,6
100,73,5
100,74,4
100,75,3
100,76,2
100,77,1
100,78,2
100,79,3
100,80,60
100,81,61
100,82,60
100,83,3
100,84,4
100,85,5
100,86,6
100,87,23
1,1,1
1,2,2
1,3,3
1,4,4
1,5,5
1,6,6
1,7,7
1,8,8
1,9,9
1,10,10
1,11,11
1,12,12
1,13,13
1,14,14
1,15,15
1,16,16
1,17,17
1,18,18
1,19,19
2,1,18
2,2,20
2,3,21
2,4,22
3,1,6
3,2,23
3,3,24
3,4,25
3,5,26
3,6,27
3,7,28
3,8,29
3,9,30
3,10,31
3,11,32
3,12,33
3,13,34
3,14,35
3,15,36
3,16,37
3,17,38
3,18,39
3,19,40
3,20,41
3,21,42
3,22,43
3,23,44
3,24,45
3,25,14
4,1,35
4,2,12
5,1,24
5,2,46
5,3,47
5,4,48
5,5,49
5,6,50
5,7,51
5,8,52
5,9,53
5,10,54
5,11,55
5,12,56
5,13,57
5,14,47
6,1,58
6,2,59
6,3,3
6,4,60
6,5,61
7,1,10
7,2,62
7,3,63
7,4,64
7,5,65
7,6,66
7,7,62
8,1,67
8,2,68
8,3,69
8,4,70
8,5,67
9,1,71
9,2,72
9,3,73
9,4,74
9,5,71
10,1,75
10,2,76
10,3,77
10,4,78
10,5,75
11,1,79
11,2,80
11,3,81
11,4,82
11,5,79
12,1,83
12,2,84
12,3,85
12,4,86
12,5,83
DATA

ways データ (way_id, classification, feature, name)

◆ /tmp/ways.csv を生成する bash プログラム

#!/bin/bash
cat >/tmp/ways.csv <<-DATA
way_id, classification, feature, name 
1,highway,unclassified,""
2,highway,unclassified,""
3,highway,unclassified,""
4,highway,unclassified,""
5,highway,unclassified,""
6,highway,unclassified,""
7,highway,unclassified,""
8,building,university,""
9,building,university,""
10,building,university,""
11,building,university,""
12,building,university,""
100,route,bus,""
DATA

CSV ファイルの表示 (View OSM CSV files)

リレーショナルデータベースの生成

テーブル定義

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
drop table nodes; 
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
#
cat >/tmp/a.$$.sql <<-SQL
create table nodes (
  node_id        integer  PRIMARY KEY autoincrement not null, 
  lat            real  not null, 
  lon            real  not null, 
  classification text, 
  feature        text, 
  name           text,  
  degree         integer 
);  
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb

#
cat >/tmp/a.$$.sql <<-SQL
drop table way_nodes; 
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
#
cat >/tmp/a.$$.sql <<-SQL
create table way_nodes (
  way_id         integer, 
  seq            integer, 
  node_id        integer
);  
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb

#
cat >/tmp/a.$$.sql <<-SQL
drop table ways; 
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
#
cat >/tmp/a.$$.sql <<-SQL
create table ways (
  way_id         integer  PRIMARY KEY autoincrement not null, 
  classification text, 
  feature        text, 
  name           text, 
  is_open        integer
);  
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb

#
cat >/tmp/a.$$.sql <<-SQL
drop table edges; 
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
#
cat >/tmp/a.$$.sql <<-SQL
create table edges (
  edge_id integer primary key autoincrement, 
  node1 integer, 
  node2 integer, 
  gdist real
);  
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb

テーブルの作成