当前位置：首页 > 文章列表 > Golang > Go问答 > 在Golang中使用GORM进行地理位置周围搜索

在Golang中使用GORM进行地理位置周围搜索

来源：stackoverflow 2024-03-17 21:48:32 0浏览收藏

在 Go 语言中，GORM 库提供了强大的 ORM 功能，可以轻松进行数据库操作。如果你需要执行复杂的地理位置周围搜索查询，可以利用 GORM 的 SQL 构建器功能。通过构建器，你可以手动编写 SQL 查询，并利用 GORM 的类型安全和语法检查功能，确保查询的正确性。

问题内容

我正在尝试向 gorm 提出特定请求。

这是我使用的表格：

+-------------------+
| tables            |
+-------------------+
| locations         |
| shops             |
| shops_tags        |
| tags              |
+-------------------+

位置表

+------------+------------------+------+-----+---------+----------------+
| field      | type             | null | key | default | extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | no   | pri | null    | auto_increment |
| shop_id    | bigint(20)       | yes  |     | null    |                |
| lat        | decimal(10,8)    | yes  |     | null    |                |
| lng        | decimal(11,8)    | yes  |     | null    |                |
+------------+------------------+------+-----+---------+----------------+

位置模型

type location struct {
    gorm.model
    shopid int64
    shop   shop
    lat    float64 `gorm:"type:decimal(10,8)"`
    lng    float64 `gorm:"type:decimal(11,8)"`
}

商店表

+-------------+------------------+------+-----+---------+----------------+
| field       | type             | null | key | default | extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | no   | pri | null    | auto_increment |
| name        | varchar(180)     | yes  | uni | null    |                |
| active      | tinyint(1)       | yes  |     | 1       |                |
+-------------+------------------+------+-----+---------+----------------+

商店模型

type shop struct {
    gorm.model
    name        string      `json:"name" gorm:"type:varchar(180);unique_index"`
    active      int         `json:"active" gorm:"type:tinyint(1);default:1"`
    tags        []*tag      `json:"tags" gorm:"many2many:shops_tags;"`
    locations   []location  `json:"locations" gorm:"locations"`
}

标记模型

type tag struct {
    gorm.model
    name  string  `json:"name" gorm:"type:varchar(180)"`    
    shops []*shop `json:"shops" gorm:"many2many:shops_tags;"`
}

返回 json 格式查询的处理程序

func getshops(c echo.context) error {
    db := db.dbmanager()

    // get post data (name lat and lng)
    type geo struct {
        lat string `json:"lat" form:"lat" query:"lat"`
        lng string `json:"lng" form:"lng" query:"lng"`
    }

    // bind request body
    g := new(geo)
    if err := c.bind(g); err != nil {
        return c.json(http.statusforbidden, err)
    }

    shops := []model.shop{}

    // totally disordered attempt of a query with `go-gorm` to display the list of nearby shops, sorted by the distance between us.

    db.preload("locations", func(db *gorm.db) *gorm.db {

        // how here use  g.lat and g.lng for this request  

        return db.order("locations.id desc").limit(1)
    }).preload("tag").find(&shops)

    // json
    return c.json(http.statusok, echo.map{
        "shops": &shops,
    })
}

因此，我们有商店，其中有位置，我们希望在位置的 15 公里范围内显示商店。只有最后一个位置有用，这就是为什么我们将结果限制为按降序排列的 1 个。

编辑

感谢@rick james建议我重新表述我的问题，这里是mysql中的查询，如何使其适应go-gorm：

SELECT
shops.id,
    shops.name, (
        (
            6371.04 * ACOS(
                (
                    (
                        COS(
                            (
                                (
                                    PI() / 2
                                ) - RADIANS(
                                    (90 - locations.lat)
                                )
                            )
                        ) * COS(
                            PI() / 2 - RADIANS(90 - -33.73788500)
                        ) * COS(
                            (
                                RADIANS(locations.lng) - RADIANS('151.23526000')
                            )
                        )
                    ) + (
                        SIN(
                            (
                                (
                                    PI() / 2
                                ) - RADIANS(
                                    (90 - locations.lat)
                                )
                            )
                        ) * SIN(
                            (
                                (
                                    PI() / 2
                                ) - RADIANS(90 - -33.73788500)
                            )
                        )
                    )
                )
            )
        )
    ) AS 'distance',
    locations.id AS 'location_id',
    locations.shop_id,
    locations.lat,
    locations.lng,
    locations.created_at
FROM
shops
INNER JOIN locations ON(
    locations.created_at >= '2017-12-13'
    AND(
        (
            6371.04 * ACOS(
                (
                    (
                        COS(
                            (
                                (
                                    PI() / 2
                                ) - RADIANS(
                                    (90 - locations.lat)
                                )
                            )
                        ) * COS(
                            PI() / 2 - RADIANS(90 - -33.73788500)
                        ) * COS(
                            (
                                RADIANS(locations.lng) - RADIANS('151.23526000')
                            )
                        )
                    ) + (
                        SIN(
                            (
                                (
                                    PI() / 2
                                ) - RADIANS(
                                    (90 - locations.lat)
                                )
                            )
                        ) * SIN(
                            (
                                (
                                    PI() / 2
                                ) - RADIANS(90 - -33.73788500)
                            )
                        )
                    )
                )
            )
        )
    ) < '500'
    AND shops.id = (locations.shop_id)
)
WHERE
  shops.active = 1
