2019-02-09 02:21 阅读 405

使用Gorm插入和选择PostGIS Geometry

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    douwang9650 douwang9650 2019-02-14 02:49

    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.

    点赞 评论 复制链接分享
  • duanbu4962 duanbu4962 2019-02-13 08:36

    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

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

    点赞 评论 复制链接分享