Skip to content

Instantly share code, notes, and snippets.

@ptrv

ptrv/spatialite_example.go

Last active September 8, 2022 02:48
Show Gist options
  • Save ptrv/6335248 to your computer and use it in GitHub Desktop.
Save ptrv/6335248 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ptrv revised this gist Aug 26, 2013. 1 changed file with 18 additions and 28 deletions.
    46 changes: 18 additions & 28 deletions spatialite_example.go
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,22 @@ import (
    "os"
    )

    func runQuery(db *sql.DB, query string) {
    tx, err := db.Begin()
    if err != nil {
    log.Fatal(err)
    }
    stmt, err := tx.Prepare(query)
    if err != nil {
    log.Fatal(err)
    }
    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
    log.Fatal(err)
    }
    tx.Commit()
    }
    func main() {
    sql.Register("sqlite3_with_spatialite",
    &sqlite3.SQLiteDriver{
    @@ -23,20 +39,7 @@ func main() {
    // This has to be surrounded by Begin and Commit,
    // otherwise InitSpatialMetaData() is very slow
    q := "SELECT InitSpatialMetaData();"
    tx, err := db.Begin()
    if err != nil {
    log.Fatal(err)
    }
    stmt, err := tx.Prepare(q)
    if err != nil {
    log.Fatal(err)
    }
    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
    log.Fatal(err)
    }
    tx.Commit()
    runQuery(db, q)

    sqls := []string{
    "DROP TABLE IF EXISTS testtable",
    @@ -52,18 +55,5 @@ func main() {
    }

    q = "INSERT INTO testtable (name, geom) VALUES ('Test', GeomFromText('POLYGON((10 10, 20 10, 20 20, 10 20, 10 10))', 4326));"
    tx, err = db.Begin()
    if err != nil {
    log.Fatal(err)
    }
    stmt, err = tx.Prepare(q)
    if err != nil {
    log.Fatal(err)
    }
    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
    log.Fatal(err)
    }
    tx.Commit()
    runQuery(db, q)
    }
  2. ptrv revised this gist Aug 25, 2013. 1 changed file with 4 additions and 17 deletions.
    21 changes: 4 additions & 17 deletions spatialite_example.go
    View file
    Original file line number Diff line number Diff line change
    @@ -8,23 +8,10 @@ import (
    )

    func main() {
    sql.Register("sqlite3_with_spatialite", &sqlite3.SQLiteDriver{
    EnableLoadExtension: true,
    ConnectHook: func(c *sqlite3.SQLiteConn) {
    stmt, err := c.Prepare("SELECT load_extension('libspatialite');")
    if err != nil {
    log.Panic(err)
    }
    _, err = stmt.Exec(nil)
    if err != nil {
    log.Panic(err)
    }

    if err = stmt.Close(); err != nil {
    log.Panic(err)
    }
    },
    })
    sql.Register("sqlite3_with_spatialite",
    &sqlite3.SQLiteDriver{
    Extensions: []string{"libspatialite"},
    })

    os.Remove("./foo.db")
    db, err := sql.Open("sqlite3_with_spatialite", "./foo.db")
  3. ptrv created this gist Aug 25, 2013.
    82 changes: 82 additions & 0 deletions spatialite_example.go
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,82 @@
    package main

    import (
    "database/sql"
    "github.com/mattn/go-sqlite3"
    "log"
    "os"
    )

    func main() {
    sql.Register("sqlite3_with_spatialite", &sqlite3.SQLiteDriver{
    EnableLoadExtension: true,
    ConnectHook: func(c *sqlite3.SQLiteConn) {
    stmt, err := c.Prepare("SELECT load_extension('libspatialite');")
    if err != nil {
    log.Panic(err)
    }
    _, err = stmt.Exec(nil)
    if err != nil {
    log.Panic(err)
    }

    if err = stmt.Close(); err != nil {
    log.Panic(err)
    }
    },
    })

    os.Remove("./foo.db")
    db, err := sql.Open("sqlite3_with_spatialite", "./foo.db")
    if err != nil {
    log.Panic(err)
    }
    defer db.Close()

    // This has to be surrounded by Begin and Commit,
    // otherwise InitSpatialMetaData() is very slow
    q := "SELECT InitSpatialMetaData();"
    tx, err := db.Begin()
    if err != nil {
    log.Fatal(err)
    }
    stmt, err := tx.Prepare(q)
    if err != nil {
    log.Fatal(err)
    }
    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
    log.Fatal(err)
    }
    tx.Commit()

    sqls := []string{
    "DROP TABLE IF EXISTS testtable",
    "CREATE TABLE testtable (id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR(255));",
    "SELECT AddGeometryColumn('testtable', 'geom', 4326, 'POLYGON', 2);",
    "SELECT CreateSpatialIndex('testtable', 'geom');",
    }
    for _, sql := range sqls {
    _, err = db.Exec(sql)
    if err != nil {
    log.Fatal(err)
    }
    }

    q = "INSERT INTO testtable (name, geom) VALUES ('Test', GeomFromText('POLYGON((10 10, 20 10, 20 20, 10 20, 10 10))', 4326));"
    tx, err = db.Begin()
    if err != nil {
    log.Fatal(err)
    }
    stmt, err = tx.Prepare(q)
    if err != nil {
    log.Fatal(err)
    }
    defer stmt.Close()
    _, err = stmt.Exec()
    if err != nil {
    log.Fatal(err)
    }
    tx.Commit()
    }