Inserting and selecting PostGIS Geometry with Gorm

小伙伴们对Golang编程感兴趣吗？是否正在学习相关知识点？如果是，那么本文《Inserting and selecting PostGIS Geometry with Gorm》，就很适合你，本篇文章讲解的知识点主要包括golang。在之后的文章中也会多多分享相关知识点，希望对大家的知识积累有所帮助！

问题内容

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.

正确答案

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](https://github.com/venicegeo/geojson-geos- go) to convert said struct into the go- geos struct I was using. A little convoluted, yes, but it works.

今天关于《Inserting and selecting PostGIS Geometry with Gorm》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于golang的内容请关注golang学习网公众号！