バスルート情報(LINESTRING)に幅を当与えてPOLYGONにして、データベースに格納する方法(Exec Error: pq: parse error - invalid geometry)

    // C:\Users\ebata\yoko_bus_route\others>go run test2.go
    バスルート情報(LINESTRING)に幅を当与えてPOLYGONにして、データベースに格納する方法

 

    (Step.1)テーブルの作成
    CREATE TABLE bus_route(name varchar(50), geom geometry(POLYGON,4326) );

 

    (Step.2)以下でエントリーを追加できることを確認
    INSERT INTO bus_route(name, geom) VALUES('test2', ST_Buffer(st_setsrid(ST_GeomFromtext('linestring(139.595873 35.378154,139.595779 35.37812,139.595766 35.378045,139.595922 35.377752,139.596212 35.377193)'),4326),0.0001,'endcap=round join=round'));

 

    route_db=# select * from bus_route;
    name  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                geom
    ------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    test1 | 0103000020E61000000100000031000000EC55D36C11736140FEFA05EA63B04140F87A208412736140B5B6AEB75BB04140C96A2B8512736140A12BBFAF5BB041404BFB57E514736140AACF835E49B04140E6839BF41473614026E3D2C548B04140BE7F21FC1473614016C2272448B041401EEB9FFB147361406DE8B87F47B04140D5C01BF314736140A104D8DE46B0414036C9E8E21473614005CDB34746B04140DB61A6CB147361402D2D1BC045B04140CF5D39AE14736140AF21444D45B04140673AC38B147361407C7498F344B0414046FF96651473614010528AB644B0414002372C3D14736140B062729844B0414098801014147361402FB5789A44B04140A547D8EB13736140535E89BC44B04140BE390FC613736140D03C55FD44B04140C71129A4137361403DDA5E5A45B04140E84E7387137361406CEA12D045B041409B630871137361400C78EB5946B0414084EF5F1111736140ED342CA758B041402CE6BECA0F73614067FE133D62B04140F2C8AEB90F736140BD9C67E862B04140C33964B20F736140124B819E63B04140E26F39B50F736140B5E0935664B0414047C27CD00F736140C026B9CB66B0414071B186DC0F736140AD16347567B041404E3A26F10F736140171FE31068B041401A76720D10736140BA15E89768B041405CD22B30107361400A2E4E0469B04140E229CA5710736140113D4D5169B041408307EC1C11736140BDAE836E6AB041401240C444117361402E7E05976AB041403D3DD26D117361405331A89F6AB041406B1A82961173614073D316886AB04140009143BD117361406E4039516AB04140435A99E011736140BD3B2BFD69B0414055D627FF1173614044B2278F69B041401B68C217127361401BF3680B69B04140A802772912736140D717FF7668B0414084769733127361409F359ED767B041404D23C03512736140F240663367B04140D5CBDB2F127361403ACCA69066B041401467242212736140E5F1A0F565B04140E8E5200D12736140D9CC496865B041408A03A0F11173614090DC0FEE64B04140BF53B0D0117361407895A58B64B04140EFDC95AB11736140452BD34464B04140EC55D36C11736140FEFA05EA63B04140
    SQLのコマンド画面からは、上記の"INSERT INTO bus_route...."でエントリーできるのですが、これをプログラム(ins.Exec())を使うと、
    どうしても、
    Exec Error: pq: parse error - invalid geometry
    というエラーが出てきて、これを消すことができないです。
    そこで、まず、"selectで"0103000020E6100000010....."を受けとってから、これを、ins.Exec()を使ってエントリーすることにしました(原因はよく分かっていません)