GROUP BY
  shops.id
ORDER BY
  distance ASC
LIMIT
  100

解决方案

看看这个查询，我肯定会手工制作这个查询，而不依赖任何 orm 来执行查询。参考在gorm数据库实例上使用exec方法ref。

但是... 要使用像 gorm 这样的 orm，可以使用 gorm 的 sql-builder。这是一个应该具有指导意义的示例。请注意，这是针对 sqlite 数据库的，而不是针对 mysql 的，因此所使用的函数不存在。我们主要关心构建查询。

package main

import (
    "fmt"
    "time"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

const (
    layoutiso = "2006-01-02"
)

type shop struct {
    gorm.model
    name      string     `json:"name" gorm:"type:varchar(180);unique_index"`
    active    int        `json:"active" gorm:"type:tinyint(1);default:1"`
    tags      []tag      `json:"tags" gorm:"many2many:shops_tags;"`
    locations []location `json:"locations" gorm:"locations"`
}

type tag struct {
    gorm.model
    name  string `json:"name" gorm:"type:varchar(180)"`
    shops []shop `json:"shops" gorm:"many2many:shops_tags;"`
}

type location struct {
    gorm.model
    shopid int64
    shop   shop
    lat    float64 `gorm:"type:decimal(10,8)"`
    lng    float64 `gorm:"type:decimal(11,8)"`
}

// get post data (name lat and lng)
type geo struct {
    lat float64 `json:"lat" form:"lat" query:"lat"`
    lng float64 `json:"lng" form:"lng" query:"lng"`
}

var distance_calculation = `
       (
           (
                   6371.04 * acos(((cos(((pi() / 2) - radians((90 - locations.lat)))) *
                                    cos(pi() / 2 - radians(90 - ?)) *
                                    cos((radians(locations.lng) - radians(?))))
                   + (sin(((pi() / 2) - radians((90 - locations.lat)))) *
                      sin(((pi() / 2) - radians(90 - ?))))))
               )
           )`

func main() {
    db, err := gorm.open("sqlite3", "test.db")
    if err != nil {
        panic("failed to connect database")
    }
    defer db.close()
    // enable logger, show detailed log
    db.logmode(true)

    // migrate the schema
    db.automigrate(&shop{})
    db.automigrate(&tag{})
    db.automigrate(&location{})


    g := new(geo)
    g.lat = -33.73788500
    g.lng = 151.23526000
    type result struct {
        id         uint
        name       string
        distance   int
        locationid uint
        shopid     uint
        lat        float64
        lng        float64
        createdat  time.time
    }

    date, _ := time.parse(layoutiso, "2017-12-13")
    var t []result
    err = db.table("shops").
        select("shops.id, shops.name, locations.id as 'location_id', locations.shop_id, locations.lat, locations.lng, locations.created_at, "+
            distance_calculation+" as distance\n", g.lat, g.lng, g.lat).
        joins("inner join locations on shops.id = locations.shop_id\n").
        where(&shop{active: 1}).
        where("locations.created_at >= cast(strftime('%s', ?) as int) * 1000", date).
        where(distance_calculation + " < ?", g.lat, g.lng, g.lat, 500).
        group("\nshops.id\n").
        order("locations.id desc\n").
        limit(100).scan(&t).error
    if err != nil {
        panic(err)
    }
    fmt.printf("%+v\n", t)
}

使用 sql builder 构建的查询

(C:/Users/fakename/source/stackoverflow/go/geo-go-gorm/main.go:97)
[2019-10-31 15:36:37]  [1.00ms]  SELECT shops.id, shops.name, locations.id AS 'location_id', locations.shop_id, locations.lat, locations.lng, locations.created_at,
       (
           (
                   6371.04 * ACOS(((COS(((PI() / 2) - RADIANS((90 - locations.lat)))) *
                                    COS(PI() / 2 - RADIANS(90 - -33.737885)) *
                                    COS((RADIANS(locations.lng) - RADIANS(151.23526))))
                   + (SIN(((PI() / 2) - RADIANS((90 - locations.lat)))) *
                      SIN(((PI() / 2) - RADIANS(90 - -33.737885))))))
               )
           ) as distance
 FROM "Shops" inner join locations on shops.id = locations.shop_id
 WHERE ("shops"."active" = 1) AND (locations.created_at >= CAST(strftime('%s', '2017-12-13 00:00:00') AS INT) * 1000) AND (
       (
           (
                   6371.04 * ACOS(((COS(((PI() / 2) - RADIANS((90 - locations.lat)))) *
                                    COS(PI() / 2 - RADIANS(90 - -33.737885)) *
                                    COS((RADIANS(locations.lng) - RADIANS(151.23526))))
                   + (SIN(((PI() / 2) - RADIANS((90 - locations.lat)))) *
                      SIN(((PI() / 2) - RADIANS(90 - -33.737885))))))
               )
           ) < 500
) GROUP BY shops.id
 ORDER BY locations.id DESC
 LIMIT 100

今天关于《在Golang中使用GORM进行地理位置周围搜索》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于的内容请关注golang学习网公众号！

版本声明

本文转载于：stackoverflow 如有侵犯，请联系study_golang@163.com删除