The SQL code generated by Graticule's spherical distance computation is insufficient and can lead to NULL distances in edge cases.

UI/UX Design by

We make sure that your target audience has the best possible experience with your digital product. You get:

• Design tailored to your audience
• Proven processes customized to your needs
• An expert team of experienced designers

Read more Show archive.org snapshot

The reason for this is that MySQL is performing several sine and cosine computations which can result in slight rounding errors -- which is usually okay. Rarely, though, for the exact center of the given circle, it is then asked to compute the arc cosine of a result like 1.0000000001.

Since this is undefined, MySQL correctly returns NULL (instead of the expected distance of 0 for the circle's center).

The solution to this problem is to discard rounding errors by forcing any results into bounds [-1; 1] before computing the arc cosine on it. Put the following into an initializer like config/initializers/graticule_spherical_to_sql_without_nulls.rb which patches Graticule's to_sql Show archive.org snapshot method:

Graticule::Distance::Spherical.class_eval do
  def self.to_sql(options)
    options = {
      :units => :miles,
      :latitude_column => 'latitude',
      :longitude_column => 'longitude'
    }.merge(options)
    %{
      (ACOS(
        GREATEST(-1,
          LEAST(1,
            SIN(RADIANS(#{options[:latitude]})) *
            SIN(RADIANS(#{options[:latitude_column]})) +
            COS(RADIANS(#{options[:latitude]})) *
            COS(RADIANS(#{options[:latitude_column]})) *
            COS(RADIANS(#{options[:longitude_column]}) - RADIANS(#{options[:longitude]}))
          )
        )
      ) * #{Graticule::Distance::EARTH_RADIUS[options[:units].to_sym]})
    }.gsub("\n", '').squeeze(" ")
  end
end