CODEWITHSUNDEEP
sqlpostgresqlgogo-gorm
eclaude
eclaude

Reputation: 885

Find all rows around 10km radius with GORM and PostgreSQL

I would like to list the races within a radius of 10km around the StartAddress (join table Address)

This is how my models are written:

    type (
        // Race model
        Race struct {
            Base // ID, CreatedAt, UpdatedAt, DeletedAt
            Title          string            `json:"title" gorm:"title;"`
            StartAddress   Address           `json:"startaddress" gorm:"foreignKey:StartAddressID"`
            StartAddressID string            `json:"start_address_id" gorm:"type:char(36);start_address_id;"`
            EndAddress     Address           `json:"endaddress" gorm:"foreignKey:EndAddressID"`
            EndAddressID   string            `json:"end_address_id" gorm:"type:char(36);end_address_id;"`
            StartDate      time.Time         `json:"start_date" gorm:"start_date;"`
            EndDate        time.Time         `json:"end_date" gorm:"end_date;"`
        }

        Address struct {
            Base // ID, CreatedAt, UpdatedAt, DeletedAt
            Street  string  `json:"street" gorm:"street;"`
            Zipcode string  `json:"zipcode" gorm:"zipcode;"`
            City    string  `json:"city" gorm:"city;"`
            Lat     float64 `json:"lat" gorm:"lat;"`
            Lng     float64 `json:"lng" gorm:"lng;"`
        }
    )

To query my database I use GORM v2

I don't know how to write the query that uses the cube & earthdistance extentions with PostgreSQL through GORM.

I have already added the extensions with :

CREATE EXTENSION cube;

and

CREATE EXTENSION earthdistance;

Like that but with the GORM syntax with the sort by distance :

SELECT * FROM races
INNER JOIN addresses ON addresses.id = races.start_address_id
AND earth_box(ll_to_earth(48.8589507,2.2770205), 5000) @> ll_to_earth(addresses.lat, addresses.lng)
ORDER BY races.status DESC
LIMIT 100

Upvotes: 0

Views: 675

Answers (1)

Ezequiel Muns
Ezequiel Muns

Reputation: 7762

The query that you've got there can be done in Gorm like this:

var res []Race

db.Model(&Race{}).
    Joins("StartAddress").
    Where("earth_box(ll_to_earth(?,?), ?) @> ll_to_earth(addresses.lat, addresses.lng)", ptLat, ptLng, dist).
    Order("races.status DESC").
    Limit(100).
    Find(&res)

To get the order by distance, you'll need to replace the Order(...). with:

    Clauses(clause.OrderBy{
        Expression: clause.Expr{
            SQL: "earth_distance(ll_to_earth(?,?), ll_to_earth(addresses.lat, addresses.lng)) ASC", 
            Vars: []interface{}{ptLat, ptLng}, 
            WithoutParentheses: true,
        },
    }).

Upvotes: 2

Related Questions