Distance Searching with Raw Gorm query [SOLVED]

So before I explain here’s my function code

func DistanceSearch(lat float64, lng float64) {
	Db, err := gorm.Open(DATABASE, CREDENTIALS)
	if err != nil {
		log.Panic(err)
	}
	defer Db.Close()
	type DistanceStruct struct {
		ID       int
		street   string
		Lat      float64
		Lng      float64
		Distance float64
	}
	selected := DistanceStruct{}
	Db.Raw("SELECT organization_id, street, lat, lng, ACOS(SIN(RADIANS(lat)) * SIN(RADIANS($1)) + COS(RADIANS($1)) * COS(RADIANS(lng) - RADIANS($2))) * 3959 as distance FROM addresses WHERE ACOS(SIN(RADIANS(lat)) * SIN(RADIANS($1)) + COS(RADIANS(lat)) * COS(RADIANS($1)) * COS(RADIANS(lng) - RADIANS($2))) * 3959 < 50 ORDER BY distance", lat, lng).Scan(&selected)
	fmt.Printf("%v \n", selected)
}

Nothing seems to be wrong with the query structure, but I am getting this console readout:

[35m(pq: input is out of range)

Problem is I’m not sure what this means exactly and Google hasn’t helped much. Any insight would be appreciated.

For anyone curious about what is going on here, I had been trying to implement a great distance search using the basic cosine formula for such. My inputs were returning out of bounds because the arc cosine function was not finishing with a result that was within range of its bounds. I have since changed my methodology and uncovered some bugs from my original conception. Instead of the basic cosine formula, because I couldn’t find a way to normalize the resulting calculations in SQL, I implemented a Haversine formula for calculating the great circle distance.

Here’s what I ended up with that works:

func DistanceSearch(lat float64, lng float64) {
	Db, err := gorm.Open(DATABASE, CREDENTIALS)
	if err != nil {
		log.Panic(err)
	}
	defer Db.Close()
	type DistanceStruct struct {
		OrganizationID int
		Name           string
		Street         string
		Lat            float64
		Lng            float64
		Distance       float64
	}
	selected := DistanceStruct{}
	Db.Raw("SELECT organization_id, street, lat, lng, 2 * 3961 * asin(sqrt((sin(radians((lat - $1) / 2))) ^ 2 + cos(radians(lat)) * cos(radians($1)) * (sin(radians(($2 - lng) / 2))) ^ 2)) as distance FROM addresses GROUP BY organization_id, street, lat, lng HAVING 2 * 3961 * asin(sqrt((sin(radians((lat - $1) / 2))) ^ 2 + cos(radians(lat)) * cos(radians($1)) * (sin(radians(($2 - lng) / 2))) ^ 2)) < 50 ORDER BY distance", lat, lng).Scan(&selected)
	fmt.Printf("%v \n", selected)
}

tl:dr: Math is hard, get the right formula!

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.