使用Gorm插入和选择PostGIS Geometry [英] Inserting and selecting PostGIS Geometry with Gorm

问题描述

我一直在尝试找到一种使用Golang插入和检索几何类型的方法，尤其是库 gorm .我还尝试使用 orb 库，该库为几何定义了不同的类型，并提供了之间的编码/解码不同的格式.

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已经为每种类型实现了Scan()和Value()方法.这使go的Insert()和Scan()函数可以使用除基本类型以外的其他类型.但是，Orb希望使用众所周知的二进制(WKB)格式表示的几何.

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.

orb文档显示，要实现此目的，您只需将字段包装在PostGIS函数ST_AsBinary()和ST_GeomFromWKB()中，分别用于查询和插入.例如，将表定义为:

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
        );
    `)

您可以这样做:

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

对于插入(其中p是orb.Point):

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))

这是我的问题:通过使用GORM，我无法使用这些功能来构建那些查询. GORM将自动将值插入给定结构的数据库中，并将数据扫描到结构的整个层次结构中.这些Scan()和Value()方法在没有我控制的情况下在后台调用.

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.

有人知道用SQL进行这项工作的方法吗?只需查询列本身就能够自动调用列上的函数?

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?

任何建议将不胜感激.

推荐答案

我最终使用的另一个解决方案是 go-geos ，因为我发现我需要使用GEOS C库.这样，我就可以将结构转换为WKT进行插入(因为postgis将其作为常规文本接受)，并且可以在扫描时从WKB进行转换.

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
}

此解决方案可能并不适合每个人，因为并非每个人都需要使用GEOS C库，因此在Windows上工作可能会很痛苦.我敢肯定，使用不同的库可以完成同一件事.

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.

我还在该结构上实现了UnmarshalJSON()和MarshalJSON()，以便它可以自动编组/解组GeoJSON，然后无缝地从数据库中保存/获取.我使用 geojson-go 完成此操作，将GeoJSON转换为结构，然后将 geojson-geos-go 将所述结构转换为

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.

这篇关于使用Gorm插入和选择PostGIS Geometry的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！