Swift Vapor query filtering and sorting by distance

I'm working on the swift vapor project. I need to filter and sort users by distance(it should not exceed 100 kilometers and the first user should be the closest). I have the coordinates latitude and longitude. I need to do this specifically with query methods filter and sort for good optimization. How can I do this?

I'm using PostgreSQL db.

func getAll(_ req: Request, arguments: UserFilter) async throws -> [User] {
  let user = try req.auth.require(User.self)
  var query = User.query(on: req.db)

  arguments. filter(&query)

  if let lat = user.latitude, let lon = user.longtitude {
    query. filter(
      User.self, 
      field: KeyPath<Schema, QueryableProperty>, // not yet provided
      method: DatabaseQuery.Filter.Method,       // not yet provided
      value: Decodable & Encodable)              // not yet provided
    )
  }
}

return try await query.all()

@OptionalField(key: "latitude")
var latitude: Float?

@OptionalField(key: "longtitude")
var longtitude: Float?

I'm not sure you are able to do this directly using the ORM if you're keen on keeping Floats. If you want to use the ORM I'd say your best bet is https://github.com/brokenhandsio/fluent-postgis

If you want to keep Floats you can use the Pythagorean Theorem in two manners:

1. Drop down do raw SQL:

let query = """
SELECT * FROM your_table 
ORDER BY SQRT(POW(111.1 * (latitude - \(bind: lat)), 2) + 
             POW(111.1 * (longitude - \(bind: lon)) * COS(latitude / 57.3), 2))
"""

2. Sort them after querying

.map { models in
   return models.sorted { model1, model2 in
       let distance1 = sqrt(pow(111.1 * (model1.latitude - lat), 2) + 
                                pow(111.1 * (model1.longitude - lat) * cos(model1.latitude / 57.3), 2))
       let distance2 = sqrt(pow(111.1 * (model2.latitude - lat), 2) + 
                                pow(111.1 * (model2.longitude - lon) * cos(model2.latitude / 57.3), 2))
       return distance1 < distance2
   }

Where:

• 111.1 is an approximation of the number of Kms per degree of latitude. (change to 69.1 if you're using miles)
• (latitude - \(lat)) calculates the difference in latitudes
• COS(latitude / 57.3) is an approximation to adjust the longitude difference for the curvature of the Earth. 57.3 is approximately the number of degrees in one radian (180/π), so this is converting the latitude from degrees to radians
• (longitude - \(lon)) calculates the difference in longitudes

Be aware that this theorem is approximating the distance so at long distances it might not be particularly precise!