Ecto & PostGiS
performance and functionality
08-11-2018
About Me
Piotr Rybarczyk
github
Just ex-geodesy student, who become programmer. As hobby... base stuff like coding, playing games and listen to music. Sometimes swimming and skiing.
What is Ecto?
It's a database wrapper and language integrated query for Elixir
Why we need it here?
It allows us to write clean, maintainable SQL queries on steroids and write it fast.
Also, it allows to map SQL data into structs, and not only plain data like strings but also GIS data like points or lines.
What is GIS?
A geographic information system (GIS) is a system designed to capture, store, manipulate, analyze, manage, and present spatial or geographic data.
Why we need it here?
It allows us to do all operations listed above with spatial/temporal/geographic data and with proper connections between them it makes "real" physical location on Earth.
When/how/why we use GiS
It can be and it is used in "spatial" data systems around the world
Storing marketing information? Yes, it is.
Field investigation and analysis? Yep...
Nearest pub with beer? Of course...
What is PostGiS?
Its an OpenSource plugin to PostgreSQL which is adding support for GiS data.
Why we need it here?
It allows us to save, search, manipulate data in SQL database making it true spatial database management system. And we can do it using just SQL with some additional functions.
Also it supports Multi-dimensional spatial indexing!
Where it comes together?
- geo - A collection of GIS functions for Elixir
- geo_postgis - Postgrex extension for PostGIS
- topo - A Geometry library for Elixir that calculates spatial relationships between two geometries
Installation
defmodule PostGisSample.Mixfile do
defp deps do
[
# ....
{:geo, "~> 3.0", override: true},
{:geo_postgis, "~> 2.0"},
{:topo, "~> 0.1.0"}
]
end
end
Creating database table
defmodule PostGisSample.Repo.Migrations.AddLocationsTable do
use Ecto.Migration
def up do
execute "CREATE EXTENSION IF NOT EXISTS postgis"
create table(:locations) do
add :name, :string
add :latitude, :decimal, precision: 9, scale: 7
add :longitude, :decimal, precision: 9, scale: 7
add :geom, :geometry
end
execute "CREATE INDEX idx_locations_geom ON locations using GIST(geom)"
end
def down do
execute "DROP INDEX idx_locations_geom;"
drop table(:locations)
execute "DROP EXTENSION IF EXISTS postgis"
end
end
And now we would like to save some data.
def save!(name, longitude, latitude) do
name
|> build_struct(longitude, latitude)
|> PostGisSample.Repo.insert!()
end
defp build_struct(name, long, lat) do
%PostGisSample.Location{
name: name,
longitude: long,
latitude: lat,
geom: %Geo.Point{
coordinates: {long, lat},
srid: 4326
}
}
end
We would like to find our data right now.
def find_location(long, lat, radius) do
long
|> build_point(lat)
|> build_query(radius)
|> PostGisSample.Repo.one()
end
defp build_point(long, lat) do
%Geo.Point{
coordinates: {long, lat},
srid: 4326
}
end
defp build_query(center, radius) do
import Ecto.Query
from(
locations in PostGisSample.Location,
where:
fragment(
"ST_DWithin(geom::geography, ?, ?)",
^center,
^radius
)
)
end
Power of macros
defmacro in_radius(column, center, radius) do
quote do
fragment("ST_DWithin(?::geography, ?, ?)",
unqote(column),
unqote(center),
unqote(radius))
end
end
Power of macros
def find_location(long, lat, radius) do
long
|> build_point(lat)
|> build_query(radius)
|> PostGisSample.Repo.one()
end
defp build_point(long, lat) do
%Geo.Point{
coordinates: {long, lat},
srid: 4326
}
end
defp build_query(center, radius) do
import Ecto.Query
from(
locations in PostGisSample.Location,
where: in_radius(locations.geom, center, radius)
)
end
And we can build structs from data
def box2d() do
import Ecto.Query
query = from(
l in PostGisSample.Location,
select: %{
box: fragment("ST_AsGeoJSON(ST_Extent(?), 15, 1) AS box", l.geom)
}
)
query
|> PostGisSample.Repo.one()
|> Map.get(:box)
|> Poison.decode!()
end
What is Bounding Box?
Other Databases?
For example ElasticSearch or SOLR
What interesting I found doing this presentation.
Check this guy: Topi Tjukanov
