gorm

https://gorm.io/docs/sql_builder.html

CreateInBatches(poiHnPrds2, 2000) batchSize 过大会hang住，自测2000
db.Table("hn_test2").Omit("Id3").Create(&hnTest) 指定表名
db, _ := db15450.DB();db.Close()

ewkbhexGeom, err := ewkbhex.Encode(c.TheGeom, ewkbhex.NDR)

db.Raw(`INSERT INTO hn_test (the_geom, id) VALUES (?, ?);`, ewkbhexGeom, c.Id).Row()

row := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row()
row.Scan(&name, &age, &email)

// Raw SQL
rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows()
defer rows.Close()
for rows.Next() {
  rows.Scan(&name, &age, &email)

  // do something
}

func NTile(tableName, field string, num int, fileName string, dbConfig config.Config) [][]string {
    db := config.GetDbByCfg(dbConfig)

    sql := fmt.Sprintf("WITH subset AS ( SELECT %s,ntile(%d) "+
        " OVER (ORDER BY %s) AS tile FROM %s ) "+
        " SELECT min(%s) as min,max(%s) as max "+
        " FROM subset "+
        " GROUP BY tile ORDER BY tile;", field, num, field, tableName, field, field)
    fmt.Println(sql)
    rows, _ := db.Raw(sql).Rows()
    defer rows.Close()
    var tiles [][]string
    for rows.Next() {
        var min, max string
        rows.Scan(&min, &max)
        tmp := []string{min, max}
        tiles = append(tiles, tmp)
    }
    WriteJson(tiles, fileName)
    return tiles
}

package datatodb

import (
    "fmt"
    _ "github.com/lib/pq"
    "github.com/qichengzx/coordtransform"
    "poi-hn-go/config"
    "poi-hn-go/models"
    "time"
)

func Edit2Prd(params ...interface{}) string {
    minVal, maxVal := params[0], params[1]
    db15450 := config.GetDbByCfg(config.Db15450)
    db15460 := config.GetDbByCfg(config.Db15460)

    fromSql := "select * from poi_hn_edit_1123 " +
        "where hn_id >= ? and hn_id <= ? limit 100"

    var poiHnPrds []models.PoiHnPrd
    var poiHnPrds2 []models.PoiHnPrd
    db15450.Raw(fromSql, minVal, maxVal).Scan(&poiHnPrds)

    for _, p := range poiHnPrds {
        lon84, lat84 := coordTransform.GCJ02toWGS84(p.Longitude, p.Latitude)
        lon09, lat09 := coordTransform.GCJ02toBD09(p.Longitude, p.Latitude)
        p.LongitudeWgs84, p.LatitudeWgs84 = lon84, lat84
        p.LongitudeBd09, p.LatitudeBd09 = lon09, lat09
        p.HnGeo = models.Geometry{Wkt: fmt.Sprintf("point(%f %f)", p.Longitude, p.Latitude)}
        poiHnPrds2 = append(poiHnPrds2, p)
    }
    db15460.CreateInBatches(poiHnPrds2, 2000)
    time.Sleep(2 * time.Second)
    return "finished"

}

package models

import (
    "context"
    "database/sql/driver"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type Geometry struct {
    Wkt string
}

func (geom Geometry) DataType() string {
    return "geometry"
}

func (geom *Geometry) Value() (driver.Value, error) {
    return geom.Wkt, nil
}

func (geom Geometry) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
    return clause.Expr{
        SQL:  "ST_GeomFromText(?,4326)",
        Vars: []interface{}{geom.Wkt},
    }
}

func (geom *Geometry) Scan(v interface{}) error {
    wkt, ok := v.(string)
    if !ok {
        return nil
    }
    geom.Wkt = wkt
    return nil
}

package models

type PoiHnEdit struct {
    HnId      string  `json:"hn_id" gorm:"column:hn_id;"`
    SourceId  string  `json:"source_id" gorm:"column:source_id;"`
    Address   string  `json:"address" gorm:"column:address;"`
    Longitude float64 `json:"longitude" gorm:"column:longitude;"`
    Latitude  float64 `json:"latitude" gorm:"column:latitude;"`
    ProvName  string  `json:"prov_name" gorm:"column:prov_name;"`
    CityName  string  `json:"city_name" gorm:"column:city_name;"`
    AdName    string  `json:"ad_name" gorm:"column:ad_name;"`
    ProvCode  string  `json:"prov_code" gorm:"column:prov_code;"`
    CityCode  string  `json:"city_code" gorm:"column:city_code;"`
    AdCode    string  `json:"ad_code" gorm:"column:ad_code;"`
    HnGeo     Geometry  `json:"hn_geo" gorm:"column:hn_geo;"`
}

//定义表名
func (PoiHnEdit) TableName() string {
    return "poi_hn_edit"
}