Tutorial
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.