Postgres Location Searching in Ecto

This post will somewhat resemble a post I did on Postgres Full Text Search in Ecto, but instead we’ll go over how to work with locations in Postgres and Ecto. Location-based filtering and ordering is a very common need, and Ecto with Postgres can fill it. For those considering separate services, Elasticsearch or Solr offer similar solutions. We will make use of PostGIS. PostGIS can be used directly with Ecto, but a lot of work can be avoided by using the excellent geo package.

We’ll add that right away:

defp deps do
  [{:geo, "~> 1.0"}]
end

My example will be for a service that searches for nearby restaurants, so we’ll need to create a table to hold our restaurants. I’ve added a GiST index to the geometry field so our future queries will remain fast even with larger datasets.

# priv/repo/migrations/20160702160049_create_restaurant.exs
defmodule LocationBasedSearching.Repo.Migrations.CreateRestaurant do
  use Ecto.Migration
  def up do
    execute "CREATE EXTENSION IF NOT EXISTS postgis"
    create table(:restaurants) do
      add :name, :string
      timestamps
    end
    # Add a field `point` with type `geometry(Point,4326)`.
    # This can store a "standard GPS" (epsg4326) coordinate pair {longitude,latitude}.
    execute("SELECT AddGeometryColumn ('restaurants','point',4326,'POINT',2)")
    execute("CREATE INDEX restaurants_point_index on restaurants USING gist (point)")
  end
  def down do
    drop table(:restaurants)
    execute "DROP EXTENSION IF EXISTS postgis"
  end
end

We don’t need to get too deep into GIS terms and formats, but the gist of the above migration gist is that we added a column that uses SRID 4326. It is the “global reference system for geospatial information and is the reference system for the Global Positioning System (GPS).”

The geo package also implements a Postgrex extension, so we can easily work with the point column, but it needs to be added to the config:

# config/config.exs
use Mix.Config
# General application configuration
config :location_based_searching, LocationBasedSearching.Repo,
  extensions: [{Geo.PostGIS.Extension, library: Geo}]

We’ll also want to implement the Ecto schema for the table, and we can use the Geo.Point type now that the extension has been added. I’ve also added a virtual field that will hold the distance the restaurant is from a specified point.

# web/models/restaurant.ex
defmodule LocationBasedSearching.Restaurant do
  use LocationBasedSearching.Web, :model
  schema "restaurants" do
    field :name, :string
    field :point, Geo.Point
    field :distance, :float, virtual: true
    timestamps()
  end
  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \ %{}) do
    struct
    |> cast(params, [:name, :point])
    |> validate_required([:name, :point])
  end
end

I’m going to set up some seed data to add some restaurants to the database. The blue pin is where I’ll be searching from, but will not be added to the database.

I’ve also included a restaurant (Odd Duck) that is much further away, so I can check our filtering works as expected. The script looks like this:

# priv/repo/seeds.exs
# Script for populating the database. You can run it as:
#
#     mix run priv/repo/seeds.exs
#
# Office
# 43.0384114,-87.9102654
# Alem Ethiopian Village
# 43.0387105,-87.9074701
# Swingin' Door Exchange
# 43.0372896,-87.9082446
# Milwaukee Public Market
# 43.035253,-87.9091676
# Odd Duck
# 43.0020021,-87.9033059
restaurant_params = [
  %{name: "Alem Ethiopian Village", point: %Geo.Point{coordinates: {-87.9074701, 43.0387105}, srid: 4326}},
  %{name: "Swingin' Door Exchange", point: %Geo.Point{coordinates: {-87.9082446, 43.0372896}, srid: 4326}},
  %{name: "Milwaukee Public Market", point: %Geo.Point{coordinates: {-87.9091676, 43.035253}, srid: 4326}}
  %{name: "Odd Duck", point: %Geo.Point{coordinates: {-87.9033059, 43.0020021}, srid: 4326}}
]
Enum.each(restaurant_params, fn(params) ->
  LocationBasedSearching.Restaurant.changeset(%LocationBasedSearching.Restaurant{}, params)
  |> LocationBasedSearching.Repo.insert!()
end)

One very important thing to note is that the coordinate tuple puts the longitude first, and the latitude second. This may be the opposite of what you are used to.

With some data, we can start setting up some queries to filter and order restaurants based on their location. The Geo library comes with helper functions to call some of the spatial type functions, but we’ll use fragments to show what’s going on.

# web/models/restaurant.ex
  def within(query, point, radius_in_m) do
    {lng, lat} = point.coordinates
    from(restaurant in query, where: fragment("ST_DWithin(?::geography, ST_SetSRID(ST_MakePoint(?, ?), ?), ?)", restaurant.point, ^lng, ^lat, ^point.srid, ^radius_in_m))
  end
  def order_by_nearest(query, point) do
    {lng, lat} = point.coordinates
    from(restaurant in query, order_by: fragment("? <-> ST_SetSRID(ST_MakePoint(?,?), ?)", restaurant.point, ^lng, ^lat, ^point.srid))
  end
  def select_with_distance(query, point) do
    {lng, lat} = point.coordinates
    from(restaurant in query,
         select: %{restaurant | distance: fragment("ST_Distance_Sphere(?, ST_SetSRID(ST_MakePoint(?,?), ?))", restaurant.point, ^lng, ^lat, ^point.srid)})
  end

