Inserting and selecting PostGIS Geometry with Gorm

Question

I've been trying to find a way to insert and retrieve geometric types using Golang, and specifically the library gorm. I'm also attempting to use the library orb that defines different types for geometries, and provides encoding/decoding between different formats.

Orb has Scan() and Value() methods already implemented for each type. This allows go's Insert() and Scan() functions to work with types other than primitives. Orb expects however to be using geometry represented in the well-known binary (WKB) format.

The orb documentation shows that to accomplish this, you should simply wrap the field in the PostGIS functions ST_AsBinary() and ST_GeomFromWKB() for querying and inserting respectively. For example, with a table defined as:

_, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS orbtest (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL,
            geom geometry(POLYGON, 4326) NOT NULL
        );
    `)

You can just do:

rows, err := db.Query("SELECT id, name, ST_AsBinary(geom) FROM orbtest LIMIT 1")

And for insert (where p is an orb.Point):

db.Exec("INSERT INTO orbtest (id, name, geom) VALUES ($1, $2, ST_GeomFromWKB($3))", 1, "Test", wkb.Value(p))

Here's my issue: By using GORM, I don't have the luxury of being able to build those queries with those functions. GORM will automatically insert values into the database given a struct, and will scan in data into the whole hierarchy of the struct. Those Scan() and Value() methods are called behind the scenes, without my control.

Trying to directly insert binary data into a geometry column won't work, and directly querying a geometry column will give the result in hex.

I've tried multiple database approaches to solve this. I've attempted creating views that automatically call the needed functions on the geometry columns. This worked for querying, but not inserting.

Is it possible to make some sort of trigger or rule that would automatically call the needed functions on the data coming in/out?

I should also note that the library I'm working on works completely independent of the data and schemas, so I don't have the luxury of hard coding any sort of query. I could of course write a function that scans the entire data model, and generates queries from scratch, but I'd prefer if there was a better option.

Does anyone know of a way of making this work in SQL? Being able to call functions on a column automatically by just querying the column itself?

Any advice would be greatly appreciated.

Superincumbent

Answer 1

Another solution, which I ended up using was with go-geos, as I discovered I needed to use the GEOS C library. With that, I am able to convert the struct into WKT for inserting (as postgis accepts it as regular text) and convert from WKB when scanning.

type Geometry4326 *geos.Geometry

// Value converts the given Geometry4326 struct into WKT such that it can be stored in a 
// database. Implements Valuer interface for use with database operations.
func (g Geometry4326) Value() (driver.Value, error) {

    str, err := g.ToWKT()
    if err != nil {
        return nil, err
    }

    return "SRID=4326;" + str, nil
}

// Scan converts the hexadecimal representation of geometry into the given Geometry4326 
// struct. Implements Scanner interface for use with database operations.
func (g *Geometry4326) Scan(value interface{}) error {

    bytes, ok := value.([]byte)
    if !ok {
        return errors.New("cannot convert database value to geometry")
    }

    str := string(bytes)

    geom, err := geos.FromHex(str)
    if err != nil {
        return errors.Wrap(err, "cannot get geometry from hex")
    }

    geometry := Geometry4326(geom)
    *g = geometry

    return nil
}

This solution might not be ideal for everyone as not everyone needs to use the GEOS C library, which can be a pain to get working on windows. I'm sure though that the same thing can be accomplished using different libraries.

I additionally implemented UnmarshalJSON() and MarshalJSON() on the struct so that it can automatically Marshal/Unmarshal GeoJSON, and then save/get from the database seamlessly. I accomplished this using geojson-go to convert GeoJSON to/from a struct, and then geojson-geos-go to convert said struct into the go-geos struct I was using. A little convoluted, yes, but it works.

Superincumbent

Answer 2

I used @robbieperry22's answer with a different encoding library and found I didn't need to tinker with bytes at all.

Included gist for reference.

import  "github.com/twpayne/go-geom/encoding/geojson"


type EWKBGeomPoint geom.Point

func (g *EWKBGeomPoint) Scan(input interface{}) error {
    gt, err := ewkb.Unmarshal(input.([]byte))
    if err != nil {
        return err
    }
    g = gt.(*EWKBGeomPoint)

    return nil
}

func (g EWKBGeomPoint) Value() (driver.Value, error) {
    b := geom.Point(g)
    bp := &b
    ewkbPt := ewkb.Point{Point: bp.SetSRID(4326)}
    return ewkbPt.Value()
}


type Track struct {
    gorm.Model

    GeometryPoint EWKBGeomPoint `gorm:"column:geom"`
}

And then used a little customization on the table set up/migrate part:

err = db.Exec(`CREATE TABLE IF NOT EXISTS tracks (
    id SERIAL PRIMARY KEY,
    geom geometry(POINT, 4326) NOT NULL
);`).Error
if err != nil {
    return err
}

err = gormigrate.New(db, gormigrate.DefaultOptions, []*gormigrate.Migration{
{
    ID: "init",
    Migrate: func(tx *gorm.DB) error {
        return tx.CreateTable(
            Tables...,
        ).Error
    },
},
{
    ID: "tracks_except_geom",
    Migrate: func(tx *gorm.DB) error {
        return db.AutoMigrate(Track{}).Error
    },
},
}).Migrate()

Fasano

Answer 3

Is it possible to make some sort of trigger or rule that would automatically call the needed functions on the data coming in/out?

Ever tried gorm hooks, example:

type Example struct {
    ID   int
    Name string
    Geom ...
}

func (e *Example) AfterFind() (err error) {
    e.Geom = ... // Do whatever you like here
    return
}

There is a handful of hooks that you can use. I find them pretty neat and useful.

Maudmaude

Answer 4

The solution I ended up using was as follows:

First I created new types that wrapped all of the orb types, for example:

type Polygon4326 orb.Polygon
type Point4326 orb.Point

Then I implemented the Scan(), Value() methods on each type. I had to however edit the bytes and convert to/from hexadecimal. When you directly query on a spatial column in PostGIS, it will return a hexadecimal representation of EWKB, essentially WKB, but including 4 bytes to represent the projection ID (in my case 4326).

Before inserting, I had to add the bytes that represent the projection of 4326.

Before reading, I had to strip those bytes, since orb's built in scanning expected WKB format.

Superincumbent

That sounds exactly like what I need, could you explain how exactly you converted from the hex string to WKB? – Glanville

Answer 5

You can do it like:

package utils

import (
    "context"
    "encoding/json"
    "fmt"

    geojson "github.com/paulmach/go.geojson"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type GeoPoint geojson.Geometry

func (g GeoPoint) GormDataType() string {
    return "geography(Point, 4326)"
}

func (g GeoPoint) GormDBDataType() string {
    return "geometry(Point, 4326)"
}
func (g GeoPoint) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
    if len(g.Point) == 0 { // Sprawdzanie, czy punkt jest pusty
        return clause.Expr{
            SQL: "NULL",
        }
    }

    geoJSONBytes, err := json.Marshal(g)
    if err != nil {
        return clause.Expr{SQL: "NULL"} // Obsłuż błąd
    }

    return clause.Expr{
        SQL:  "ST_SetSRID(ST_GeomFromGeoJSON(?),4326)",
        Vars: []interface{}{string(geoJSONBytes)},
    }
}

func (g *GeoPoint) Scan(input interface{}) error {
    switch value := input.(type) {
    case []byte:
        // Stworzenie pustego obiektu GeoPoint
        *g = GeoPoint{}
    case string:
        geom, err := geojson.UnmarshalGeometry([]byte(value))
        if err != nil {
            return fmt.Errorf("can't unmarshal GeoJSON: %w", err)
        }

        if geom.Type != geojson.GeometryPoint {
            return fmt.Errorf("expected point geometry, got %s", geom.Type)
        }

        // Przypisanie wartości do *g
        *g = GeoPoint(*geom)
    default:
        return fmt.Errorf("can't convert %T to GeoJSON", value)
    }

    return nil
}

And in model desc:

    Position     utils.GeoPoint `gorm:"column:position"`

Next, to set data - it will just work like this (*s.Geometry is from geojson package):

    utils.GeoPoint(*s.Geometry)

And to receive data:

    result := store.db.Raw(`SELECT id, uuid, ST_AsGeoJSON(position) as position FROM table WHERE deleted_at IS NULL AND something_id = ?`, somethingID).Scan(&models)

(I don't find way to automate ST_AsGeoJSON function on specific field via GORM)

Candra

Answer 6

for gomigrate/v2, a bit newer updated version of code above, this is what I use:

func customMigrateTables() error {

sqlStatements := []string{
    `CREATE SCHEMA IF NOT EXISTS "your custom schema"`,
    `CREATE EXTENSION IF NOT EXISTS postgis`,
    `CREATE TABLE IF NOT EXISTS "your custom table" (
            id SERIAL PRIMARY KEY,
            geom geometry(GEOMETRY, 4326) NOT NULL
        );`,
    // needed for some postgres id issue with gorm.
    `ALTER TABLE IF EXISTS dook.findings ALTER COLUMN "id" TYPE bigint`,
}

for _, stm := range sqlStatements {
    err := DB.Exec(stm).Error
    if err != nil {
        log.Fatal(err)
    }

}

err := gormigrate.New(DB, gormigrate.DefaultOptions, []*gormigrate.Migration{
    {
        ID: "init",
        Migrate: func(tx *gorm.DB) error {
            // your normal tables to be migrated.
            return tx.AutoMigrate(&Note{})
        },
    },
    {
        ID: "findings_except_geom",
        Migrate: func(tx *gorm.DB) error {
            return tx.AutoMigrate(Finding{})
        },
    },
}).Migrate()

return err

}

Pisarik