Mysql地理区域位置储POLYGON,ST_PolygonFromText,ST_GeomFromText

go 语言 mysql7以上

数据表字段类型 polygon

CREATE TABLE `t_area` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `city_code` varchar(255) DEFAULT NULL,
  `area_name` varchar(64) DEFAULT NULL,
  `time_type` tinyint(2) DEFAULT '1' COMMENT '1:每天 2:区分工作日/周末',
  `status` tinyint(2) DEFAULT NULL,
  `region` polygon DEFAULT NULL,
  `tenant_id` bigint(32) DEFAULT NULL,
  `created_by` bigint(32) DEFAULT NULL,
  `updated_by` bigint(32) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4;

多表插入 需要取出自增id 只好先插入后更新了 如果是插入时保存 polygon 数据只能拼接语句 暂时没想到其他方法 保存数据

func SaveArea(region string, area Area, times []TaxiTime) (uint, error) {
	//createTime := time.Now()
	err := database.MysqlInstance().Debug().Transaction(func(tx *gorm.DB) error {
		//存线路基础信息和运营信息
		//db := tx.Exec("INSERT INTO t_area(area_name,city_code,region,time_type,status,"+
		//	"tenant_id,created_by,updated_by,created_at,updated_at)"+
		//	"VALUES(?,?,ST_GeomFromText('POLYGON(("+region+"))'),?,?,?,?,?,?,?)",
		//	area.AreaName, area.CityCode, area.TimeType, area.Status,
		//	area.TenantId, area.CreatedBy, area.UpdatedBy, createTime, createTime)
		//db := tx.Raw("INSERT INTO t_area(area_name,city_code,region,time_type,status,"+
		//	"tenant_id,created_by,updated_by,created_at,updated_at)"+
		//	"VALUES(?,?,?,?,?,?,?,?,?,?) ",
		//	area.AreaName, area.CityCode, area.Region, area.TimeType, area.Status,
		//	area.TenantId, area.CreatedBy, area.UpdatedBy, createTime, createTime).Scan(&areaId)
		//area.CreatedAt = createTime
		//area.UpdatedAt = createTime

		//db := tx.Exec("INSERT INTO t_area(area_name,city_code,region,time_type,status,tenant_id,created_by,updated_by,created_at,updated_at)"+
		//	"VALUES(?,?,?,?,?,?,?,?,?,?);SELECT LAST_INSERT_ID();",
		//	area.AreaName, area.CityCode, area.TimeType, area.Status,
		//	area.TenantId, area.CreatedBy, area.UpdatedBy, createTime, createTime).Scan(&areaId)
		db := tx.Create(&area)
		if db.Error != nil {
			logger.Sugar.Errorf("SaveArea Create area errr:%s", db.Error.Error())
			return db.Error
		}

		//val := fmt.Sprintf(`ST_GeomFromText('POLYGON((%s))')`, region)
		//fmt.Println(val)
		//db1 := tx.Model(&Area{}).Where("id=?", area.ID).UpdateColumn("region", val)
		db1 := tx.Exec("Update  t_area set region=ST_GeomFromText('POLYGON(("+region+"))') where id=?", area.ID)

		if db1.Error != nil {
			logger.Sugar.Errorf("SaveArea Update region errr:%s", db1.Error.Error())
			return db1.Error
		}

		for i, _ := range times {
			times[i].AreaId = area.ID
			//times[i].CreatedAt = createTime
			//times[i].UpdatedAt = createTime
		}
		//保存线路和站点的关系
		db = tx.Model(&TaxiTime{}).CreateInBatches(&times, len(times))
		if db.Error != nil {
			logger.Sugar.Errorf("SaveArea CreateInBatches errr:%s", db.Error.Error())
			return db.Error
		}
		return nil
	})
	return area.ID, err
}

校验坐标点是否在区域范围中

func CheckPointExistsById(tenantId uint, id uint, gcjLon, gcjLat float64) int {
   var exist int
   result := database.MysqlInstance().Model(&Area{}).Where("id = ? and tenant_id=?  ", id, tenantId).Select(" ST_Contains(region, ST_GeomFromText('POINT(" + fmt.Sprintf("%f", gcjLon) + " " + fmt.Sprintf("%f", gcjLat) + ")')) AS exist ").Scan(&exist)
   if result.Error != nil {
      logger.Sugar.Errorf("CheckPointExistsById db.Error:%s ", result.Error)
      return exist
   }

   return exist
}

校验区域是否重叠 polygon :="116.411226 39.969334, 116.411138 39.972436, 116.407767 39.972346, 116.407825 39.969311, 116.411226 39.969334"

func QueryTaxiAreaIntersects(id, tenantId uint, polygon string) int64 {
	var count int64
	result := database.MysqlInstance().Model(&Area{}).Where("id <> ? and tenant_id =?  and ST_Intersects(region,ST_PolygonFromText('POLYGON(("+polygon+"))')) ", id, tenantId).Count(&count)
	//result := database.MysqlInstance().Select("SELECT  *  FROM t_area where  ST_Intersects(region,ST_PolygonFromText('POLYGON((116.411226 39.969334, 116.411138 39.972436, 116.407767 39.972346, 116.407825 39.969311, 116.411226 39.969334))'));").Find(&area, "tenant_id in ?  and city_code=? ", tenantIds, cityCode)
	if result.Error != nil {
		logger.Sugar.Errorf("QueryAreaIntersects db.Error:%s ", result.Error)
		return int64(1)
	}
	return count
}