This post is the second of a 2-part series about Rails and PostGIS. Read Part 1.

Setting up model fields

Once PostGIS is all set-up, you’ll need to integrate it with one or more of your models. The activerecord-postgis-adapter gem allows you to write spatial migrations using the Rails DSL – refer to the README for a good overview. PostGIS has different primitives for the various kinds of spatial shapes you can store, which are commonly represented in the Well-known text (WKT) format:

• POINT – a single latitude/longitude coordinate
• LINESTRING – an ordered collection of POINTs
• POLYGON – a LINESTRING that joins to itself, with one or more optional LINESTRING holes within its boundaries (think a lake with islands)

Each type also has an accompanying collection – MULTIPOINT, MULTILINESTRING, MULTIPOLYGON – as well as a generic GEOMETRY type which accepts any of the above types.

Most geospatial apps will probably use a simple POINT column, since this is what is returned from geocoding services like Google’s API.

Geometry or Geography?

The next decision is how you’re going to store your geospatial data. PostGIS deals with two types of data – geometric and geographic. Geometric refers to a projected coordinate system, whereby calculations (distance, area, locating items etc.) are done in 2-dimensional cartesian space. Geographic columns, on the other hand, use spherical coordinates and do all their calculations on a sphere (which is more complicated, and thus slower). Since geographic columns are a recent addition to PostGIS, there are fewer available functions for them when compared to geometric columns.

Reading around, the general rule of thumb is to stick to geometric columns if your data is fairly localised within the same city/state/region. If you’re dealing with coordinates across the globe, geographic columns are probably your best bet. PostGIS is able to cast between the two, so using one doesn’t necessarily preclude you from using functions designed for another – you just need to be careful with the conversion.

In our case, we went for a geographic column for the app, since our needs were comparatively simple and it meant we didn’t have to worry about projections.

Geocoding in Rails

Now we have a way of storing our geospatial data, we need to get it from somewhere. This is the process of geocoding – translating a given address into a coordinate. Google is far and away the most popular geocoding service, so we’ll talk about their services from now on, but be aware there are alternatives.

This is where the fantastic geocoder gem comes into play. This gives a unified interface to just about every geocoding service out there, as well as some SQL functions to generate some basic spatial queries (using database trigonometric functions) if you don’t want/need to use PostGIS.

Since Geocoder relies on the presence of two fields for storing your latitude/longitude, we need some extra setup in our model:

class User
  GEO_FACTORY = RGeo::Geographic.spherical_factory(srid: 4326)

  set_rgeo_factory_for_column :coordinates, GEO_FACTORY

  geocoded_by :address do |record, results|
    result = results.first

    record.address     = result.address # Store the address used for geocoding
    record.coordinates = GEO_FACTORY.point(result.latitude, result.longitude)
  end
end

# The migration for this model:
create_table :users do |t|
  t.string :address                      # The address to geocode
  t.point :coordinates, geographic: true # Our PostGIS column
end

The Geocoder .geocoded_by method takes a block where we can customise exactly how we store the results from Google. RGeo uses a factory pattern to generate spatial objects (so that they have the correct projection), so we create a RGeo::Geographic factory which generates spherical coordinates (the SRID 4326 refers to this coordinate system). We store this in a constant, so that we can set this as the factory for our PostGIS column, as well as using it to produce compatible spatial objects that can be serialised to and from the database.

Finding points within a shape

For the app we’re building, finding relevent points was complicated by the fact that the app deals with journeys, which always have a origin and destination. We couldn’t use a simple bounding box or radius query, since this would include irrelevant journeys. Instead, we used the very useful ST_Buffer function, which generates an expanded version of the shape given to it. We thus constructed a LINESTRING from the origin and destination latitude/longitude points, and used this to generate a sausage shape with ST_Buffer, which we could then feed into our queries using ST_Covers.

For example, to find journeys similar to a journey from Bristol (lat: 51.45, long: -2.583333) to London (lat: 51.507222, long: -0.1275), we can use the following query (where Journey#start and Journey#finish are PostGIS geographic POINT columns):

SELECT "journeys".* FROM "journeys"
WHERE
  (ST_Covers(
    ST_Buffer(
      ST_GeographyFromText('LINESTRING(-2.583333 51.45, -0.1275 51.507222)'),
    20000),
  "journey"."start"))
 AND
  (ST_Covers(
    ST_Buffer(
      ST_GeographyFromText('LINESTRING(-2.583333 51.45, -0.1275 51.507222)'),
    20000),
  "journey"."finish"));

Here’s how the buffer looks on the map:

To produce this query, we used a small(ish) class:

class JourneyMatcher
  def initialize(start_coordinate, finish_coordinate)
    @start_point  = geo_factory.point(*start_coordinate)
    @finish_point = geo_factory.point(*finish_coordinate)
  end

  def find
    Journey.where(start_matches.and finish_matches)
  end

  protected

  def start_matches
    covers(Journey.arel_table[:start])
  end

  def finish_matches
    covers(Journey.arel_table[:finish])
  end

  def line_string
    @line_string ||= geography_from_text(geo_factory.line_string [@start_point, @finish_point])
  end

  def buffer
    line_string.st_buffer(20_000) # distance in metres
  end

  def covers(column)
    buffer.st_function(:ST_Covers, column)
  end

  def geo_factory
    @geo_factory ||= RGeo::Geographic.spherical_factory(srid: 4326)
  end

  def geography_from_text(spatial_object)
    Arel.spatial(spatial_object.as_text).st_function(:ST_GeographyFromText)
  end
end

And call it as follows:

# PostGIS requires coordinates in x, y order (i.e. longitude, latitude)
matcher = JourneyMatcher.new [-2.583333, 51.45], [-0.1275, 51.507222]
matcher.find # => array of matching journeys

How it works

To start, we create a pair of RGeo point objects from the arrays given, using a geographic factory:

def initialize(start_coordinate, finish_coordinate)
  @start_point  = geo_factory.point(*start_coordinate)
  @finish_point = geo_factory.point(*finish_coordinate)
end

We then use these points to create a line string object:

geo_factory.line_string [@start_point, @finish_point]

Then convert it to WKT using the following helper:

def geography_from_text(spatial_object)
  Arel.spatial(spatial_object.as_text).st_function(:ST_GeographyFromText)
end

Arel.spatial is provided by rgeo-activerecord, and wraps the WKT in a RGeo::ActiveRecord::SpatialConstantNode that we can call further PostGIS/RGeo method on, allowing us to compose functions:

Arel.spatial("some text").st_function(:one).st_function(:two).to_sql # => "two(one('some text'))"

Several functions (like st_buffer) are available as shortcut methods and can be called directly, but anything else can be called using the generic st_function method.

Now we have our line string in WKT form, we can produce the SQL to generate a buffer (here we use a diameter of 20 km):

def buffer
  line_string.st_buffer(20_000) # distance in metres
end

We then chain onto the buffer SQL to produce the SQL for the ST_Covers function

#
def covers(column)
  buffer.st_function(:ST_Covers, column)
end

We can then feed this method a column (as an Arel::Attributes::Attribute), which we get from the Journey model’s .arel_table method (we do this for each column):

def start_matches
  covers(Journey.arel_table[:start])
end

def finish_matches
  covers(Journey.arel_table[:finish])
end

Finally, we give our Arel AST to ActiveRecord to query the database:

def find
  Journey.where(start_matches.and finish_matches)
end

Photo by Emm Enn on Flickr