使用Gorm和MySQL处理空间数据 [英] Working with spatial data with Gorm and MySQL

问题描述

我引用了 irbanana的答案关于支持PostGIS的空间数据类型.我正在使用MySQL，并尝试为自定义数据类型 EWKBGeomPoint 实现 Value().

I referenced irbanana's answer about supporting Spatial data type for PostGIS. I'm using MySQL and am trying to implement Value() for the custom data type EWKBGeomPoint.

我的Gorm模型:

import (
    "github.com/twpayne/go-geom"
    "github.com/twpayne/go-geom/encoding/ewkb"
)

type EWKBGeomPoint geom.Point

type Tag struct {
    Name string `json:"name"`
json:"siteID"` // forign key
    Loc EWKBGeomPoint `json:"loc"`
}

据我所知，MySQL支持这样的插入:

From what I know, MySQL supports insertion like this:

INSERT INTO `tag` (`name`,`loc`) VALUES ('tag name',ST_GeomFromText('POINT(10.000000 20.000000)'))

或

INSERT INTO `tag` (`name`,`loc`) VALUES ('tag name', ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'))

如果我执行自己的 Value()以满足 database/sql 的 Valuer 接口:

If I do a my own Value() to satisfy the database/sql's Valuer interface:

func (g EWKBGeomPoint) Value() (driver.Value, error) {
    log.Println("EWKBGeomPoint value called")
    b := geom.Point(g)
    bp := &b

    floatArr := bp.Coords()
    return fmt.Sprintf("ST_GeomFromText('POINT(%f %f)')", floatArr[0], floatArr[1]), nil
}

Gorm的单引号中包括了包括 ST_GeomFromText()在内的整个值，因此它将不起作用:

The entire value including ST_GeomFromText() is quoted in a single quote from Gorm, and so it won't work:

INSERT INTO `tag` (`name`,`loc`) VALUES ('tag name','ST_GeomFromText('POINT(10.000000 20.000000)')');

我如何使其工作?

我追踪到Gorm代码，最终它进入了 callback_create.go 的 createCallback 函数.在其中检查 if primaryField == nil 是否为真，它进入调用 scope.SQLDB().Exec 的操作，所以我无法进一步跟踪.

I trace into Gorm code, eventually it get's to callback_create.go's createCallback function. Inside it check for if primaryField == nil and it is true, it goes into calling scope.SQLDB().Exec then I failed to trace further.

scope.SQL是字符串 INSERT INTO tag ( name ， loc )VALUES(?，?)和 scope.SQLVars 打印 [标记名{{1 2 [10 20] 0}}] .看起来插值发生在此调用内.

scope.SQL is string INSERT INTOtag(name,loc) VALUES (?,?) and scope.SQLVars prints [tag name {{1 2 [10 20] 0}}]. It looks like interpolation happens inside this call.

这是调用 database/sql 代码吗?

在此处找到了类似的Stackoverflow问题.但是我不明白解决方案.

Found a similar Stackoverflow question here. But I do not understand the solution.

推荐答案

这是另一种方法.使用二进制编码.

Here's another approach; use binary encoding.

根据该 doc ，MySQL使用4个字节存储几何值，以指示SRID(空间参考ID)，后跟该值的WKB(熟知二进制)表示形式.

According to this doc, MySQL stores geometry values using 4 bytes to indicate the SRID (Spatial Reference ID) followed by the WKB (Well Known Binary) representation of the value.

因此，一个类型可以使用WKB编码，并在Value()和Scan()函数中添加和删除四个字节的前缀.在其他答案中找到的go-geom库具有WKB编码包github.com/twpayne/go-geom/encoding/wkb.

So a type can use WKB encoding and add and remove the four byte prefix in Value() and Scan() functions. The go-geom library found in other answers has a WKB encoding package, github.com/twpayne/go-geom/encoding/wkb.

例如:

type MyPoint struct {
    Point wkb.Point
}

func (m *MyPoint) Value() (driver.Value, error) {
    value, err := m.Point.Value()
    if err != nil {
        return nil, err
    }

    buf, ok := value.([]byte)
    if !ok {
        return nil, fmt.Errorf("did not convert value: expected []byte, but was %T", value)
    }

    mysqlEncoding := make([]byte, 4)
    binary.LittleEndian.PutUint32(mysqlEncoding, 4326)
    mysqlEncoding = append(mysqlEncoding, buf...)

    return mysqlEncoding, err
}

func (m *MyPoint) Scan(src interface{}) error {
    if src == nil {
        return nil
    }

    mysqlEncoding, ok := src.([]byte)
    if !ok {
        return fmt.Errorf("did not scan: expected []byte but was %T", src)
    }

    var srid uint32 = binary.LittleEndian.Uint32(mysqlEncoding[0:4])

    err := m.Point.Scan(mysqlEncoding[4:])

    m.Point.SetSRID(int(srid))

    return err
}

使用MyPoint类型定义标签:

Defining a Tag using the MyPoint type:

type Tag struct {
    Name string   `gorm:"type:varchar(50);primary_key"`
    Loc  *MyPoint `gorm:"column:loc"`
}

func (t Tag) String() string {
    return fmt.Sprintf("%s @ Point(%f, %f)", t.Name, t.Loc.Point.Coords().X(), t.Loc.Point.Coords().Y())
}

使用以下类型创建标签

tag := &Tag{
    Name: "London",
    Loc: &MyPoint{
        wkb.Point{
            geom.NewPoint(geom.XY).MustSetCoords([]float64{0.1275, 51.50722}).SetSRID(4326),
        },
    },
}

err = db.Create(&tag).Error
if err != nil {
    log.Fatalf("create: %v", err)
}

MySQL结果:

mysql> describe tag;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   | PRI | NULL    |       |
| loc   | geometry    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


mysql> select name, st_astext(loc) from tag;
+--------+------------------------+
| name   | st_astext(loc)         |
+--------+------------------------+
| London | POINT(0.1275 51.50722) |
+--------+------------------------+

• ( ArcGIS说 4326是最常见的空间参考用于在全球范围内存储参考数据.它是PostGIS空间数据库和GeoJSON标准的默认设置，并且在大多数网络地图库中默认使用.)
• (ArcGIS says 4326 is the most common spatial reference for storing a referencing data across the entire world. It serves as the default for both the PostGIS spatial database and the GeoJSON standard. It is also used by default in most web mapping libraries.)

这篇关于使用Gorm和MySQL处理空间数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！