How I built a location-based promotional code system that’s both powerful and maintainable with PostGIS and Ecto, read along on how to do it
Ever needed to validate that a user is in the right place at the right time? Maybe you’re building surge pricing zones, geo-fenced promotions, or location-based rewards. I recently faced this exact challenge: building a promotional code system that only works when both pickup and drop-off locations fall within a specific radius of an event venue.
My first instinct was to write raw SQL with PostGIS functions. But then I realised something: why not leverage Ecto composable queries while still getting all the power of PostGIS? The result was a clean, type-safe, and surprisingly performant system that handles thousands of location validations without breaking a sweat.
Let me show you exactly how I built it.
The Challenge: Beyond Simple Location Checking
Traditional location validation might check if a user is within X kilometers of a point. But I needed something more sophisticated:
- Validate promotional codes based on geographic areas
- Ensure both pickup AND destination are within the allowed zone
- Handle complex shapes (not just circular radii)
- Scale to thousands of concurrent validations
- Keep everything maintainable and testable
Here’s what most tutorials would have you write:
SELECT COUNT(*) FROM promo_codes
WHERE ST_Distance_Sphere(event_location, ST_GeogFromText('POINT(77.1025 28.7041)')) < 50000
AND code = 'PROMO123' AND is_active = true;Code language: SQL (Structured Query Language) (sql)But I wanted something that played nicely with Ecto’s query composition and type safety. Here’s what I ended up with:
def location_within_range?(%{"lat" => lat, "lng" => lng}, code) do
point = %Geo.Point{coordinates: {lng, lat}, srid: 4326}
from(p in PromoCode,
where: p.code == ^code and
p.is_active == true and
fragment("ST_DWithin(?, ?, ? * 1000)", p.event_location, ^point, p.radius_km)
)
|> Repo.exists?()
endCode language: Elixir (elixir)Same power, but now it’s composable, type-safe, and testable. Let me show you how to build this step by step.
Setting Up PostGIS with Ecto Dependencies
Add these to your mix.exs
defp deps do
[
{:ecto_sql, "~> 3.10"},
{:postgrex, "~> 0.17"},
{:geo, "~> 3.5"},
{:geo_postgis, "~> 3.4"},
# ... other deps
]
endCode language: Elixir (elixir)PostGIS Types Configuration
Create a custom types module for PostGIS integration:
# lib/my_app/postgrex_types.ex
Postgrex.Types.define(
MyApp.PostgrexTypes,
[Geo.PostGIS.Extension] ++ Ecto.Adapters.Postgres.extensions(),
[]
)Code language: Elixir (elixir)Configure your repo to use these types:
# config/config.exs
config :my_app, MyApp.Repo,
adapter: Ecto.Adapters.Postgres,
username: "postgres",
password: "postgres",
database: "my_app_dev",
hostname: "localhost",
types: MyApp.PostgrexTypes # This enables PostGIS supportCode language: Elixir (elixir)Enable PostGIS Extension
Create a migration to enable PostGIS:
defmodule MyApp.Repo.Migrations.EnablePostgis do
use Ecto.Migration
def up do
execute "CREATE EXTENSION IF NOT EXISTS postgis"
end
def down do
execute "DROP EXTENSION IF EXISTS postgis"
end
endCode language: Elixir (elixir)Designing the Schema
Here’s where PostGIS integration really shines. I can store geographic data directly in my Ecto schema:
defmodule MyApp.PromoCode do
use Ecto.Schema
import Ecto.Changeset
schema "promo_codes" do
field :code, :string
field :description, :string
field :is_active, :boolean, default: true
field :amount, :decimal
field :event_location, Geo.PostGIS.Geometry # PostGIS geometry field
field :radius_km, :decimal
field :expires_at, :utc_datetime
field :starts_at, :utc_datetime, default: &DateTime.utc_now/0
timestamps(type: :utc_datetime)
end
# Here write your changeset
def changeset(promo_code, attrs) do
promo_code
|> cast(attrs, [:code, :description, :amount, :event_location, :radius_km, :expires_at])
|> validate_required([:code, :event_location, :radius_km])
|> validate_number(:radius_km, greater_than: 0)
|> validate_number(:amount, greater_than_or_equal_to: 0)
|> unique_constraint(:code)
end
def active(query \\ __MODULE__) do
now = DateTime.utc_now()
from p in query,
where: p.is_active == true and
p.starts_at <= ^now and
p.expires_at > ^now
end
enddefmodule MyApp.PromoCode do
use Ecto.Schema
import Ecto.Changeset
schema "promo_codes" do
field :code, :string
field :description, :string
field :is_active, :boolean, default: true
field :amount, :decimal
field :event_location, Geo.PostGIS.Geometry # PostGIS geometry field
field :radius_km, :decimal
field :expires_at, :utc_datetime
field :starts_at, :utc_datetime, default: &DateTime.utc_now/0
timestamps(type: :utc_datetime)
end
# Here write your changeset
def changeset(promo_code, attrs) do
promo_code
|> cast(attrs, [:code, :description, :amount, :event_location, :radius_km, :expires_at])
|> validate_required([:code, :event_location, :radius_km])
|> validate_number(:radius_km, greater_than: 0)
|> validate_number(:amount, greater_than_or_equal_to: 0)
|> unique_constraint(:code)
end
def active(query \\ __MODULE__) do
now = DateTime.utc_now()
from p in query,
where: p.is_active == true and
p.starts_at <= ^now and
p.expires_at > ^now
end
endCode language: Elixir (elixir)The corresponding migration:
defmodule MyApp.Repo.Migrations.CreatePromoCodes do
use Ecto.Migration
def up do
create table(:promo_codes) do
add :code, :string, null: false
add :description, :text
add :is_active, :boolean, default: true
add :amount, :decimal, precision: 10, scale: 2
add :event_location, :geometry, null: false # PostGIS geometry column
add :radius_km, :decimal, precision: 8, scale: 3
add :expires_at, :utc_datetime
add :starts_at, :utc_datetime, default: fragment("NOW()")
timestamps(type: :utc_datetime)
end
create unique_index(:promo_codes, [:code])
create index(:promo_codes, [:is_active, :starts_at, :expires_at])
# Spatial index for geographic queries - this is crucial for performance
create index(:promo_codes, [:event_location], using: :gist)
end
def down do
drop table(:promo_codes)
end
endCode language: Elixir (elixir)The Magic: Ecto Fragments with PostGIS
Now for the interesting part. Instead of raw SQL, I am gonna use Ecto fragments to leverage PostGIS functions while keeping everything composable:
Basic Location Validation
defmodule MyApp.PromoCodeValidator do
import Ecto.Query
alias MyApp.{Repo, PromoCode}
@doc """
Checks if coordinates are within a promotional code's valid area.
Returns {:ok, promo_code} if valid, {:error, reason} otherwise.
"""
def validate_location(%{"lat" => lat, "lng" => lng}, code)
when is_number(lat) and is_number(lng) do
# Create PostGIS point (note: PostGIS uses {longitude, latitude} order)
point = %Geo.Point{coordinates: {lng, lat}, srid: 4326}
query =
from p in PromoCode.active(),
where: p.code == ^code and
fragment("ST_DWithin(?, ?, ? * 1000)", p.event_location, ^point, p.radius_km),
select: p
case Repo.one(query) do
%PromoCode{} = promo -> {:ok, promo}
nil -> {:error, :invalid_location_or_code}
end
end
def validate_location(_, _), do: {:error, :invalid_coordinates}
endCode language: Elixir (elixir)Let me break down what’s happening:
%Geo.Point{coordinates: {lng, lat}, srid: 4326}– Creates a PostGIS point. SRID 4326 is the standard GPS coordinate system.fragment("ST_DWithin(?, ?, ? * 1000)", ...)– Uses PostGIS’sST_DWithinfunction through Ecto fragments. The* 1000converts kilometers to meters.PromoCode.active()– Composes with our active scope, showing how Ecto queries remain composable.
Validating Pickup AND Destination
The real challenge was ensuring both locations are valid. Here’s my solution using Elixir’s concurrency:
def validate_ride(%{"code" => code, "pickup" => pickup, "dropoff" => dropoff}) do
# Run validations concurrently or normally however you want
pickup_task = Task.async(fn -> validate_location(pickup, code) end)
dropoff_task = Task.async(fn -> validate_location(dropoff, code) end)
case {Task.await(pickup_task), Task.await(dropoff_task)} do
{{:ok, promo}, {:ok, promo}} ->
{:ok, %{message: "Valid code for both locations", promo: promo}}
{{:error, _}, {:error, _}} ->
{:error, "Both pickup and dropoff are outside the promotion area"}
{{:error, _}, {:ok, _}} ->
{:error, "Pickup location is outside the promotion area"}
{{:ok, _}, {:error, _}} ->
{:error, "Dropoff location is outside the promotion area"}
end
end
# For simpler use cases, a synchronous version:
def validate_ride_sync(%{"code" => code, "pickup" => pickup, "dropoff" => dropoff}) do
with {:ok, promo} <- validate_location(pickup, code),
{:ok, ^promo} <- validate_location(dropoff, code) do
{:ok, promo}
else
{:error, reason} -> {:error, reason}
end
endCode language: Elixir (elixir)Advanced Queries with Multiple Conditions
The beauty of Ecto fragments is composability. Need more complex validation? No problem:
def find_applicable_codes(%{"lat" => lat, "lng" => lng}, user_id) do
point = %Geo.Point{coordinates: {lng, lat}, srid: 4326}
from(p in PromoCode.active(),
left_join: u in PromoUsage, on: u.promo_code_id == p.id and u.user_id == ^user_id,
where: is_nil(u.id) and # User hasn't used this code
fragment("ST_DWithin(?, ?, ? * 1000)", p.event_location, ^point, p.radius_km),
order_by: [desc: p.amount],
limit: 5
)
|> Repo.all()
endCode language: Elixir (elixir)Creating Promotional Codes with Geographic Data
Generating codes with their associated locations is straightforward:
defmodule MyApp.PromoCodeGenerator do
alias MyApp.{PromoCode, Repo}
def create_codes(attrs) do
%{
"count" => count,
"event_location" => %{"lat" => lat, "lng" => lng},
"radius_km" => radius,
"description" => description,
"amount" => amount
} = attrs
# Create PostGIS point
event_location = %Geo.Point{coordinates: {lng, lat}, srid: 4326}
expires_at = DateTime.utc_now() |> DateTime.add(15, :day)
# Generate unique codes
codes = generate_unique_codes(count)
# Batch insert all codes
code_attrs = Enum.map(codes, fn code ->
%{
code: code,
description: description,
amount: Decimal.new(amount),
event_location: event_location,
radius_km: Decimal.new(radius),
expires_at: expires_at,
inserted_at: DateTime.utc_now(),
updated_at: DateTime.utc_now()
}
end)
{inserted_count, _} = Repo.insert_all(PromoCode, code_attrs)
{:ok, %{inserted: inserted_count, codes: codes}}
end
defp generate_unique_codes(count) do
1..count
|> Enum.map(fn _ ->
:crypto.strong_rand_bytes(8)
|> Base.encode32()
|> binary_part(0, 8)
end)
|> Enum.uniq()
end
endCode language: Elixir (elixir)Performance Optimisations
The key to good performance with PostGIS queries is proper indexing. Here are the indexes I use:
# In your migration
def up do
# ... table creation ...
# Spatial index - crucial for geographic queries
create index(:promo_codes, [:event_location], using: :gist)
# Composite index for common query patterns
create index(:promo_codes, [:is_active, :starts_at, :expires_at])
# Covering index for code lookups
create index(:promo_codes, [:code],
where: "is_active = true AND expires_at > NOW()")
endCode language: Elixir (elixir)With these indexes, I can handle thousands of concurrent location validations. Here’s a simple benchmark function:
def benchmark_validations do
coordinates = %{"lat" => 28.7041, "lng" => 77.1025}
code = "TEST123"
{time_microseconds, _result} = :timer.tc(fn ->
1..1000
|> Task.async_stream(fn _ ->
MyApp.PromoCodeValidator.validate_location(coordinates, code)
end)
|> Enum.to_list()
end)
IO.puts "1000 validations took #{time_microseconds / 1000}ms"
endCode language: Elixir (elixir)
Real-World Benefits
After building this system, I discovered several key advantages:
Type Safety: Ecto ensures all parameters are properly typed and escaped. No SQL injection vulnerabilities.
Composability: I can build complex queries step by step:
base_query = PromoCode.active()
geo_filtered =
from p in base_query,
where: fragment("ST_DWithin(?, ?, ?)", p.event_location, ^point, ^radius)
final_query =
from p in geo_filtered,
where: p.amount > ^minimum_discountCode language: Elixir (elixir)Testability: Each part can be tested independently:
test "validates location within radius" do
promo = insert(:promo_code,
event_location: %Geo.Point{coordinates: {77.1025, 28.7041}, srid: 4326},
radius_km: Decimal.new("5.0")
)
# 3km away - should be valid
nearby_location = %{"lat" => 28.7310, "lng" => 77.1205}
assert {:ok, %PromoCode{}} =
PromoCodeValidator.validate_location(nearby_location, promo.code)
endCode language: Elixir (elixir)Maintainability: The code is self-documenting and easy to modify. Adding new geographic constraints is straightforward.
Performance: With proper indexing, the system scales beautifully. PostGIS is incredibly optimized for spatial queries.
Advanced Patterns
Once you have the basics down, you can tackle more complex scenarios:
Custom Shapes (Not Just Circles)
# Store polygons instead of points with radius
field :coverage_area, Geo.PostGIS.Geometry
# Validate against polygon
def within_polygon?(coordinates, polygon) do
point = %Geo.Point{coordinates: {coordinates["lng"], coordinates["lat"]}, srid: 4326}
from(p in PromoCode,
where: fragment("ST_Within(?, ?)", ^point, p.coverage_area)
)
|> Repo.exists?()
endCode language: Elixir (elixir)Distance-Based Discounts
def calculate_distance_discount(coordinates, event_location) do
point = %Geo.Point{coordinates: {coordinates["lng"], coordinates["lat"]}, srid: 4326}
from(p in PromoCode,
where: p.event_location == ^event_location,
select: %{
distance_km: fragment("ST_Distance_Sphere(?, ?) / 1000", ^point, p.event_location),
base_amount: p.amount
}
)
|> Repo.one()
|> case do
%{distance_km: distance, base_amount: amount} ->
# Closer = bigger discount
multiplier = max(0.1, 1.0 - (distance / 10.0))
Decimal.mult(amount, Decimal.from_float(multiplier))
nil ->
Decimal.new("0.0")
end
enddef calculate_distance_discount(coordinates, event_location) do
point = %Geo.Point{coordinates: {coordinates["lng"], coordinates["lat"]}, srid: 4326}
from(p in PromoCode,
where: p.event_location == ^event_location,
select: %{
distance_km: fragment("ST_Distance_Sphere(?, ?) / 1000", ^point, p.event_location),
base_amount: p.amount
}
)
|> Repo.one()
|> case do
%{distance_km: distance, base_amount: amount} ->
# Closer = bigger discount
multiplier = max(0.1, 1.0 - (distance / 10.0))
Decimal.mult(amount, Decimal.from_float(multiplier))
nil ->
Decimal.new("0.0")
end
endCode language: Elixir (elixir)What I Learned
Building this system taught me that you don’t have to choose between PostGIS power and Elixir elegance. Ecto fragments give you both:
- Raw PostGIS functionality when you need complex geographic operations
- Type safety and composability from Ecto’s query system
- Maintainable code that your team can understand and extend
- Performance that scales with proper indexing
The key insight is that Ecto fragments aren’t just an escape hatch for complex SQL — they’re a powerful way to integrate specialised database functions into your application’s type system.
Wrapping Up
Geographic features don’t have to mean abandoning your ORM. With PostGIS and Ecto fragments, you can build sophisticated location-aware applications while keeping your code clean, testable, and maintainable.
The next time you need to work with geographic data in Elixir, consider this approach. You might be surprised by how much you can accomplish while staying within Ecto’s composable query system.
Building location-aware apps in Elixir? I’d love to hear about your experiences and any patterns you’ve discovered. The combination of PostGIS and Ecto opens up some fascinating possibilities!
I hope you have enjoyed this long article. If you liked it, consider buying a coffee here … Thank You
