ODデータ(csv):20220518weekday
領域データ(csv):yamaguchi_area
出力データ(csv):new_20220518weekday
その他: 年齢を乱数を使って適当に作成しているコードも入っている
// ~/yamaguchi/src_try1/others/main61.go
// Usage: go run main61.go 20220518weekday.csv new_20220518weekday.csv
package main
import (
"database/sql"
"encoding/csv"
"fmt"
"log"
"math/rand"
"os"
"strconv"
_ "github.com/lib/pq"
)
func main() {
file, err := os.Open("yamaguchi_area.csv")
if err != nil {
log.Fatal(err)
}
defer file.Close()
r := csv.NewReader(file)
rows, err := r.ReadAll() // csvを一度に全て読み込む
if err != nil {
log.Fatal(err)
}
str := "SELECT ST_Covers(st_geomfromtext('POLYGON(("
// 行ごとに
for i, row := range rows {
if i == 0 {
continue // CSVのヘッダー行を無視
}
str += row[1] + " " + row[2] + ", " // rowのままで取り出せば、文字列になっている
}
str1 := str[:len(str)-2] + "))'),st_geomfromtext('LINESTRING(" // 上記の最後の", "を削除して、文字列を追加
dbMap, err := sql.Open("postgres",
"user=postgres password=password host=192.168.0.23 port=15432 dbname=yama_db sslmode=disable")
log.Println("------------------ map db open ------------------")
if err != nil {
log.Fatal("OpenError: ", err)
}
defer dbMap.Close()
///////////////////////////////////////
//file2, err2 := os.Open("20220518weekday.csv")
file2, err2 := os.Open(os.Args[1]) // 第1パラメータ
if err2 != nil {
log.Fatal(err2)
}
defer file2.Close()
r2 := csv.NewReader(file2)
rows2, err2 := r2.ReadAll() // csvを一度に全て読み込む
if err != nil {
log.Fatal(err2)
}
file3, err3 := os.Create(os.Args[2]) // 第2パラメータ
if err3 != nil {
panic(err)
}
w := csv.NewWriter(file3)
var id int
for _, row := range rows2 {
//if i == 0 {
// continue // CSVのヘッダー行を無視
//}
str2 := str1 + row[0] + " " + row[1] + ", " + row[2] + " " + row[3]
str2 += ")'))"
fmt.Println(str2)
rows1, err := dbMap.Query(str2)
if err != nil {
log.Fatal(err)
}
defer rows1.Close()
//var dt string
var dt bool
for rows1.Next() {
if err := rows1.Scan(&dt); err != nil {
fmt.Println(err)
}
// fmt.Println(dt)
if dt {
//if err := rows.Scan(&id, &age, &type1, &departure_name, &departure_number, &departure_lat, &departure_lng, &arrival_name, &arrival_number, &arrival_lat, &arrival_lng); err != nil {
// fmt.Println(err)
// 上記のSQLのフォームと同じ形でcsvファイルを作る
var age int
fmt.Println("row[4]:", row[4])
// id_str := strconv.Itoa(id) // idを文字列に
if row[4] == "10-15" {
age = 10 + rand.Intn(5)
} else if row[4] == "15-29" {
age = 15 + rand.Intn(5)
} else if row[4] == "20-24" {
age = 20 + rand.Intn(5)
} else if row[4] == "25-29" {
age = 25 + rand.Intn(5)
} else if row[4] == "30-34" {
age = 30 + rand.Intn(5)
} else if row[4] == "35-39" {
age = 35 + rand.Intn(5)
} else if row[4] == "40-44" {
age = 40 + rand.Intn(5)
} else if row[4] == "44-49" {
age = 45 + rand.Intn(5)
} else if row[4] == "50-54" {
age = 50 + rand.Intn(5)
} else if row[4] == "55-59" {
age = 55 + rand.Intn(5)
} else if row[4] == "60-64" {
age = 60 + rand.Intn(5)
} else if row[4] == "65-69" {
age = 65 + rand.Intn(5)
} else if row[4] == "70-74" {
age = 70 + rand.Intn(5)
} else if row[4] == "75-80" {
age = 75 + rand.Intn(5)
} else if row[4] == "80-84" {
age = 80 + rand.Intn(5)
} else if row[4] == "85-89" {
age = 85 + rand.Intn(5)
} else if row[4] == "90-94" {
age = 90 + rand.Intn(5)
} else if row[4] == "95-99" {
age = 95 + rand.Intn(5)
} else {
age = 15 + rand.Intn(65) // 15箸ｷ79までの乱数
}
type1 := "resident"
departure_name := ""
_row1, _ := strconv.ParseFloat(row[1], 64)
_row0, _ := strconv.ParseFloat(row[0], 64)
departure_number, departure_lng, departure_lat := fixPosition(dbMap, _row1, _row0) // row[1], row[0]の順番に注意
arrival_name := ""
_row3, _ := strconv.ParseFloat(row[3], 64)
_row2, _ := strconv.ParseFloat(row[2], 64)
arrival_number, arrival_lng, arrival_lat := fixPosition(dbMap, _row3, _row2) // row[3], row[2]の順番に注意
output := []string{fmt.Sprint(id), fmt.Sprint(age), type1, departure_name, fmt.Sprint(departure_number), fmt.Sprint(departure_lat), fmt.Sprint(departure_lng), arrival_name, fmt.Sprint(arrival_number), fmt.Sprint(arrival_lat), fmt.Sprint(arrival_lng)}
//output := []string{row[0], row[1], row[2], row[3], row[4]}
fmt.Println("output:", output)
if err = w.Write(output); err != nil {
log.Fatal(err)
}
id++ // idの加算
}
}
err = rows1.Err()
if err != nil {
panic(err)
}
defer w.Flush()
if err := w.Error(); err != nil {
log.Fatal(err)
}
}
}
// 指定した座標に近いDB上の座標を取得
func fixPosition(db *sql.DB, _x1, _y1 float64) (int, float64, float64) {
// Scan用の仮変数
var source int
var longitude float64
var latitude float64
var dist float64
upperLimitMeter := 1500.0 // 近傍ノードの上限を1500 mに設定
str := fmt.Sprintf(
// 修正前: ways (道) の中から最近傍を取得
// "SELECT source, x1 AS longitude, y1 AS latitude, ST_Distance('SRID=4326;POINT(%v %v)'::GEOGRAPHY, the_geom) AS dist FROM ways WHERE ST_DWithin(the_geom, ST_GeographyFromText('SRID=4326;POINT(%v %v)'), %.1f) ORDER BY dist LIMIT 1",
// 修正後: ways_vertices_pgr (点座標) の中から最近傍を取得
"SELECT id AS source, lon AS longitude, lat AS latitude, ST_Distance('SRID=4326;POINT(%v %v)'::GEOGRAPHY, the_geom) AS dist FROM ways_vertices_pgr WHERE ST_DWithin(the_geom, ST_GeographyFromText('SRID=4326;POINT(%v %v)'), %.1f) ORDER BY dist LIMIT 1",
_x1, _y1, _x1, _y1, upperLimitMeter,
)
//fmt.Println(str)
rows, err := db.Query(str)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
foundGoodMapNode := false
for rows.Next() {
foundGoodMapNode = true
if err := rows.Scan(&source, &longitude, &latitude, &dist); err != nil {
fmt.Println(err)
}
//fmt.Println(source, longitude, latitude, dist)
}
if !foundGoodMapNode {
log.Println("Warning: in func fixPosition: Good Map Node not found for query point (",
_x1, ",", _y1, ")")
}
return source, longitude, latitude
}
