Ruby, SQLite and trigonometric functions

Question

I want to be able to use various trigonometric to find the distance between latitudes and longitudes from ruby using the SQLite database. SQLite doesn't provide trigonometric functions (cos, sin, pow) so I'm looking for how to implement these functions - any ideas?

I know I can use other databases (PostgreSQL, MySQL etc) but I need to use SQLite so no database installation is required. Any ideas as to how to compile extensions for SQLite that I can insert into the sqlite3 gem?

Thanks!

Answer 1

If writing an extension is too much work, what I normally do for this problem is to approximate the distance i want to select (and over estimate a bit) by using a lat/lng bounding box which reduces the sample space significantly, and then iterate over that set in ruby to find the exact set of points within the right distance.

Edit: Looks like someone has already written a library that lets you easily define functions as if they were stored procedures. I don't know if it's a ruby implementation or if it does some trickery to compile it down to C first, so I don't know the performance, but it seems pretty easy to use. https://github.com/copiousfreetime/amalgalite

require 'rubygems'
$: << "../lib"
$: << "../ext"
require 'amalgalite'
db = Amalgalite::Database.new( "mydb.sqlite" )
db.define_function( 'geo_dist' ) do |lat1, lng1, lat2, lng2|
  # Haversine formular here to calculate the distance
end

Here's the full example https://github.com/copiousfreetime/amalgalite/blob/master/examples/define_function.rb