For consistency, we’ll also pass Geo points to our functions. In most cases, a query ordering by distance will be used alongside the filter method as ordering larger datasets by location without filtering out distant points will require a lot of work within the database.

When combined, the query will filter out restaurants outside of our specified radius, and then order by the nearest ones. The third function puts the distance into the Restaurant struct, but it is important to note that it is not indexed, and so calculating distance for a large number of restaurants may be intensive. Here’s a couple examples:

point = %Geo.Point{coordinates: {-87.9079503, 43.0384303}, srid: 4326}
# => %Geo.Point{coordinates: {-87.9079503, 43.0384303}, srid: 4326}
Restaurant.within(Restaurant, point, 400) |> Restaurant.order_by_nearest(point) |> Restaurant.select_with_distance(point) |> Repo.all
# [debug] QUERY OK db=7.6ms decode=0.2ms
# SELECT r0."id", r0."name", r0."point", r0."inserted_at", r0."updated_at", ST_Distance_Sphere(r0."point", ST_SetSRID(ST_MakePoint($1,$2), $3)) FROM "restaurants" AS r0 WHERE (ST_DWithin(r0."point"::geography, ST_SetSRID(ST_MakePoint($4, $5), $6), $7)) ORDER BY r0."point" <-> ST_SetSRID(ST_MakePoint($8,$9), $10) [-87.9079503, 43.0384303, 4326, -87.9079503, 43.0384303, 4326, 400, -87.9079503, 43.0384303, 4326]
[%LocationBasedSearching.Restaurant{distance: 49.93833358, id: 1,
  inserted_at: #Ecto.DateTime<2016-07-02 17:05:42>,
  name: "Alem Ethiopian Village",
  point: %Geo.Point{coordinates: {-87.9074701, 43.0387105}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-07-02 17:05:42>},
 %LocationBasedSearching.Restaurant{distance: 129.07572229, id: 2,
  inserted_at: #Ecto.DateTime<2016-07-02 17:05:42>,
  name: "Swingin' Door Exchange",
  point: %Geo.Point{coordinates: {-87.9082446, 43.0372896}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-07-02 17:05:42>},
 %LocationBasedSearching.Restaurant{distance: 366.89115457, id: 3,
  inserted_at: #Ecto.DateTime<2016-07-02 17:05:42>,
  name: "Milwaukee Public Market",
  point: %Geo.Point{coordinates: {-87.9091676, 43.035253}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-07-02 17:05:42>}]
# Expand search radius to include distant restaurant:
iex(76)> Restaurant.within(Restaurant, point, 5000) |> Restaurant.order_by_nearest(point) |> Restaurant.select_with_distance(point) |> Repo.all
[debug] QUERY OK db=3.9ms decode=0.1ms
SELECT r0."id", r0."name", r0."point", r0."inserted_at", r0."updated_at", ST_Distance_Sphere(r0."point", ST_SetSRID(ST_MakePoint($1,$2), $3)) FROM "restaurants" AS r0 WHERE (ST_DWithin(r0."point"::geography, ST_SetSRID(ST_MakePoint($4, $5), $6), $7)) ORDER BY r0."point" <-> ST_SetSRID(ST_MakePoint($8,$9), $10) [-87.9079503, 43.0384303, 4326, -87.9079503, 43.0384303, 4326, 5000, -87.9079503, 43.0384303, 4326]
[%LocationBasedSearching.Restaurant{distance: 49.93833358, id: 1,
  inserted_at: #Ecto.DateTime<2016-07-02 17:05:42>,
  name: "Alem Ethiopian Village",
  point: %Geo.Point{coordinates: {-87.9074701, 43.0387105}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-07-02 17:05:42>},
 %LocationBasedSearching.Restaurant{distance: 129.07572229, id: 2,
  inserted_at: #Ecto.DateTime<2016-07-02 17:05:42>,
  name: "Swingin' Door Exchange",
  point: %Geo.Point{coordinates: {-87.9082446, 43.0372896}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-07-02 17:05:42>},
 %LocationBasedSearching.Restaurant{distance: 366.89115457, id: 3,
  inserted_at: #Ecto.DateTime<2016-07-02 17:05:42>,
  name: "Milwaukee Public Market",
  point: %Geo.Point{coordinates: {-87.9091676, 43.035253}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-07-02 17:05:42>},
 %LocationBasedSearching.Restaurant{distance: 4068.19584617, id: 4,
  inserted_at: #Ecto.DateTime<2016-09-12 15:46:01>, name: "Odd Duck",
  point: %Geo.Point{coordinates: {-87.9033059, 43.0020021}, srid: 4326},
  updated_at: #Ecto.DateTime<2016-09-12 15:46:01>}]

As the results show, we are filtering and ordering as expected, which is great! These examples can hopefully serve as a basic jumping off point for doing some initial location-based work in Postgres and Ecto. For further reading on PostGIS, the boundless PostGIS intro is a great resource.

Feel free to contact me on Twitter at @mitchellhenke or IRC in #elixir-lang with the same name. If you’d like to work with me, shoot me an email at [email protected].