Working with spatial data with Gorm and MySQL

user_133747 0
Viewed 2344

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.

My Gorm model:

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"`
}

From what I know, MySQL supports insertion like this:

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

or 

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

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
}

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

How do I make it work?

EDIT 1:

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

Is this calling into database/sql code?

EDIT 2:

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

go-gorm go spatial mysql
2 Answers

Here's another approach; use binary encoding.

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.

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.

For example:

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
}

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

Creating a tag using the type:

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 results:

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

Update: this approach didn't work.

Hooks may let you set the column to a gorm.Expr before Gorm's sql generation.

For example, something like this before insert:

func (t *Tag) BeforeCreate(scope *gorm.Scope) error {

  x, y := .... // tag.Loc coordinates

  text := fmt.Sprintf("POINT(%f %f)", x, y)

  expr := gorm.Expr("ST_GeomFromText(?)", text)

  scope.SetColumn("loc", expr)

  return nil
}
user_39150
Related