dounao1875 2019-02-09 02:21
浏览 663
已采纳

使用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 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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥15 state显示变量是字符串形式,但是仍然红色,无法引用,并显示类型不匹配
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波