Open Street Map のデータ

このWebページでは、Open Street Mapのデータを用いてSQLite 3のテーブルを生成します。

★作成するSQLite 3データベース: osmdb

準備

次のコマンドを使用する

Open Street Map データの必要部分の切り出し

SQLite 3 データベースの生成

使用するソフトウェア

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

この Web ページでは、データベースの作成を行いますので、作成するデータベースのデータベース論理名を決めておくこと。この Web ページでは、次のように書きます：

使用するデータベースの名前のこと『データベース論理名』と呼ぶことにする。データベース論理名は、自由に決めていいですが、半角文字（つまり英数字）を使い、スペースを含まないこと。

テーブルの作成

  1. テーブル定義 (node, way, relation)

    ★bash プログラム 

    #!/bin/bash

rm -f /tmp/osmdb
#
cat >/tmp/a.$$.sql <<-SQL
drop table osmnode;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
#
cat >/tmp/a.$$.sql <<-SQL
drop table osmway;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
#
cat >/tmp/a.$$.sql <<-SQL
drop table osmrelation;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
#
cat >/tmp/a.$$.sql <<-SQL
create table osmnode (
    id                integer not null, 
    lat               real    not null, 
    lon               real    not null, 
    version           integer not null,
    changeset         integer,  
    timestamp         datetime ); 

create table osmway (
    id                integer not null, 
    version           integer not null,
    changeset         integer,  
    user              text, 
    uid               integer, 
    timestamp         datetime ); 

create table osmrelation (
    id                integer not null, 
    version           integer not null,
    changeset         integer,  
    user              text, 
    uid               integer, 
    timestamp         datetime ); 

SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
  2. データの作成

    ★bash プログラム 

    #!/bin/bash
cat >/tmp/a.$$.sql <<-SQL
.mode csv
.import /tmp/itoshima-city_node.csv osmnode
.import /tmp/itoshima-city_way.csv osmway
.import /tmp/itoshima-city_relation.csv osmrelation
.import /tmp/fukuoka-city_node.csv osmnode
.import /tmp/fukuoka-city_way.csv osmway
.import /tmp/fukuoka-city_relation.csv osmrelation
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
# 
echo 'select * from osmnode limit 10;' | sqlite3 /tmp/osmdb
echo 'select * from osmway limit 10;' | sqlite3 /tmp/osmdb
echo 'select * from osmrelation limit 10;' | sqlite3 /tmp/osmdb
  3. (オプション) データベースの再構築

    ★bash プログラム 

    #!/bin/bash

cd /tmp
ls -la osmdb

# dump
cat >/tmp/a.$$.sql <<-SQL
.output /tmp/osmdb.sql
.dump 
.exit
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb

#
rm /tmp/osmdb

# restore 
cat >/tmp/a.$$.sql <<-SQL
.read /tmp/osmdb.sql
.exit
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
#
cd /tmp
ls -la osmdb
  4. プロット 
    #!/bin/bash
cat > /tmp/a.r <<-COMMAND
  library("RSQLite")
  drv <- dbDriver("SQLite", max.con = 1)
  conn <- dbConnect(drv, dbname="/tmp/osmdb")
  rs <- dbSendQuery( conn, "SELECT distinct lat, lon from osmnode;" )
  R <- fetch(rs, n = -1)
  png("/tmp/a.png")
  plot(R\$lat, R\$lon)
  dev.off()
COMMAND
cat /tmp/a.r | r
display /tmp/a.png
  5. 追加テーブル定義 (nodetag) 
    #!/bin/bash
cat > /tmp/a.sql <<-SQL
create table nodetag (
    nodeid            integer not null,
    lat               real    not null, 
    lon               real    not null, 
    version           integer not null,
    changeset         integer,  
    timestamp         datetime,
    k                 text not null, 
    v                 text not null );  

create table waynd (
    wayid             integer not null,
    version           integer not null,
    changeset         integer,  
    user              text, 
    uid               integer, 
    timestamp         datetime,
    ref               integer );  

create table waytag (
    wayid             integer not null,
    version           integer not null,
    changeset         integer,  
    user              text, 
    uid               integer, 
    timestamp         datetime,
    k                 text not null, 
    v                 text not null );  

create table relationmember (
    relationid        integer not null,
    version           integer not null,
    changeset         integer,  
    user              text, 
    uid               integer, 
    timestamp         datetime,
    type              text, 
    ref               integer, 
    role              text ); 

create table relationtag (
    ralationid        integer not null,
    version           integer not null,
    changeset         integer,  
    user              text, 
    uid               integer, 
    timestamp         datetime,
    k                 text not null, 
    v                 text not null );  
SQL
cat /tmp/a.sql | sqlite3 /tmp/osmdb
  6. データの挿入 
    #!/bin/bash
cat > /tmp/a.sql <<-SQL
.mode csv/japan.csv | sed 's/^\/node, //g'     > /tmp/japan_node.csv
# grep '^/way'      /tmp/japan.csv | sed 's/^\/way, //g'      > /tmp/japan_way.csv
# grep '^/relation' /tmp/japan.csv | sed 's/^\/relation, //g' > /tmp/japan_relation.csv
  7. XML 形式から CSV 形式への変換 (tag, nd, member に関する CSV ファイル) 
    .mode csv
.import /tmp/itoshima-city_node_tag.csv nodetag
.import /tmp/itoshima-city_way_nd.csv waynd
.import /tmp/itoshima-city_way_tag.csv waytag
.import /tmp/itoshima-city_relation_member.csv relationmember
.import /tmp/itoshima-city_relation_tag.csv relationtag

.import /tmp/fukuoka-city_node_tag.csv nodetag
.import /tmp/fukuoka-city_way_nd.csv waynd
.import /tmp/fukuoka-city_way_tag.csv waytag
.import /tmp/fukuoka-city_relation_member.csv relationmember
.import /tmp/fukuoka-city_relation_tag.csv relationtag
SQL
cat /tmp/a.sql | sqlite3 /tmp/osmdb
  8. プロット 
    #!/bin/bash
cat > /tmp/a.r <<-COMMAND
  library("RSQLite")
  drv <- dbDriver("SQLite", max.con = 1)
  conn <- dbConnect(drv, dbname="/tmp/osmdb")
  rs <- dbSendQuery( conn, "SELECT distinct lat, lon from nodetag;" )
  R <- fetch(rs, n = -1)
  png("/tmp/a.png")
  plot(R\$lat, R\$lon)
  dev.off()
COMMAND
cat /tmp/a.r | r
display /tmp/a.png