/*
	// C:\Users\ebata\yoko_bus_route\others>go run test2.go

	バスルート情報(LINESTRING)に幅を当与えてPOLYGONにして、データベースに格納する方法

	テーブルの作成
	CREATE TABLE bus_route(name varchar(50), geom geometry(POLYGON,4326) );

	以下でエントリーを追加できることを確認
	INSERT INTO bus_route(name, geom) VALUES('test2', ST_Buffer(st_setsrid(ST_GeomFromtext('linestring(139.595873 35.378154,139.595779 35.37812,139.595766 35.378045,139.595922 35.377752,139.596212 35.377193)'),4326),0.0001,'endcap=round join=round'));

	route_db=# select * from bus_route;
 	name  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                geom
	------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 	test1 | 0103000020E61000000100000031000000EC55D36C11736140FEFA05EA63B04140F87A208412736140B5B6AEB75BB04140C96A2B8512736140A12BBFAF5BB041404BFB57E514736140AACF835E49B04140E6839BF41473614026E3D2C548B04140BE7F21FC1473614016C2272448B041401EEB9FFB147361406DE8B87F47B04140D5C01BF314736140A104D8DE46B0414036C9E8E21473614005CDB34746B04140DB61A6CB147361402D2D1BC045B04140CF5D39AE14736140AF21444D45B04140673AC38B147361407C7498F344B0414046FF96651473614010528AB644B0414002372C3D14736140B062729844B0414098801014147361402FB5789A44B04140A547D8EB13736140535E89BC44B04140BE390FC613736140D03C55FD44B04140C71129A4137361403DDA5E5A45B04140E84E7387137361406CEA12D045B041409B630871137361400C78EB5946B0414084EF5F1111736140ED342CA758B041402CE6BECA0F73614067FE133D62B04140F2C8AEB90F736140BD9C67E862B04140C33964B20F736140124B819E63B04140E26F39B50F736140B5E0935664B0414047C27CD00F736140C026B9CB66B0414071B186DC0F736140AD16347567B041404E3A26F10F736140171FE31068B041401A76720D10736140BA15E89768B041405CD22B30107361400A2E4E0469B04140E229CA5710736140113D4D5169B041408307EC1C11736140BDAE836E6AB041401240C444117361402E7E05976AB041403D3DD26D117361405331A89F6AB041406B1A82961173614073D316886AB04140009143BD117361406E4039516AB04140435A99E011736140BD3B2BFD69B0414055D627FF1173614044B2278F69B041401B68C217127361401BF3680B69B04140A802772912736140D717FF7668B0414084769733127361409F359ED767B041404D23C03512736140F240663367B04140D5CBDB2F127361403ACCA69066B041401467242212736140E5F1A0F565B04140E8E5200D12736140D9CC496865B041408A03A0F11173614090DC0FEE64B04140BF53B0D0117361407895A58B64B04140EFDC95AB11736140452BD34464B04140EC55D36C11736140FEFA05EA63B04140

	SQLのインタースを使うと、上記の"INSERT INTO bus_route...."でエントリーできるのですが、これをプログラム(ins.Exec())を使うと、
	どうしても、
	Exec Error: pq: parse error - invalid geometry
	というエラーが出てきて、これを消すことができないです。

	そこで、まず、"selectで"0103000020E6100000010....."を受けとってから、これを、ins.Exec()を使ってエントリーすることにしました(原因はよく分かっていません)
*/

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq" // ←これを追記
)

func main() {

	db, err := sql.Open("postgres",
		"user=postgres password=password host=192.168.0.23 port=15432 dbname=route_db sslmode=disable")
	if err != nil {
		log.Fatal("OpenError: ", err)
	}
	defer db.Close()

	str := "select ST_Buffer(st_setsrid(ST_GeomFromtext('linestring(139.595873 35.378154,139.595779 35.37812,139.595766 35.378045,139.595922 35.377752,139.596212 35.377193)'),4326),0.0001,'endcap=round join=round')"

	rows, err := db.Query(str)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var ret_str string
	for rows.Next() {
		if err := rows.Scan(&ret_str); err != nil {
			fmt.Println(err)
		}
		fmt.Println(ret_str)
	}

	ins, err := db.Prepare("insert into bus_route (name, geom) VALUES($1, $2)")
	if err != nil {
		log.Fatal("db.Prepare Error: ", err)
	}

	a := "test2"
	_, err = ins.Exec(a, ret_str)
	if err != nil {
		log.Fatal("ins.Exec Error: ", err)
	}

}

ちなみに

route_db=# SELECT name, ST_AsText(geom) from bus_route;

とすると、ちゃんとポリゴンで表示されます。

test2 | POLYGON((139.595877087369 35.378049137993,139.596010268629 35.3777989962668,139.596010765838 35.3777980502557,139.596300765838 35.3772390502557,139.596308044186 35.3772208480571,139.596311631611 35.3772015756733,139.596311390248 35.3771819737311,139.596307329374 35.3771627955223,139.596299605045 35.3771447780546,139.596288514104 35.3771286137292,139.596274482769 35.3771149237322,139.596258050256 35.3771042341623,139.596239848057 35.3770969558137,139.596220575673 35.3770933683894,139.596200973731 35.377093609752,139.596181795522 35.3770976706261,139.596163778055 35.3771053949546,139.596147613729 35.3771164858958,139.596133923732 35.3771305172312,139.596123234162 35.3771469497443,139.595833480238 35.3777054754117,139.595677731371 35.3779980037332,139.595669595124 35.3780184274596,139.595666118386 35.378040135509,139.595667469198 35.3780620786724,139.595680469198 35.3781370786724,139.595686209748 35.3781572822894,139.595696043647 35.3781758412353,139.595709537084 35.3781919368007,139.595726094808 35.3782048589456,139.595744986392 35.3782140376226,139.595838986392 35.3782480376226,139.595857985784 35.3782528664418,139.595877562164 35.3782538958791,139.595896963222 35.3782510863738,139.595915443388 35.3782445458936,139.595932292477 35.3782345257855,139.59594686299 35.3782214111166,139.59595859499 35.3782057058763,139.595967037623 35.3781880136082,139.595971866442 35.3781690142161,139.595972895879 35.3781494378362,139.595970086374 35.3781300367777,139.595963545894 35.3781115566124,139.595953525785 35.3780947075226,139.595940411117 35.3780801370095,139.595924705876 35.3780684050097,139.595907013608 35.3780599623774,139.595877087369 35.378049137993))

2023,江端さんの技術メモ

Posted by ebata