Defining a custom sqlite function

[aidaan]

I have a table that includes GPS locations on each row (in columns latitude and longitude), and I wanted to sort these items by the distance to the users location. I could load all the rows into memory and sort them manually, but I wanted to try to have this sorting accomplished in SQL. The standard build of SQLite does not include POW or SQRT functions, so I can't use them for this calculation. But I was able to create a custom SQLite function to specifically compute this distance, and wanted to pass this along:

private static func createMapDistanceFunction(in db: Database) {
    let success = sqlite3_create_function(db.sqliteConnection, "MAP_DISTANCE", 4, SQLITE_UTF8, nil, { context, argc, argv in
        guard argc == 4, let argv = argv else {
            sqlite3_result_error(context, "Invalid arguments to MAP_DISTANCE function", SQLITE_ERROR)
            return
        }
        
        let point1 = CLLocationCoordinate2D(latitude: sqlite3_value_double(argv[0]),
                                            longitude: sqlite3_value_double(argv[1]))
        let point2 = CLLocationCoordinate2D(latitude: sqlite3_value_double(argv[2]),
                                            longitude: sqlite3_value_double(argv[3]))
        let distance = MKMapPoint(point1).distance(to: MKMapPoint(point2))
        
        sqlite3_result_double(context, distance)
    }, nil, nil)

    assert(success == SQLITE_OK, "failed to create MAP_DISTANCE function")
}

This creates the MAP_DISTANCE function which accepts 4 arguments: the latitude and longitude for the two points to compare. It uses the distance(to:) method on MKMapPoint, because this apparently takes into account the curvature of the earth. With this I can construct a query using this function:

let rows = try dbPool.read { db in
    try Row.fetchAll(db, sql: "SELECT * FROM table ORDER BY MAP_DISTANCE(latitude, longitude, \(userLocation.latitude), \(userLocation.longitude))")
}

I honestly don't know if this is more or less performant than sorting manually. But it's nice to have all of this logic handled in the query.