トップページ ▶ 遐皮ゥカ驕灘・邂ア縺ィ謨呎攝 ▶ 繝・・繧ソ繝吶・繧ケ貍皮ソ・/A> ▶ Open Street Map 縺ョ繝・・繧ソ
[繧オ繧、繝医・繝・・縺ク]  

サイトの全体構成

Open Street Map 縺ョ繝・・繧ソ

縺薙・ Web 繝壹・繧ク縺ァ縺ッ・・Open Street Map 縺ョ繝・・繧ソ繧剃スソ縺・ESQLite 3 縺ョ繝・・繝悶Ν繧堤函謌舌＠縺セ縺吶E

笳・菴懈・縺吶ｋSQLite 3 繝・・繧ソ繝吶・繧ケ: osmdb

蜑肴コ門ｙ

谺。縺ョ繧ウ繝槭Φ繝峨ｒ菴ソ逕ィ縺吶ｋ

Open Street Map 繝・・繧ソ縺ョ蠢・を・Κ蛻・・蛻・ｊ蜃コ縺・

SQLite 3 繝・・繧ソ繝吶・繧ケ縺ョ逕滓・

菴ソ逕ィ縺吶ｋ繧ス繝輔ヨ繧を繧ィ繧「

縺ゅｉ縺九§繧∵アコ繧√※縺翫¥莠矩E

こ Web 繝壹・繧ク縺ァ縺ッ・後ョ繝シ繧ソ繝吶・繧ケ縺ョ菴懈・繧定。後＞縺セ縺吶・縺ァ・・菴懈・縺吶ｋ繝・・繧ソ繝吶・繧ケ縺ョ繝・・繧ソ繝吶・繧ケ隲也炊蜷・/STRONG>繧呈アコ繧√※縺翫¥縺薙→・・縺薙・ Web 繝壹・繧ク縺ァ縺ッ・梧ャ。縺ョ繧医≧縺ォ譖ク縺阪∪縺呻シ・

  • 繝・・繧ソ繝吶・繧ケ隲也炊蜷・ osmdb

    菴ソ逕ィ縺吶ｋ繝・・繧ソ繝吶・繧ケ縺ョ蜷榊燕縺ョ縺薙→繧抵シ後データベス論理名」と呼ぶことにする！EチE・繧ソ繝吶・繧ケ隲也炊蜷阪・・瑚・逕ア縺ォ豎コ繧√※縺・＞縺ァ縺吶′・悟濠隗呈枚蟄暦シ医▽縺セ繧願恭蟄励→闍ア險伜捷・峨ｒ菴ソ縺・シ後せ繝壹・繧ケ繧貞性縺セ縺ェ縺・％縺ィ・・

繝・・繝悶Ν縺ョ菴懈・

  1. 繝・・繝悶Ν螳夂セゥ (node, way, relation)

    笳・bash 繝励Ο繧ー繝ゥ繝/strong> 

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

    [image]
  2. チE・繝悶Ν縺ョ菴懈・

    笳・bash 繝励Ο繧ー繝ゥ繝/strong> 

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

    [image]
  3. (オプション) チE・繧ソ繝吶・繧ケ縺ョ蜀肴ァ区・

    笳・bash 繝励Ο繧ー繝ゥ繝/strong> 

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

# resutor 
cat >/tmp/a.$$.sql <<-SQL
.read /tmp/osmdb.sql
.exit
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/osmdb
#
cd /tmp
ls -la osmdb

    [image]
  4. プロチEヨ 

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

    [image]
  5. 霑ス蜉チE・繝悶Ν螳夂セゥ (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
.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
  7. 繝励Ο繝・ヨ 

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