Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package main
- import (
- "database/sql"
- "fmt"
- _ "github.com/lib/pq"
- _ "github.com/mattn/go-sqlite3"
- )
- const (
- host = "localhost"
- port = 5432
- user = "postgres"
- password = "test"
- dbname = "testdb"
- )
- var liteDB *sql.DB
- var psqlDB *sql.DB
- func connectSQLite() (*sql.DB, error) {
- db, err := sql.Open("sqlite3", "/home/morten/Downloads/testdb.dbv")
- if err != nil {
- return nil, err
- }
- fmt.Println("Loaded S57 database")
- return db, nil
- }
- func connectPsql() (*sql.DB, error) {
- psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
- "password=%s dbname=%s sslmode=disable",
- host, port, user, password, dbname)
- db, err := sql.Open("postgres", psqlInfo)
- if err != nil {
- return nil, err
- }
- err = db.Ping()
- if err != nil {
- return nil, err
- }
- fmt.Println("Connected to PostGresQL")
- return db, nil
- }
- func getGeometryData() ([]string, error) {
- fmt.Println("fetching geometry data...")
- var ret []string
- rows, err := liteDB.Query("SELECT wkt_geometry FROM MX_CHART")
- if err != nil {
- panic(err)
- }
- var geom string
- for rows.Next() {
- err = rows.Scan(&geom)
- if err != nil {
- panic(err)
- }
- ret = append(ret, geom)
- }
- rows.Close()
- return ret, nil
- }
- func setupPsql() {
- sqlStatement := `
- CREATE TABLE IF NOT EXISTS wkt_geometry (
- id SERIAL PRIMARY KEY,
- geometry TEXT NOT NULL
- );`
- _, err := psqlDB.Exec(sqlStatement)
- if err != nil {
- panic(err)
- }
- }
- func insertGeom(geom string) {
- sqlStatement := `
- INSERT INTO wkt_geometry(geometry) VALUES ($1)`
- _, err := psqlDB.Exec(sqlStatement, geom)
- if err != nil {
- panic(err)
- }
- }
- func truncate(db *sql.DB, tablename string) error {
- fmt.Println("Truncating table:", tablename)
- sqlStatement := fmt.Sprintf("TRUNCATE %s", tablename)
- _, err := db.Exec(sqlStatement)
- if err != nil {
- return err
- }
- return nil
- }
- func postGIS(geom string) {
- _, err := psqlDB.Exec("CREATE EXTENSION IF NOT EXISTS postgis")
- if err != nil {
- panic(err)
- }
- // SELECT POINT_LOCATION
- // FROM LOCATIONS_TABLE
- // WHERE ST_Contains(ST_GEOMFROMTEXT('POLYGON((P1.X P1.Y, P2.X P2.Y, ...))'), LOCATIONS_TABLE.POINT_LOCATION);
- sqlStatement := `
- SELECT geometry
- FROM wkt_geometry
- WHERE ST_Contains(ST_GEOMFROMTEXT('$1'), wkt_geometry.geometry);
- `
- rows, err := psqlDB.Query(sqlStatement, geom)
- if err != nil {
- panic(err)
- }
- var res string
- for rows.Next() {
- err = rows.Scan(&res)
- if err != nil {
- panic(err)
- }
- fmt.Println("POSTGIS: ", res)
- }
- rows.Close()
- // SELECT ST_Contains(polygon.geom, point.geom)
- // FROM public."LOCATIONS_TABLE" point, public."POLYGON" polygon
- }
- func main() {
- var err error
- liteDB, err = connectSQLite()
- if err != nil {
- panic(err)
- }
- psqlDB, err = connectPsql()
- if err != nil {
- panic(err)
- }
- defer liteDB.Close()
- defer psqlDB.Close()
- setupPsql()
- wktGeometry, err := getGeometryData()
- if err != nil {
- panic(err)
- }
- err = truncate(psqlDB, "wkt_geometry")
- if err != nil {
- panic(err)
- }
- for _, geom := range wktGeometry {
- insertGeom(geom)
- }
- fmt.Println("Inserted elements")
- fmt.Println(len(wktGeometry))
- postGIS(wktGeometry[0])
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement