Find all rows around 10km radius with GORM and PostgreSQL

Question

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

Hi there. Just out of curiosity, why did you chose earthdistance over postgis for dealing with spatial data? cheers — Jim Jones, Feb 01 '21 at 10:31
@JimJones Thank you for your question, I chose the "earthdistance" car which I found more suitable and it's officially supported by Postgres. I am open to using PostGIS, or any extension. — eclaude, Feb 01 '21 at 12:12
PostGIS is to my knowledge by far the most suitable extension to deal with spatial data. In this answer you can find an example to compute distances: https://stackoverflow.com/a/51889638/2275388 — Jim Jones, Feb 01 '21 at 13:37

Ezequiel Muns · Accepted Answer · 2021-02-04T08:43:57.957

2

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,
        },
    }).

edited Feb 04 '21 at 08:43

answered Feb 02 '21 at 14:43

Ezequiel Muns

7,492
33
57

Thanks for your answer, with both options I have this error: `ERROR: syntax error at end of input` `SELECT [...] FROM "races" LEFT JOIN "addresses" "StartAddress" ON "races"."start_address_id" = "StartAddress"."id" WHERE earth_box(ll_to_earth(53.3781516,-9.338521), '5000') @> ll_to_earth(addresses.lat, addresses.lng AND "races"."deleted_at" IS NULL ORDER BY races.status DESC` – eclaude Feb 03 '21 at 04:27
There was a typo in my code, see it? The second ll_to_earth was missing a parens. – Ezequiel Muns Feb 03 '21 at 09:04
Thank you so much for your response. I simply had to replace the occurrences "addresses.lat" and "addresses.lng" with "lat" and "lng" if I had an error. – eclaude Feb 04 '21 at 02:31

Find all rows around 10km radius with GORM and PostgreSQL

1 Answers1