· 6 years ago · Jun 20, 2019, 01:48 AM
1package main
2
3import (
4 "database/sql"
5 "fmt"
6
7 _ "github.com/lib/pq"
8 _ "github.com/mattn/go-sqlite3"
9)
10
11const (
12 host = "localhost"
13 port = 5432
14 user = "postgres"
15 password = "test"
16 dbname = "testdb"
17)
18
19var liteDB *sql.DB
20var psqlDB *sql.DB
21
22func connectSQLite() (*sql.DB, error) {
23 db, err := sql.Open("sqlite3", "/home/morten/Downloads/testdb.dbv")
24 if err != nil {
25 return nil, err
26 }
27
28 fmt.Println("Loaded S57 database")
29 return db, nil
30}
31
32func connectPsql() (*sql.DB, error) {
33 psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
34 "password=%s dbname=%s sslmode=disable",
35 host, port, user, password, dbname)
36
37 db, err := sql.Open("postgres", psqlInfo)
38 if err != nil {
39 return nil, err
40 }
41
42 err = db.Ping()
43 if err != nil {
44 return nil, err
45 }
46
47 fmt.Println("Connected to PostGresQL")
48 return db, nil
49}
50
51func getGeometryData() ([]string, error) {
52 fmt.Println("fetching geometry data...")
53
54 var ret []string
55
56 rows, err := liteDB.Query("SELECT wkt_geometry FROM MX_CHART")
57 if err != nil {
58 panic(err)
59 }
60
61 var geom string
62
63 for rows.Next() {
64 err = rows.Scan(&geom)
65 if err != nil {
66 panic(err)
67 }
68 ret = append(ret, geom)
69 }
70
71 rows.Close()
72
73 return ret, nil
74}
75
76func setupPsql() {
77 sqlStatement := `
78 CREATE TABLE IF NOT EXISTS wkt_geometry (
79 id SERIAL PRIMARY KEY,
80 geometry TEXT NOT NULL
81 );`
82 _, err := psqlDB.Exec(sqlStatement)
83 if err != nil {
84 panic(err)
85 }
86}
87
88func insertGeom(geom string) {
89 sqlStatement := `
90 INSERT INTO wkt_geometry(geometry) VALUES ($1)`
91 _, err := psqlDB.Exec(sqlStatement, geom)
92 if err != nil {
93 panic(err)
94 }
95}
96
97func truncate(db *sql.DB, tablename string) error {
98 fmt.Println("Truncating table:", tablename)
99 sqlStatement := fmt.Sprintf("TRUNCATE %s", tablename)
100 _, err := db.Exec(sqlStatement)
101 if err != nil {
102 return err
103 }
104
105 return nil
106}
107
108func postGIS(geom string) {
109 _, err := psqlDB.Exec("CREATE EXTENSION IF NOT EXISTS postgis")
110 if err != nil {
111 panic(err)
112 }
113 // SELECT POINT_LOCATION
114 // FROM LOCATIONS_TABLE
115 // WHERE ST_Contains(ST_GEOMFROMTEXT('POLYGON((P1.X P1.Y, P2.X P2.Y, ...))'), LOCATIONS_TABLE.POINT_LOCATION);
116 sqlStatement := `
117 SELECT geometry
118 FROM wkt_geometry
119 WHERE ST_Contains(ST_GEOMFROMTEXT('$1'), wkt_geometry.geometry);
120 `
121 rows, err := psqlDB.Query(sqlStatement, geom)
122 if err != nil {
123 panic(err)
124 }
125
126 var res string
127
128 for rows.Next() {
129 err = rows.Scan(&res)
130 if err != nil {
131 panic(err)
132 }
133 fmt.Println("POSTGIS: ", res)
134 }
135
136 rows.Close()
137
138 // SELECT ST_Contains(polygon.geom, point.geom)
139 // FROM public."LOCATIONS_TABLE" point, public."POLYGON" polygon
140}
141
142func main() {
143 var err error
144
145 liteDB, err = connectSQLite()
146 if err != nil {
147 panic(err)
148 }
149
150 psqlDB, err = connectPsql()
151 if err != nil {
152 panic(err)
153 }
154
155 defer liteDB.Close()
156 defer psqlDB.Close()
157
158 setupPsql()
159
160 wktGeometry, err := getGeometryData()
161 if err != nil {
162 panic(err)
163 }
164
165 err = truncate(psqlDB, "wkt_geometry")
166 if err != nil {
167 panic(err)
168 }
169
170 for _, geom := range wktGeometry {
171 insertGeom(geom)
172 }
173 fmt.Println("Inserted elements")
174 fmt.Println(len(wktGeometry))
175
176 postGIS(wktGeometry[0])
177}