Following the “why did I make longitude a string” debacle of 2014, adding a radius search to my Rails app was ridiculously simple.

The goal was a simple “what locations are within X metres of these coordinates” search.  In the past, I’ve done this with MySQL by basically putting the great circle calculation into the query, which isn’t pretty.

Using PostgreSQL, it’s a lot easier.  I’ll admit, I made the switch because I was deploying to Heroku, but I’m liking psql a lot and this is just one more reason.

First, you’ll need to add two extensions to your database:

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

(FYI, I seem to recall seeing that the order matters here, but I didn’t take the time to try them the other way because… this one worked.)

Then, just to be nice, add an index (this is all assuming, as in my previous post, that you’ve got a locations table with float fields called longitude and latitude, change as needed for your app)

execute "CREATE INDEX geo_location on locations USING gist(ll_to_earth(latitude, longitude));"

Then you can add a scope for your model:

scope :within_radius, lambda {|latitude, longitude, metres| where("earth_box(ll_to_earth(?, ?), ?) @> ll_to_earth(latitude, longitude)", latitude, longitude, metres) }

And that’s basically it.  You can call Location.within_radius(43.6422125, -79.3744256, 5000) and you’ll have a list of locations within 5 km of downtown Toronto.

As an aside: I’m rather pleased with how I named that parameter metres.  I’ve run into this problem numerous times where the units aren’t clear, especially in time/duration functions (milliseconds? seconds? minutes?) so this way even I can figure it out (at a cost of having to decide between metres and meters, but using the American English version when the USA isn’t on the metric system anyway felt a bit off.)

Alternate title: how to lose that “you done goofed” feeling.

So let’s say you made an update to your table to support longitude and latitude, because you knew you’d need them someday but the push right now was just to add the fields so they could start getting populated.

And let’s go further and say it was item 4892 on your plate, and validating numbers is soooo hard, so you just made them strings:

  def change
    add_column :locations, :longitude, :string, :default => '', :null => false
    add_column :locations, :latitude, :string, :default => '', :null => false
  end

…And that’s great, until you actually want to do something with those, like, oh, run calculations on them, because calculations are done with numbers and you’ve got strings, so you can either push your string agenda forward with a lot of expensive casts, or you can admit there’s a problem and you’ve got to change it.

Unfortunately, a straight-up “change_column :locations, :longitude, :float, :default => 0, :null => false” isn’t going to cut it, at least in PostgreSQL (which we’re using precisely because we’re moving to geo stuff, more on that later.)  Those empty strings are going to fail on the conversion, because it’s not obvious that ” == 0, and you’ll get PG::Error: ERROR:  column “latitude” cannot be cast to type double precision

So you think it’s just a matter of changing the empty strings to zeros ahead of time, like “update locations set longitude=’0′ where longitude=””? Close, but nope.  PostgreSQL needs a hint, which means you’ve got to drop down to raw SQL:

execute("ALTER TABLE locations ALTER COLUMN latitude TYPE float USING (latitude::float)")

And that leads to “PG::Error: ERROR:  default for column “latitude” cannot be cast to type double precision” – it doesn’t look like you can alter a column type and default in the same statement, so you’ve got to break them up.

execute("ALTER TABLE web_listings ALTER COLUMN latitude drop default")
execute("ALTER TABLE locations ALTER COLUMN latitude TYPE float USING (latitude::float)")
execute("ALTER TABLE locations ALTER COLUMN latitude set default 0")

And that seems to work.  Now you’ve got floats in your database and can start doing geo math – more on that in the next post.