sqlite

Custom function in SQLite with fmdb

par

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)
        return
    }
    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!