#!/usr/bin/env ruby
# coding: utf-8
require 'sqlite3'
require 'cgi'
require 'json'
require 'csv'
cgi = CGI.new(:accept_charset => "UTF-8")
print "Content-type: text/html; charset=UTF-8\n\n"
mode = cgi['mode']
db=SQLite3::Database.new("db/viewer_prc.sq3")
thiscgi = "./"+File.basename($0)
def head()
print <<__EOM__
<!DOCTYPE html>
<html lang="ja">
<head>
<title>make GeoJSON</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
</head>
<body>
__EOM__
end
def body()
print <<__EOM__
</body>
</html>
__EOM__
end
#dbmap.length
if mode == ""#htmlに表示するやつを書く
head()
print <<__EOM__
<h1>データベースから抽出andデータ作成</h1>
<form method="POST" action="#{thiscgi}" enctype="multipart/form-data">
<p>条件指定<select name="cond2"></p>
__EOM__
#<p>条件指定: <input name="cond" length="20"></p>
dbmap = db.execute("SELECT maptype FROM maptype")
tt = 0
for i in 1 .. dbmap.length
print <<__EOM__
<option value=#{dbmap[tt][0]}>#{dbmap[tt][0]}</option>
__EOM__
tt += 1
end
print <<__EOM__
</select>
<p><input type="submit" value="OK"></p>
</form>
__EOM__
body()
else
# ok push後こっちにくる。この辺でgeojsonを作る
head()
# cond = CGI.escapeHTML(cgi["cond"].read)
cond2 = CGI.escapeHTML(cgi["cond2"].read)
#if cond = "" ##formに何もなかったら場合の処理
printf("%s",cond2)
dbn = db.execute("SELECT mapitem.ename,mapitem.name,mapitem.addr, mapitem.lat , mapitem.lon , object.objtype , object.jsonval , maptype.maptype , group_concat(attribute.attr || ',' || attribute.value ) FROM mapitem JOIN attribute JOIN object JOIN maptype ON mapitem.ename == attribute.ename AND mapitem.ename == object.ename AND mapitem.ename == maptype.ename WHERE maptype.maptype == ? GROUP BY maptype.maptype;", cond2.to_s)
# else
#dbn = db.execute("SELECT mapitem.ename,mapitem.name,mapitem.addr, mapitem.lat , mapitem.lon , object.objtype , object.jsonval , maptype.maptype , group_concat(attribute.attr || ',' || attribute.value ) FROM mapitem JOIN attribute JOIN object JOIN maptype ON mapitem.ename == attribute.ename AND mapitem.ename == object.ename AND mapitem.ename == maptype.ename WHERE maptype.maptype == ? GROUP BY maptype.maptype;", cond.to_s)
# end
if dbn[0][5] == "Point"
hoge1 = Array.new
hoge1 = dbn[0][6].split(",")
hash = {"type"=>"FeatureCollection","features" => [{"type" =>"Feature","properties"=>{"name"=>dbn[0][1],"address"=>dbn[0][2],"maptype"=>dbn[0][7] },"geometry" =>{"type" => dbn[0][5],"coordinates"=>[hoge1[0].to_f,hoge1[1].to_f]}}]}
else
##Point以外の時の処理
hash = {"type"=>"FeatureCollection","features" => [{"type" =>"Feature","properties"=>{"name"=>dbn[0][1],"address"=>dbn[0][2],"maptype"=>dbn[0][7] },"geometry" =>{"type" => dbn[0][5],"coordinates"=>[dbn[0][6]]}}]}
end
### ここまで
##ここから add kye value
hogehoge2 = Array.new
hogehoge3 = Array.new
hogehoge4 = Array.new
hogehoge2 = dbn[0][8].split(",")
tt = 0
for t in 0..hogehoge2.length - 1
if tt % 2 == 0 #あまりが0 == 偶数
hogehoge3[0] = hogehoge2[0]
else
hogehoge4[0] = hogehoge2[1]
end
tt += 1
end
for g in 0.. hogehoge3.length - 1
hash["features"][0]["properties"][hogehoge3[0]] = hogehoge4[0]
end
###ここまで
hash_leng = dbn.length #データの抽出量を数える
if hash_leng >= 2 ###データの抽出が2個以上ある時の処理
i = 1
#u = 1
for hoge2 in 1..hash_leng - 1
if dbn[i][5] == "Point"
hoge1 = Array.new
hoge1 = dbn[i][6].split(",")
hash["features"][i] = {"type" =>"Feature","properties"=>{"name"=>dbn[i][1],"address"=>dbn[i][2],"maptype"=>dbn[i][7]},"geometry" =>{"type" => dbn[i][5],"coordinates"=>[hoge1[0].to_f,hoge1[1].to_f]}}
else
#### point以外の処理
hash["features"][i] = {"type" =>"Feature","properties"=>{"name"=>dbn[i][1],"address"=>dbn[i][2],"maptype"=>dbn[i][7]},"geometry" =>{"type" => dbn[i][5],"coordinates"=>[dbn[i][6]]}}
end
hogehoge2 = Array.new ##配列リセット
hogehoge3 = Array.new
hogehoge4 = Array.new
hogehoge2 = dbn[i][8].split(",")
tt1 = 0
tt2 = 0
tt3 = 0
tt4 = 0
tt5 = 1
for t in 0..hogehoge2.length - 1
if tt1 % 2 == 0 #あまりが0 == 偶数
hogehoge3[tt2] = hogehoge2[tt4]##kye
tt2 += 1
tt4 += 2
else
hogehoge4[tt3] = hogehoge2[tt5]##value
tt3 += 1
tt5 += 2
end
tt1 += 1
end
ww = 0
for g in 0.. hogehoge3.length - 1
hash["features"][i]["properties"][hogehoge3[ww]] = hogehoge4[ww]
ww += 1
end
i += 1
# u += 1
end
end
#### GeoJSON write
open("geojson/test.geojson",'w') do |row|
geojson = JSON.pretty_generate(hash)
row.print geojson
#puts geojson
# print("geojsonできたよ\n")
#end
#### ここまで
### create kml
#system("ogr2ogr -f KML geojson/test.KML geojson/test.geojson")
#print("kmlできたよ\n")
### ここまで
#### gitbucket push GeoJSON
#system("sh auto_push.sh \"add geojson\"")
#print("pushされたよ\n")
geojson = JSON.pretty_generate(hash)
print <<__EOM__
<p>条件: #{cond2}</p>
<p> #{geojson}</p>
<p> 以下でダウンロードできます</p>
<a href="geojson/test.geojson" download="test.geojson">ダウンロード</a>
__EOM__
body()
end
end
