Dan Chak’s Blog

To help proliferate the idea that the database is very much a part of your Rails app, I’m going to be posting useful tidbits of PL/pgSQL that can help you application go and go fast. Feel free to send in your own PL/pgSQL snippets and I’ll post them here as well.

I find that when folks say Ruby/Rails is slow, they’re doing things in Ruby/Rails application code that they shouldn’t be. The following PL/pgSQL function will compute an approximate distance between two sets of latitude/longitude pairs.

create or replace function miles_between_lat_long(
  lat1 numeric, long1 numeric, lat2 numeric, long2 numeric
) returns numeric
language 'plpgsql' as $$
declare
  x numeric = 69.1 * (lat2 - lat1);
  y numeric = 69.1 * (long2 - long1) * cos(lat1/57.3);
begin
  return sqrt(x * x + y * y);
end
$$;

Why is this useful? Well, if your web application needs to find distances, such as “drug stores within 10 miles of zip code 02139,” then you can construct a single SQL query that will give you the answer. This can be far more efficient than loading the entire zip code database in memory and trudging through the data in the application layer, which may not be optimized for doing these sorts of computations. For example, assume you’ve got a table drug_stores with latitude and longitude columns, and a table zip_codes that has the latitude and longitude for each zip code in the United States. To find the drug stores within 10 miles of 02139, you issue the following query:

select *
  from drug_stores d,
       zip_codes z
 where z.zip_code = '02139'
   and miles_between_lat_long(d.latitude, d.longitude, z.latitude, z.longitude) < 10;