福岡市バス停データ

使用するデータ：福岡市のバス停データ（CSV，XML ファイル形式）（自作のデータ）（2013年10月16日）

利用条件: クリエイティブコモンズ BY NC SA

次で公開中: https://www.kkaneko.jp/sample/nnrmap-2013-10-16/index.html

次を行う

• SQLite3 に投入する（テーブル定義，CSV データを読み込んで SQLite3 に投入．その後確認表示）

前準備

使用するソフトウェア

• R システムのインストールが済んでいること
• SQLite 3 のインストール: SQLite 3インストール，データベース作成，テーブル定義，レコード挿入（Windows 上）別ページ »で説明

テーブルの作成

1. テーブル定義
   • bstop(seq, x, y, busstop, yomi)

   ◆ bash プログラム

   #!/bin/bash
   
   rm -f /tmp/bstopdb
   #
   cat >/tmp/a.$$.sql <<-SQL
   drop table bstop; 
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   #
   cat >/tmp/a.$$.sql <<-SQL
   drop table T; 
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   #
   cat >/tmp/a.$$.sql <<-SQL
   create table bstop (
   chiiki   text, 
   seq      integer, 
   x1        real, 
   y1        real, 
   x2        real, 
   y2        real, 
   x3        real, 
   y3        real, 
   busstop text, 
   yomi     text ); 
   create table T (
   seq      integer, 
   x        real, 
   y        real, 
   busstop text, 
   yomi     text ); 
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   

   
2. テーブルの作成

   ◆ bash プログラム

   #!/bin/bash
   
   # １．城南区
   cat >/tmp/a.$$.sql <<-SQL
   create table T1 as select * from T; 
   create table T2 as select * from T; 
   create table T3 as select * from T; 
   .mode csv
   .import /tmp/01.$$.csv T1
   .import /tmp/02.$$.csv T2
   .import /tmp/03.$$.csv T3
   insert into bstop select '福岡市城南区' as chiiki, T1.seq as seq, T1.x as x1, T1.y as x2, T2.x as x2, T2.y as y2, T3.x as x3, T3.y as y3, T1.busstop, T1.yomi
     from T1, T2, T3
     where T1.seq = T2.seq
       AND T1.seq = T3.seq;
   vacuum; 
   SQL
   #
   nkf -w jounanku/jyonanku-01.csv | tail -n +2 > /tmp/01.$$.csv
   nkf -w jounanku/jyonanku-02.csv | tail -n +2 > /tmp/02.$$.csv
   nkf -w jounanku/jyonanku-03.csv | tail -n +2 > /tmp/03.$$.csv
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   
   # ２．西区・早良区(あとは同じ）
   cat >/tmp/a.$$.sql <<-SQL
   drop table T1;
   drop table T2;
   drop table T3;
   create table T1 as select * from T; 
   create table T2 as select * from T; 
   create table T3 as select * from T; 
   .mode csv
   .import /tmp/01.$$.csv T1
   .import /tmp/02.$$.csv T2
   .import /tmp/03.$$.csv T3
   insert into bstop select '福岡市西区，福岡市早良区' as chiiki, T1.seq as seq, T1.x as x1, T1.y as x2, T2.x as x2, T2.y as y2, T3.x as x3, T3.y as y3, T1.busstop, T1.yomi
     from T1, T2, T3
     where T1.seq = T2.seq
       AND T1.seq = T3.seq;
   vacuum; 
   SQL
   #
   nkf -w nishiku_sawaraku/nishiku-01.csv | tail -n +2 > /tmp/01.$$.csv
   nkf -w nishiku_sawaraku/nishiku-02.csv | tail -n +2 > /tmp/02.$$.csv
   nkf -w nishiku_sawaraku/nishiku-03.csv | tail -n +2 > /tmp/03.$$.csv
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   
   # ３．東区(あとは同じ）
   cat >/tmp/a.$$.sql <<-SQL
   drop table T1;
   drop table T2;
   drop table T3;
   create table T1 as select * from T; 
   create table T2 as select * from T; 
   create table T3 as select * from T; 
   .mode csv
   .import /tmp/01.$$.csv T1
   .import /tmp/02.$$.csv T2
   .import /tmp/03.$$.csv T3
   insert into bstop select '福岡市東区' as chiiki, T1.seq as seq, T1.x as x1, T1.y as x2, T2.x as x2, T2.y as y2, T3.x as x3, T3.y as y3, T1.busstop, T1.yomi
     from T1, T2, T3
     where T1.seq = T2.seq
       AND T1.seq = T3.seq;
   vacuum; 
   SQL
   #
   nkf -w higashiku/higashiku-01.csv | tail -n +2 > /tmp/01.$$.csv
   nkf -w higashiku/higashiku-02.csv | tail -n +2 > /tmp/02.$$.csv
   nkf -w higashiku/higashiku-03.csv | tail -n +2 > /tmp/03.$$.csv
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   
   # ４．南区・中央区(あとは同じ）
   cat >/tmp/a.$$.sql <<-SQL
   drop table T1;
   drop table T2;
   drop table T3;
   create table T1 as select * from T; 
   create table T2 as select * from T; 
   create table T3 as select * from T; 
   .mode csv
   .import /tmp/01.$$.csv T1
   .import /tmp/02.$$.csv T2
   .import /tmp/03.$$.csv T3
   insert into bstop select '福岡市南区，福岡市中央区' as chiiki, T1.seq as seq, T1.x as x1, T1.y as x2, T2.x as x2, T2.y as y2, T3.x as x3, T3.y as y3, T1.busstop, T1.yomi
     from T1, T2, T3
     where T1.seq = T2.seq
       AND T1.seq = T3.seq;
   vacuum; 
   SQL
   #
   nkf -w minamiku_chuouku/tyuouku-01.csv | tail -n +2 > /tmp/01.$$.csv
   nkf -w minamiku_chuouku/tyuouku-02.csv | tail -n +2 > /tmp/02.$$.csv
   nkf -w minamiku_chuouku/tyuouku-03.csv | tail -n +2 > /tmp/03.$$.csv
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   
   # ５．博多区・志摩町(あとは同じ）
   cat >/tmp/a.$$.sql <<-SQL
   drop table T1;
   drop table T2;
   drop table T3;
   create table T1 as select * from T; 
   create table T2 as select * from T; 
   create table T3 as select * from T; 
   .mode csv
   .import /tmp/01.$$.csv T1
   .import /tmp/02.$$.csv T2
   .import /tmp/03.$$.csv T3
   insert into bstop select '福岡市博多区，志摩町' as chiiki, T1.seq as seq, T1.x as x1, T1.y as x2, T2.x as x2, T2.y as y2, T3.x as x3, T3.y as y3, T1.busstop, T1.yomi
     from T1, T2, T3
     where T1.seq = T2.seq
       AND T1.seq = T3.seq;
   vacuum; 
   SQL
   #
   nkf -w hakataku_shimemachi/hatakaku-01.csv | tail -n +2 > /tmp/01.$$.csv
   nkf -w hakataku_shimemachi/hatakaku-02.csv | tail -n +2 > /tmp/02.$$.csv
   nkf -w hakataku_shimemachi/hatakaku-03.csv | tail -n +2 > /tmp/03.$$.csv
   cat /tmp/a.$$.sql | sqlite3 /tmp/bstopdb
   
   #
   echo 'select * from bstop limit 10;' | sqlite3 /tmp/bstopdb
   

   SQLiteman で確認

   
3. R で使ってみる

   #!/bin/bash
   cat >/tmp/a.$$.r <<-RCOMMAND
   library(RSQLite)
   library(sqldf)
   dbname="/tmp/bstopdb"
   driver=dbDriver("SQLite")
   conn=dbConnect(driver,dbname)
   #
   T <- dbGetQuery(conn, "SELECT * from bstop");
   #
   D <- sqldf("select x1, y1, busstop from T where chiiki='西区・早良区'")
   plot(x=D\$x1, y=D\$y1)
   Sys.sleep(100)
   RCOMMAND
   cat /tmp/a.$$.r | r 
   

   

   確認表示

   edit(X)