PostgreSQL DB location query with Fluent 4

user_4309446 0
Viewed 54

I have an application that stores items with a latitude and longitude, I wanted to create a query that filters the items and paginates them by their distance to a given lat/lon pair.

I have read online and a lot of the solutions don't seem feasible within the constraints of fluent.

vapor fluent swift postgresql
2 Answers

You'll want to use PostGIS for any kind of Geometric/Geographic query. Luckily there's a package for that! https://github.com/brokenhandsio/fluent-postgis

You can do filterGeograghyWithin() to find items within a certain distance. Note the geographic queries are better suited for these kind of transformations (and more accurate) as they take into account the curvature of the Earth.

The downside is you'll need to convert your lat/lon columns to a GeographicPoint2D which is the native PostGIS type, but given PostGIS is the de facto standard for this kind of work it's worth doing anyway

user_18237050

The best you could do is to calculate the angular distance (ang) from your given latitude (lat) and longitude (lng), and then select the square region that will include the circle. Then use Pythagoras to filter those lying within the required distance:

let ang2 = pow(ang, 2)
Point.query(on: req.db)
    .filter(\.$lng >= lng - ang).filter(\.$lng <= lng + ang)
    .filter(\.$lng >= lat - ang).filter(\.$lat <= lat + ang).all().flatMap { points in
        let closePoints = points.filter { pow($0.lat - lat, 2) * pow($0.lng - lng, 2) <= ang2) }
        // continue processing
    }
}

EDIT: Following the OPs disclosure that he wants to paginate the results!

Capture the id values of the final set of Points and then filter on this. Something like:

let ang2 = pow(ang, 2)
Point.query(on: req.db)
    .filter(\.$lng >= lng - ang).filter(\.$lng <= lng + ang)
    .filter(\.$lng >= lat - ang).filter(\.$lat <= lat + ang).all().flatMap { points in
        return points.filter { pow($0.lat - lat, 2) * pow($0.lng - lng, 2) <= ang2) }.map { $0.id }
    }.flatMap { ids in
        Point.query(on: req.db).filter(\.$id ~~ ids).paginate().flatMap { points in
             // continue processing
        }
    }
}
user_20701110
Related