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

• 九州大学地図の CSV データ（路線図から手作りした自作データ）
• 関連するプログラムとして，この CSV データを SQLite3 に投入するプログラム（テーブル定義，CSV データを読み込んで SQLite3 に投入）

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

前準備

使用するソフトウェア

• R システムのインストール: R システム・バージョン 4 と RTools のインストール（Windows 上）別ページ »で説明
• SQLite 3 のインストール: SQLite 3インストール，データベース作成，テーブル定義，レコード挿入（Windows 上）別ページ »で説明

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

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

• データベース名: kyudaimapdb

  データベース名は，自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

CSV ファイル

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

• /tmp/nodes.csv : nodes データ (node_id, lat, lon, classification, feature, name)
• /tmp/way_nodes.csv : way_nodes データ (way_id, seq, node_id)
• /tmp/ways.csv : ways データ (way_id, classification, feature, name)

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)

• nodes データ (node_id, lat, lon, classification, feature, name) の緯度、経度

  ◆ R プログラム

  require(data.table)
  require(ggplot2)
  nodes <- data.table( read.csv("/tmp/nodes.csv", header=TRUE, as.is=TRUE) )
  ggplot(nodes, aes(x=lat, y=lon)) + geom_point()
  # または tkplot(x=nodes$lat, y=nodes$lon)
  

  
