SHARE
TWEET

Untitled

a guest Jun 19th, 2019 139 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package main
  2.  
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.  
  7.     _ "github.com/lib/pq"
  8.     _ "github.com/mattn/go-sqlite3"
  9. )
  10.  
  11. const (
  12.     host     = "localhost"
  13.     port     = 5432
  14.     user     = "postgres"
  15.     password = "test"
  16.     dbname   = "testdb"
  17. )
  18.  
  19. var liteDB *sql.DB
  20. var psqlDB *sql.DB
  21.  
  22. func 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.  
  32. func 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.  
  51. func 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.  
  76. func 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.  
  88. func 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.  
  97. func 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.  
  108. func 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.  
  142. func 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. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top