使用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个回答



我最终使用的解决方案如下:</ p>

首先,我创建了新的包装类型 所有的orb类型,例如:</ p>

  type Polygon4326 orb.Polygon 
type Point4326 orb.Point
</ code> </ pre>

然后我对每种类型实现了 Scan()</ code>, Value()</ code>方法。 但是,我必须编辑字节并转换为十六进制。 当直接在PostGIS中的空间列上查询时,它将返回EWKB的十六进制表示形式,本质上是WKB,但包括4个字节来表示投影ID(在我的情况下为4326)。</ p>

在插入之前,我必须添加代表4326投影的字节。</ p>

在读取之前,我必须去除那些字节,因为orb内置了扫描预期的WKB格式。</ p>

</ div>

展开原文

原文

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.




是否可以创建某种触发器或规则,以自动对传入的数据调用所需的函数 / out?</ p>
</ blockquote>

曾经尝试过的gorm钩子,例如:</ p>

  type示例struct {
ID int
名称字符串
Geom ...
}

func(e * Example)AfterFind()(错误错误){
e.Geom = ... // 在这里做任何您想做的事
return
}
</ code> </ pre>

有很多钩子。 我发现它们非常简洁实用。</ p>
</ div>

展开原文

原文

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.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