Custom function in SQLite with fmdb

I’ve used Gus Mueller’s fmdb SQLite wrapper in most of my iOS projects and I’m in the process of migrating an app from Objective-C to Swift.

In this app, I needed a custom function for SQLite to compute the Haversine distance (giving great-circle distances between two points on a sphere from their longitudes and latitudes)

In all its glory*, here’s how I did it:

db.makeFunctionNamed("distance", arguments: 4) { context, argc, argv in
    guard db.valueType(argv[0]) == .float || db.valueType(argv[1]) == .float || db.valueType(argv[2]) == .float || db.valueType(argv[3]) == .float else {
        db.resultError("Expected double parameter", context: context)
    let lat1 = db.valueDouble(argv[0])
    let lon1 = db.valueDouble(argv[1])
    let lat2 = db.valueDouble(argv[2])
    let lon2 = db.valueDouble(argv[3])

    let lat1rad = DEG2RAD(lat1)
    let lat2rad = DEG2RAD(lat2)

    let distance = acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1

    db.resultDouble(distance, context: context)

db is an FMDatabase, obviously.

This is how I use it:

let rs: FMResultSet? = db.executeQuery("SELECT ID, NO_BH, LAT, LONG, distance(?, ?, LAT, LONG) as distance FROM bh ORDER BY distance LIMIT 50", withArgumentsIn: [location.coordinate.latitude, location.coordinate.longitude])

Which gives me the 50 nearest points from location.

*Any improvement, remark greatly appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *