領域の中に線が属しているかを判定し、属している場合はCSVデータ(ライン)を出力し、属していない場合は廃棄するプログラム

2023年2月8日

keyword: postgres postgis st_geomfromtext ST_Covers POLYGON LINESTRING

早い話、

postgres=# \c yama_db
psql (13.4, server 12.5 (Debian 12.5-1.pgdg100+1))
You are now connected to database "yama_db" as user "postgres".

yama_db=# SELECT ST_Covers(st_geomfromtext('POLYGON((34.15131035 131.5194525, 34.16270729 131.5152409, 34.16516798 131.5115994, 34.16898442 131.5047413, 34.17907707 131.5010998, 34.18354557 131.4989149, 34.18561453 131.5025188, 34.18856404 131.5058416, 34.18909138 131.5050188, 34.19687848 131.5103978, 34.19927298 131.5072766, 34.20121465 131.510747, 34.20300086 131.5120785, 34.20536452 131.5096798, 34.20975399 131.5068729, 34.20709501 131.5009018, 34.20388724 131.5048315, 34.20194564 131.5047294, 34.19785124 131.4988093, 34.20194564 131.4969721, 34.20528011 131.4932975, 34.20114366 131.4948286, 34.19802009 131.4962576, 34.19894873 131.4931955, 34.19705211 131.4916877, 34.19973769 131.4886834, 34.19579714 131.490747, 34.19717761 131.4888048, 34.19802009 131.4962576, 34.20061613 131.483555, 34.19383935 131.482506, 34.19306821 131.4800321, 34.18962489 131.4802896, 34.20041552 131.4735871, 34.19870882 131.4691668, 34.19193378 131.4719122, 34.19281714 131.4631425, 34.18750372 131.4649829, 34.18208468 131.4692086, 34.17814677 131.4619382, 34.18089338 131.4606752, 34.18352048 131.465583, 34.18537135 131.4575717, 34.18358019 131.4532773, 34.17689286 131.4597008, 34.17568909 131.4574644, 34.17805351 131.453254, 34.18115671 131.4490436, 34.18094562 131.4462621, 34.17766341 131.4457491, 34.17637565 131.444856, 34.17452943 131.4472807, 34.17400259 131.4457099, 34.1745741 131.4435783, 34.17591761 131.4391215, 34.17349927 131.4370465, 34.171469 131.4367036, 34.17997306 131.4313284, 34.17831615 131.4270193, 34.16993073 131.4276566, 34.17045141 131.4197235, 34.16662955 131.4242344, 34.17054098 131.4093664, 34.16585321 131.4150321, 34.16292695 131.4256418, 34.16209086 131.4305497, 34.15853738 131.4324262, 34.1555247 131.431539, 34.1368104 131.4231705, 34.13155312 131.4262604, 34.13252285 131.4303061, 34.12981919 131.4361751, 34.12994592 131.4412276, 34.13256509 131.4488319, 34.13167796 131.4539864, 34.13486474 131.4591635, 34.13933143 131.4660729, 34.14099198 131.4710403, 34.14447279 131.4734114, 34.14677254 131.4766944, 34.14983145 131.488296, 34.14860698 131.5025144, 34.15034783 131.5173554, 34.15131035 131.5194525))'),st_geomfromtext('LINESTRING(34.17282136235268 131.4810495539796, 34.17234106467595 131.47746432882846)'));

というSQL文をGoプログラムで作る。

(1)csvファイル(84点)からなる領域を作っておいて、エリアデータ
(2)ODデータを直線として取り込み、直線データ
(3)上記(2)の直線が、上記(1)の領域に入っているかどうかを調べて、入っている場合は標準出力で出力する

// ~/yamaguchi/src_try1/others/main6.go

package main

import (
	"database/sql"
	"encoding/csv"
	"fmt"
	"log"
	"os"

	_ "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")
	if err2 != nil {
		log.Fatal(err2)
	}
	defer file2.Close()

	r2 := csv.NewReader(file2)
	rows2, err2 := r2.ReadAll() // csvを一度に全て読み込む
	if err != nil {
		log.Fatal(err2)
	}

	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 {
				output := row[0] + "," + row[1] + "," + row[2] + "," + row[3] + "," + row[4]
				fmt.Println(output)
			}

		}

	}
}