• way_nodes から edge に関するデータ (way_id, node1, node2)を生成するプログラム

  ◆ edge に関するデータ (way_id, node1, node2) を生成する bash プログラム

  #!/bin/bash
  cat >/tmp/a.$$.sql <<-SQL
    create table way_nodes (
  
  SQL
  cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimap.db
  #
  cat >/tmp/a.$$.sql <<-SQL
    select A.way_id, A.node_id, B.node_id 
    from way_nodes as A, way_nodes as B
    where A.way_id = B.way_id and A.seq = B.seq - 1
  SQL
  cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimap.db
  

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

テーブル定義

• nodes(node_id, lat, lon, classification, feature, name, degree)
• way_nodes(way_id, seq, node_id)
• ways(way_id, classification, feature, name, is_open)
• edges(edge_id, node1, node2, gdist)

#!/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

テーブルの作成

• テーブル ways の作成

  ◆ bash プログラム

  #!/bin/bash
  
  # /tmp/nodes.csv 
  tail -n +2 /tmp/nodes.csv | nkf -w > /tmp/nodes_uft8.csv 
  cat >/tmp/a.$$.sql <<-SQL
  create table T (
    node_id        integer  PRIMARY KEY autoincrement not null, 
    lat            real  not null, 
    lon            real  not null, 
    classification text, 
    feature        text, 
    name           text   
  );  
  .mode csv
  .import /tmp/nodes_uft8.csv T 
  insert into nodes(node_id, lat, lon, classification, feature, name) select * from T; 
  drop table T; 
  vacuum; 
  SQL
  cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
  # 
  echo 'select * from nodes limit 10;' | sqlite3 /tmp/kyudaimapdb
  

• テーブル way_nodes の作成

  ◆ bash プログラム

  #!/bin/bash
  
  # /tmp/way_nodes.csv 
  tail -n +2 /tmp/way_nodes.csv | nkf -w > /tmp/way_nodes_uft8.csv 
  cat >/tmp/a.$$.sql <<-SQL
  .mode csv
  .import /tmp/way_nodes_uft8.csv way_nodes 
  vacuum; 
  SQL
  cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
  # 
  echo 'select * from way_nodes limit 10;' | sqlite3 /tmp/kyudaimapdb
  

• テーブル ways の作成

  ◆ bash プログラム

  #!/bin/bash
  
  # /tmp/ways.csv 
  tail -n +2 /tmp/ways.csv | nkf -w > /tmp/ways_uft8.csv 
  cat >/tmp/a.$$.sql <<-SQL
  create table T (
    way_id         integer  PRIMARY KEY autoincrement not null, 
    classification text, 
    feature        text, 
    name           text 
  );  
  .mode csv
  .import /tmp/ways_uft8.csv T 
  insert into ways(way_id, classification, feature, name) select * from T; 
  drop table T; 
  vacuum; 
  SQL
  cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
  # 
  echo 'select * from ways limit 10;' | sqlite3 /tmp/kyudaimapdb
  

• テーブル edges の作成

  以下の手順で way_nodes, edges からテーブル edgesを生成

  1. way_nodes からテーブル T(node_id, node1, node2) を作成.

     テーブルTは edges を作成するための中間テーブル．テーブルTに、あとで属性gdistを追加してedgesが完成

     ◆ bash プログラム

     #!/bin/bash
     
     # /tmp/ways.csv 
     tail -n +2 /tmp/ways.csv | nkf -w > /tmp/ways_uft8.csv 
     cat >/tmp/a.$$.sql <<-SQL
     create table T (
       edge_id integer primary key autoincrement, 
       node1 integer, 
       node2 integer 
     );  
     insert into T(node1, node2)  
       select distinct A.node_id, B.node_id 
       from way_nodes as A, way_nodes as B
       where A.way_id = B.way_id and A.seq = B.seq - 1;
     vacuum; 
     SQL
     cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
     # 
     echo 'select * from T limit 10;' | sqlite3 /tmp/kyudaimapdb
     

  2. テーブル edges の属性 gdist に関する処理

     ◆ R プログラム

     require(RSQLite)
     require(geosphere)
     require(sqldf)
     drv <- dbDriver("SQLite", max.con = 1)
     conn <- dbConnect(drv, dbname="/tmp/kyudaimapdb") 
     rs <- dbSendQuery( conn, "SELECT * FROM T order by edge_id;" )
     edges <- fetch(rs, n = -1)
     # lat and lon of each edge
     rs <- dbSendQuery( conn, "select T.edge_id as edge_id, A1.lon as lon1, A1.lat as lat1, A2.lon as lon2, A2.lat as lat2 from T, nodes as A1, nodes as A2 where T.node1 = A1.node_id and T.node2 = A2.node_id order by edge_id;" )
     latlon <- fetch(rs, n = -1)
     # WGS 84, Vincety method, geodesic distance 
     edges$gdist <- distVincentyEllipsoid( sqldf("select lon1, lat1 from latlon"), sqldf("select lon2, lat2 from latlon") )
     write.csv(edges, file="/tmp/edges.csv", row.names=FALSE)
     q() 
     

     生成される /tmp/edges.csv の先頭部分

     生成される /tmp/edges.csv

     1,23,24,65.7038739521881
     2,24,46,78.4599552184638
     3,46,47,89.7854304998341
     4,47,48,49.5914604960342
     5,48,49,32.0740858824511
     6,49,50,50.752137709759
     7,50,51,48.0526457987608
     8,51,52,31.8754630737111
     9,52,53,39.7938630196082
     10,53,54,153.789821334658
     11,54,55,22.8076769154549
     12,55,56,71.9958464175374
     13,56,57,30.3241231498982
     14,57,47,11.0695227972531
     15,47,46,89.7854304998392
     16,46,24,78.4599552183069
     17,24,25,104.588490934714
     18,25,26,47.8848850837779
     19,26,27,71.3467155987657
     20,27,28,48.8352869847077
     21,28,29,96.3769355333057
     22,29,30,64.9589981155187
     23,30,31,44.2615670023388
     24,31,32,31.4696778184444
     25,32,33,74.8361655883246
     26,33,34,73.7634654636914
     27,34,35,73.8169756406262
     28,35,36,28.5478314689245
     29,36,37,27.9935517107653
     30,37,38,27.798351376489
     31,38,39,41.8623381840441
     32,39,40,33.5405074956008
     33,40,41,34.4340640522017
     34,41,42,77.9150505007364
     35,42,43,135.168632487512
     36,43,44,64.743180324783
     37,44,45,49.7036573617485
     38,45,14,102.288535013693
     39,14,15,78.1377835990947
     40,15,16,276.505543623206
     41,16,17,160.146353792369
     42,17,18,118.387186308154
     43,18,19,118.455212062789
     44,19,18,118.455212062804
     45,18,20,183.716967317859
     46,20,21,160.394600235214
     47,21,22,26.9291559303123
     48,22,21,26.9291559299656
     49,21,20,160.39460023525
     50,20,18,183.716967317632
     51,18,17,118.387186308147
     52,17,16,160.146353792485
     53,16,15,276.505543623021
     54,15,14,78.1377835990385
     55,14,13,106.906634826122
     56,13,12,82.7806067381158
     57,12,11,57.497518732198
     58,11,10,200.899234039972
     59,10,62,97.5757794427777
     60,62,63,17.8276560874584
     61,63,64,20.5537329306603
     62,64,65,22.3283145006889
     63,65,66,12.6256952683182
     64,66,62,9.63509441488442
     65,62,10,97.5757794427416
     66,10,9,174.173755247864
     67,9,8,160.192861889183
     68,8,7,67.8375426417807
     69,7,6,67.6334530134162
     70,6,5,59.6351915898455
     71,5,4,126.527621421435
     72,4,3,72.1559441200289
     73,3,2,83.0848001124146
     74,2,1,113.822698357085
     75,1,2,113.822698356889
     76,2,3,83.0848001122443
     77,3,60,190.934476780764
     78,60,61,618.688581629668
     79,61,60,618.688581629787
     80,60,3,190.934476780898
     81,3,4,72.1559441197823
     82,4,5,126.527621421512
     83,5,6,59.6351915900086
     84,6,23,92.5076716304976
     85,6,7,67.6334530134523
     86,7,8,67.8375426418179
     87,8,9,160.192861889168
     88,9,10,174.173755247745
     89,10,11,200.899234039749
     90,11,12,57.4975187322279
     91,12,13,82.780606738105
     92,13,14,106.906634826058
     93,35,12,92.9409705602022
     94,58,59,84.3063027308876
     95,59,3,79.2367316188885
     96,67,68,68.0041169015511
     97,68,69,54.6602913506111
     98,69,70,61.4600033710786
     99,70,67,41.0444233287955
     100,71,72,36.08497814543
     101,72,73,55.2002047225422
     102,73,74,38.987533357354
     103,74,71,57.701645414321
     104,75,76,39.9928718068043
     105,76,77,21.046353830033
     106,77,78,41.9085447958451
     107,78,75,24.3857517678189
     108,79,80,49.5005952873161
     109,80,81,19.9968953594562
     110,81,82,41.9086426444658
     111,82,79,23.6275240735169
     112,83,84,97.8777877174605
     113,84,85,25.9579981553192
     114,85,86,104.21943380663
     115,86,83,27.8797768797288
     

  3. テーブル edges の作成

     ◆ bash プログラム

     #!/bin/bash
     
     # /tmp/edges.csv 
     tail -n +2 /tmp/edges.csv | nkf -w > /tmp/edges_uft8.csv 
     cat >/tmp/a.$$.sql <<-SQL
     .mode csv
     .import /tmp/edges_uft8.csv edges 
     vacuum; 
     SQL
     cat /tmp/a.$$.sql | sqlite3 /tmp/kyudaimapdb
     # 
     echo 'select * from edges limit 10;' | sqlite3 /tmp/kyudaimapdb