dounao1875 2019-02-09 02:21
浏览 665

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥65 LineageOs-21.0系统编译问题
  • ¥30 关于#c++#的问题,请各位专家解答!
  • ¥15 App的会员连续扣费
  • ¥15 不同数据类型的特征融合应该怎么做
  • ¥15 用proteus软件设计一个基于8086微处理器的简易温度计
  • ¥15 用联想小新14Pro
  • ¥15 multisim中关于74ls192n和DSWPK开关仿真图分析(减法计数器)
  • ¥15 w3wp,exe 中发生未处理的 Microsoft ,NETFramework 异常。
  • ¥20 C51单片机程序及仿真(加减器)
  • ¥15 AQWA | 水动力分析 二阶波浪力