Get started with Postgis and Ecto

If you're looking to enhance your Phoenix application with geographical data handling capabilities, PostGIS is an indispensable tool you'll want to consider. This robust Postgres extension facilitates precise calculations between geographic data points, such as longitude and latitude, enabling you to calculate distances between companies with relative ease. In this tutorial, we'll cover how to seamlessly integrate PostGIS with Ecto in your Phoenix application, paving the way for advanced location-based features.

This tutorial is designed to help you incorporate PostGIS into your Phoenix projects, a step that will considerably improve your application's interaction with geographical data. PostGIS enables the direct manipulation of geographic objects within SQL queries, offering a robust solution for spatial data management. Ecto, Elixir's main tool for database interaction and query composition, complements this by providing a powerful and expressive way to work with databases. Throughout this tutorial, we'll walk you through the process of setting up PostGIS with your Phoenix application, configuring Ecto to work with spatial data, and crafting queries to efficiently locate nearby points of interest.

Installing PostGIS on OSX

First things first, we need to ensure PostGIS is installed and ready to go on your system. Open up your terminal and execute the following command:

# Install PostGIS on OSX using Homebrew
brew install postgis

This command uses Homebrew, a very popular package manager for OSX's, to install PostGIS, equipping your system with all necessary components to start working with geographical data in your PostgreSQL database.

If you are on Linux or Windows, you need to find alternative ways to install PostGIS. I have been using OSX for the last 10+ years and dont have a recommended way for other operating systems.

Activating the PostGIS Extension

With PostGIS installed, our next move is to enable the PostGIS extension within our PostgreSQL database. Accomplish this by generating a new migration file within your Phoenix project:

Create the migration at: priv/repo/migrations/20240210063240_enable_postgis_extension.exs

defmodule Tutorial.Repo.Migrations.EnablePostgis do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS postgis;"
  end
end

Execute this migration to seamlessly integrate the PostGIS extension into your database, a crucial step for leveraging geographical features in your application.

Crafting the Companies Schema and Migration

Now, let's focus on constructing a schema to store geographical data. We'll illustrate this with a 'companies' table, which includes essential fields like longitude and latitude.

Migration for 'Companies' Table

Generate the migration for the 'companies' table:

File: priv/repo/migrations/20240210063250_create_companies_migration.exs

defmodule Tutorial.Repo.Migrations.CreateCompanies do
  use Ecto.Migration

  def change do
    create table(:companies) do
      add :name, :string
      add :longitude, :float
      add :latitude, :float

      timestamps()
    end
  end
end

Defining the Schema

Next, define the schema within your Phoenix project:

File: lib/tutorial/companies/company.ex

defmodule Tutorial.Companies.Company do
  use Ecto.Schema
  import Ecto.Changeset

  schema "companies" do
    field :name, :string
    field :longitude, :float
    field :latitude, :float

    timestamps()
  end

  def changeset(company, attrs) do
    company
    |> cast(attrs, [:name, :longitude, :latitude])
    |> validate_required([:name, :longitude, :latitude])
  end
end

Seeding the database with geographical data

Having set up the schema and migration, let's seed our database with some geographical data to work with. For this tutorial, we can just use two companies with two different coordinates.

File: priv/repo/seed.exs

alias Tutorial.Companies.Company
alias Tutorial.Repo

Repo.insert!(%Company{
  name: "Phoenix Framework Headquarters",
  longitude: -112.0740373,
  latitude: 33.4483771
})

Repo.insert!(%Company{
  name: "Elixir Language Office",
  longitude: -122.083851,
  latitude: 37.4220656
})

Execute the seed script to populate your database with these initial company records.

mix run priv/repo/seeds.exs

Running the first spatial query with Ecto

Let's delve into querying our geographical data. We'll develop a module to identify companies within a specified distance from a particular point.

File: lib/tutorial/companies/location_query.ex

defmodule Tutorial.Companies.LocationQuery do
  import Ecto.Query
  alias Tutorial.Repo
  alias Tutorial.Companies.Company

  def get_nearby_companies(longitude, latitude, distance_in_meters) do
    query = from c in Company,
            where: ^fragment("ST_DistanceSphere(geom, ST_MakePoint(?, ?)) < ?", c.longitude, c.latitude, ^distance_in_meters)

    Repo.all(query)
  end
end

This function, that has a longitude, latitude, and a distance in meters, returns a list of companies located within the specified distance.

Wrapping Up

Thats it for this tutorial. I have show how you can easily integrate and use PostGIS into an existing Phoenix application with Ecto.  This journey has equipped you with the knowledge to set up and enable PostGIS, devise a schema for geographical data, seed your database with initial data, and perform spatial queries to locate nearby companies. These capabilities are foundational for creating dynamic, location-aware applications using the power of Elixir and Phoenix.