#!/usr/bin/env ruby
# coding: utf-8

require 'sqlite3'
require "json"
require "csv"

db=SQLite3::Database.new("viewer.sq3")

db.execute("CREATE TABLE IF NOT EXISTS " +
           "mapdate2( name text, period text, address text, season text, viewer text, lati text,long text, type text)")

thiscgi = "./"+File.basename($0)

#### csv読み取ってDBに挿入3
data = CSV.read("debug.csv", {:headers => true})
data.each{|r|
  db.execute("insert into mapdate3 values ( ?, ?, ?, ?, ?, ?,?,?)",[r["name"],r["period"],r["address"],r["season"],r["viewer"],r["lati"],r["long"],r["type"]])
}
##### ここまで

#### GeoJSON生成
dbn = db.execute("SELECT * from mapdate3 WHERE viewer=1") #条件指定 のちのちユーザーが指定できるように
hash_leng =  dbn.length #データの抽出量を数える
  hash = {"type"=>"FeatureCollection","features" => [{"type" =>"Feature","properties"=>{"name"=>dbn[0][0],"description"=>dbn[0][1]},"geometry" =>{"type" => dbn[0][7],"coordinates"=>[dbn[0][5].to_f,dbn[0][6].to_f]}}]}

if hash_leng >= 2 ###データの抽出が2個以上ある時の処理
  i = 1
  u = 1
  for hoge2 in 1..hash_leng - 1
    p i
    hash["features"][i] =  {"type" =>"Feature","properties"=>{"name"=>dbn[u][0],"description"=>dbn[u][1]},"geometry" =>{"type" => dbn[u][7],"coordinates"=>[dbn[u][5].to_f,dbn[u][6].to_f]}}
    i += 1
    u += 1
  end
end
#### ここまで


#### GeoJSONファイル書き込み
open("geojson/test.geojson",'w') do |row|
  geojson = JSON.pretty_generate(hash)
  row.print geojson
  print("geojsonできたよ\n")
  puts geojson
end
#### ここまで


### kml
system("ogr2ogr -f KML geojson/test.KML geojson/test.geojson")
print("kmlできたよ\n")
### ここまで

#### gitbucketにpush
#system("sh auto_push.sh \"add geojson\"")
print("pushされたよ\n")