DEV Community: Kacper Goliński

Playing with GPX tracks in Elixir and PostGIS

Kacper Goliński — Sun, 22 Mar 2020 13:43:59 +0000

Lately, I've been thinking about the idea of creating a web app for storing and visualizing my cycle rides. Most of the popular activity trackers, allow exporting your activities as GPX files. We can use those files to import activity to the other service, for example to the one that we will build in a moment.

In this blog post, I would like to present my findings on how to store and visualize GPX tracks using Elixir/Phoenix, PostgreSQL and a little bit of JavaScript. The plan is to parse the GPX file and extract track data. Save it in PostgreSQL as a geometry type, which comes with PostGIS. Finally, visualize track using an interactive web map.

GitHub repo containing code used in this blogpost

GPX intro

GPX (GPS Exchange Format) is an XML data format, designed to share GPS data between software applications. You can find more info about the format on the official website.

GPX can be used to describe the following data:

waypoints - individual points without relation to each other
routes - an ordered list of points, representing a series of turns leading to a destination
tracks - an ordered list of points, describing a path, for example, a raw output of GPS recording of single trip

Below we can examine an example GPX file. It contains tracks data, which were recorded during an activity.

<!-- my_activity.xml -->

<?xml version="1.0" encoding="UTF-8"?>
<gpx creator="StravaGPX" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd" version="1.1" xmlns="http://www.topografix.com/GPX/1/1">
  <metadata>
    <time>2020-02-02T10:37:13Z</time>
  </metadata>
  <trk> <!-- representation of a track -->
    <name>Gdynia</name>
    <trkseg> <!-- track segment -->
      <trkpt lat="54.5198480" lon="18.5396990"> <!-- track point -->
        <ele>10.2</ele> <!-- point elevation -->
        <time>2020-02-02T10:37:13Z</time> <!-- time of the recording  -->
      </trkpt>

      <trkpt lat="54.5198540" lon="18.5397300">
        <ele>10.2</ele>
        <time>2020-02-02T10:37:14Z</time>
      </trkpt>

      <!-- more track points  -->
    </trkseg>
  </trk>
</gpx>

To parse GPX files in Elixir, I’ve created GpxEx package. It’s still work in progress but it supports parsing tracks. After reading a file, you can convert it to Elixir structs.

{:ok, gpx_doc} = File.read("./my_track.gpx")
{:ok, gpx} = GpxEx.parse(gpx_doc)

%GpxEx.Gpx{
  tracks: [
    %GpxEx.Track{
      name: "Track's name",
      segments: [
        %GpxEx.TrackSegment{
          points: [
            %GpxEx.TrackPoint{
              ele: 10.2,
              lat: 54.519848,
              lon: 18.539699,
              time: "2020-02-02T10:37:13Z"
            },
            %GpxEx.TrackPoint{
              ele: 10.2,
              lat: 54.519854,
              lon: 18.53973,
              time: "2020-02-02T10:37:14Z"
            }
          ]
        }
      ]
    }
  ]
}

PostGIS intro

What is PostGIS and why do we need it? PostgreSQL supports the XML data type natively. Why not use that? We could save the GPX file straight away and skip the whole parsing part and adding the extra extension.

Doing all of that, we would lose many benefits that are provided by PostGIS. PostGIS is a spatial database extension that adds support for geographic objects. After converting tracks to geo type and storing them in Postgres, we will be able to run location queries and spatial functions. For example, we will be able to:

find all tracks near a certain location
calculate track's distance
convert a track to the format used by web maps (GeoJSON, TopoJSON, KML, etc)

Intial project setup

In this tutorial I'm using the following versions:

Elixir 1.10
Phoenix 1.4.14
PostgreSQL 12.2
PostGIS 3.0

Let's start by creating a fresh Phoenix project.

mix phx.new gpx_phoenix
cd gpx_phoenix
mix ecto.create

PostGIS in Phoenix framework

We need to add PostGIS support in Phoenix application. To do that, we can use geo and geo_postgis packages.

defp deps do
  # other deps
  {:geo, "~> 3.3"},
  {:geo_postgis, "~> 3.3"}
end

First, we need to pass new PostGIS extensions to postgrex. We have to create new file, for example lib/gpx_phoenix/postgrex_extensions.ex. It has to be defined only once during compilation, hence it needs to be done outside of any module or function.

# lib/gpx_phoenix/postgrex_extensions.ex

Postgrex.Types.define(
  GpxPhoenix.PostgresTypes,
  [Geo.PostGIS.Extension] ++ Ecto.Adapters.Postgres.extensions(),
  json: Jason
)

After defining the above types, we need to specify them in our Repo config.

# config/config.exs

config :gpx_phoenix, GpxPhoenix.Repo,
  types: GpxPhoenix.PostgresTypes

The last step is to enable PostGIS in PostgreSQL.

defmodule GpxPhoenix.Repo.Migrations.EnablePostgisExtension 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
end

Tracks context

In this section, we are creating Tracks context. We have to generate migration which will create tracks table with two columns. geom column will hold the geometry of each track. We can create it using the AddGeometryColumn function provided by PostGIS.

-- AddGeometryColumn(table_name, column_name, srid, type, dimension);
SELECT AddGeometryColumn('tracks', 'geom', 3857, 'MULTILINESTRINGZ', 3);

The sird stands for spatial reference system identifier which defines the coordinate system. We are going to use Pseudo-Mercator (EPSG:3857) used for rendering most of the popular web maps.
The type specifies geometry type, eg 'MULTILINESTRINGZ', 'POLYGON', 'POINT'.
The last argument is the dimension. We want to store 3 dimensions, x and y coordinates along with elevation (z).

We are defining geom as MULTILINESTRINGZ because it plays nicely with the way how GPX format. GPX track can contain multiple segments, and each segment contains multiple points. Each point has (lat, lon) coordinates and can also hold elevation.

A linestring is a path between locations, an ordered series of two or more points. A "Z" dimension adds height information to each point. A MultiLineStringZ is a collection of linestringZ

# priv/repo/migrations/20200316162637_create_tracks_table.exs

defmodule GpxPhoenix.Repo.Migrations.CreateTracksTable do
  use Ecto.Migration

  def up do
    create table(:tracks) do
      add(:name, :string)

      timestamps()
    end

    execute("SELECT AddGeometryColumn('tracks', 'geom', 3857, 'MULTILINESTRINGZ', 3);")
  end

  def down do
    drop table(:tracks)
  end
end

In track’s schema, we have to use Geo.PostGIS.Geometry type which was added by geo_postigs package. We have to remember that we specified our geometry type as MULTILINESTRINGZ and the database will enforce that.

# lib/gpx_phoenix/tracks/track.ex

defmodule GpxPhoenix.Tracks.Track do
  use Ecto.Schema
  import Ecto.Changeset

  schema "tracks" do
    field(:name, :string)
    field(:geom, Geo.PostGIS.Geometry)

    timestamps()
  end

  @doc false
  def changeset(track, attrs) do
    track
    |> cast(attrs, [:name, :geom])
    |> validate_required([:name, :geom])
  end
end

Tracks context with basic CRUD functions.

# lib/gpx_phoenix/tracks/tracks.ex

defmodule GpxPhoenix.Tracks do
  @moduledoc """
  The Tracks context.
  """

  import Ecto.Query, warn: false
  alias GpxPhoenix.Repo

  alias GpxPhoenix.Tracks.Track

  def get_track!(id), do: Repo.get!(Track, id)

  def list_tracks, do: Repo.all(Track)

  def create_track(attrs \\ %{}) do
    %Track{}
    |> Track.changeset(attrs)
    |> Repo.insert()
  end

  def change_track(%Track{} = track), do: Track.changeset(track, %{})
end

Tracks importer

Now we can focus on track importer module. It will be responsible for parsing GPX and creating a new track record. We will parse GPX file using GpxEx package which we have to add to our dependencies.

# mix.exs

defp deps do
  # other deps
  {:gpx_ex, git: "git@github.com:caspg/gpx_ex.git", tag: "0.1.0"}
end

Before saving parsed GPX file to the database, we have to convert it to our geometry type, which is Geo.MultiLineStringZ. When creating Geo type, we have to use the same srid value as we used during creating geom column.

# lib/gpx_phoenix/tracks/import_track.ex

defmodule GpxPhoenix.Tracks.ImportTrack do
  alias GpxPhoenix.Tracks.Track

  @spec call(gpx_doc: String.t()) :: {:error, %Ecto.Changeset{}} | {:ok, %Track{}}

  def call(gpx_doc) do
    gpx_doc
    |> GpxEx.parse()
    |> get_first_track()
    |> build_track_geometry()
    |> create_track()
  end

  defp get_first_track({:ok, %GpxEx.Gpx{tracks: [track | _]}}), do: {:ok, track}

  defp build_track_geometry({:ok, %GpxEx.Track{segments: segments} = track}) do
    multilinez_coordinates = convert_segments_to_mulitlinez(segments)

    track_geometry = %Geo.MultiLineStringZ{
      coordinates: multilinez_coordinates,
      srid: 3857
    }

    {:ok, track, track_geometry}
  end

  defp convert_segments_to_mulitlinez(segments) do
    Enum.map(segments, fn segment ->
      Enum.map(segment.points, fn point ->
        {point.lon, point.lat, point.ele}
      end)
    end)
  end

  defp create_track({:ok, %GpxEx.Track{name: name}, track_geometry}) do
    GpxPhoenix.Tracks.create_track(%{name: name, geom: track_geometry})
  end
end

Let's import some example Gpx files. Here are three tracks I recorded during my rides https://github.com/caspg/gpx_phoenix/tree/master/gpx_files. We can import them using Elixir console.

iex -S mix

iex(1)> {:ok, gpx_doc} = File.read("./gpx_files/gdansk-elblag.gpx")
iex(2)> GpxPhoenix.Tracks.ImportTrack.call(gpx_doc)

# same for othe files

Converting track to GeoJSON

GeoJSON format is designed to represent geographical objects and is based on the JSON. It is commonly used in web mapping applications. We can convert our geometry to GeoJSON using PostGIS function.

# lib/gpx_phoenix/tracks/tracks.ex

defmodule GpxPhoenix.Tracks do
  import Ecto.Query, warn: false
  alias GpxPhoenix.Repo
  alias GpxPhoenix.Tracks.Track

  # ...other functions

  def get_geom_as_geojson!(%{id: id}) do
    query =
      from(t in Track,
        where: t.id == ^id,
        select: fragment("ST_AsGeoJSON(?)::json", t.geom)
      )

    Repo.one!(query)
  end
end

Tracks controller

Let's create tracks_controller, tracks_view and corresponding templates. tracks_controller will have two standard CRUD actions and one action, geojson, for fetching track's GeoJSON asynchronously.

defmodule GpxPhoenixWeb.Router do
  # omitted code

 scope "/", GpxPhoenixWeb do
    pipe_through :browser

    get "tracks", TracksController, :index
    get "tracks/:id", TracksController, :show
    get "tracks/:id/geojson", TracksController, :geojson
  end
end

# lib/gpx_phoenix_web/controllers/tracks_controller.ex

defmodule GpxPhoenixWeb.TracksController do
  use GpxPhoenixWeb, :controller

  def index(conn, _params) do
    tracks = GpxPhoenix.Tracks.list_tracks()
    render(conn, "index.html", tracks: tracks)
  end

  def show(conn, %{"id" => id} = _params) do
    track = GpxPhoenix.Tracks.get_track!(id)
    render(conn, "show.html", track: track)
  end

  def geojson(conn, %{"id" => id} = _params) do
    geojson = GpxPhoenix.Tracks.get_geom_as_geojson!(%{id: id})

    json(conn, geojson)
  end
end

# lib/gpx_phoenix_web/views/tracks_view.ex

defmodule GpxPhoenixWeb.TracksView do
  use GpxPhoenixWeb, :view
end

# lib/gpx_phoenix_web/templates/tracks/index.html.eex

<ul>
  <%= for track <- @tracks do %>
    <li>
      <%= link(track.name, to: Routes.tracks_path(@conn, :show, track.id)) %>
    </li>
  <% end %>
</ul>

# lib/gpx_phoenix_web/templates/tracks/show.html.eex

<h2><%= @track.name %></h2>

Interactive web map

We can use Leaflet.js to render an interactive web map. Before writing any JavaScript code we have to include Leaflet CSS and Leaflet JavaScript files. To make things simpler we can include those files in the show template.

We also need an HTML element that will serve as a container for our map and will hold track-id as a data attribute. We are going to use a track-id to fetch correct GeoJSON.

# lib/gpx_phoenix_web/templates/tracks/show.html.eex

<h2><%= @track.name %></h2>

<div id="track-map" data-track-id="<%= @track.id %>" style="height: 500px; margin-top: 50px;"></div>

<link rel="stylesheet" href="https://unpkg.com/leaflet@1.6.0/dist/leaflet.css" />
<script src="https://unpkg.com/leaflet@1.6.0/dist/leaflet.js"></script>

The only thing that’s left is to create an actual map and render our track on it. Leaflet allows for adding GeoJSON layers. There is also a handy function that will make sure our track fits the map. In this example, I’m using OpenStreetMap as a free tiles provider. In a production app, we should look for some commercial provider.

// assets/js/app.js

function renderMap() {
  const trackMap = document.getElementById('track-map')

  if (!trackMap) {
    return
  }

  // create leaflet map object
  const map = L.map(trackMap)

  L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
    attribution:
      '&copy <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
  }).addTo(map)

  const trackId = trackMap.dataset.trackId

  // fetch geojson and add it to the map as new layer
  fetch(`/tracks/${trackId}/geojson`)
    .then(res => res.json())
    .then(geojson => {
      const geojsonLayer = L.geoJSON(geojson).addTo(map)

      // handy function that makes sure our track will fit the map
      map.fitBounds(geojsonLayer.getBounds())
    })
}

renderMap()

Finally, we can see our tracks on the interactive map. In case you are wondering, yes, there is Hel in Poland.

Comments

You can reach my via email or discuss on Twitter.

Optimizing full-text search with Postgres materialized view in Rails

Kacper Goliński — Tue, 03 Mar 2020 12:44:43 +0000

My recent side project is an aggregator for remote dev jobs https://remotestack.club. To keep things simple, I decided to use Postgres full-text search. It offers powerful search capabilities. More than enough for a side project and early startups.

I built the project with Ruby on Rails and I used pg_search gem to handle PostgreSQL's full-text search. I wanted to offer a search across the job's details, skills' names, and the company's name.

It is easy to search columns on associated models. Unfortunately, there is no simple solution to speed up those searches. This article shows how to optimize search with Postgres materialized view.

Quick intro to full-text search

Full-text search is a technique for searching natural-language documents that satisfy a query.
In our case, a query is a text provided by a user.

A document is the unit of searching in a full text search system; for example, a magazine article or email message

Postgres text search intro

In PostgreSQL, a document usually is a text field or a combination of fields. Possibly stored across multiple tables. During the search, each document is converted into tsvector.

A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word

Text search data types

Below we can see a tsvector in action.

postgres=# SELECT to_tsvector('Ruby on Rails, is a server-side web application framework');
                                       to_tsvector
-----------------------------------------------------------------------------------------
 'applic':10 'framework':11 'rail':3 'rubi':1 'server':7 'server-sid':6 'side':8 'web':9
(1 row)

Schema

class CreateJobPostsSkillsAndCompanies < ActiveRecord::Migration
  def change
    create_table :companies do |t|
      t.string :name
    end

    create_table :job_posts do |t|
      t.string :title
      t.text :description
      t.belongs_to :company

      t.timestamps
    end

    create_table :skills do |t|
      t.string :name

      t.timestamps
    end

    create_table :job_post_skills do |t|
      t.belongs_to :skill
      t.belongs_to :job_post

      t.timestamps
    end
  end
end

Seed data

Let’s create some seed data. 10_000 job posts should be enough.

https://github.com/caspg/optimizing-postgresql-full-text-search-rails/blob/master/db/seeds.rb

Full text with `pg_search` gem

Pg_search gem builds ActiveRecord named scopes that take advantage of PostgreSQL's full-text search. We can configure a search scope using pg_search_scope. The first parameter is a scope that we will use for full-text search.

We want to search against columns in JobPost but also against columns on associated models, Skill and Company. pg_search supports searching through associations with :associated_against options.

class JobPost < ApplicationRecord
  has_many :job_post_skills, dependent: :destroy
  has_many :skills, through: :job_post_skills
  belongs_to :company

  include PgSearch::Model
  pg_search_scope(
    :search,
    against: [:title, :description],
    associated_against: { skills: :name, company: :name },
    using: {
      tsearch: {
        dictionary: 'english',
      },
    },
  )
end

After adding a couple of lines of code, we can already use a full-text search. As we can see below, performance is not that great.

> JobPost.search("ruby on rails")
JobPost Load (1076.5ms)  SELECT  "job_posts".* FROM "job_posts" ...

Potential optimizations

We can use database indexes to speed up data retrieval. Postgres gives us two types of indexes for full-text searches.

GIN (Generalized Inverted Index)
GiST (Generalized Search Tree)

Acording to the documentation, GIN indexes are the preferred type.

CREATE INDEX name ON table USING GIN (column);
-- or
CREATE INDEX name ON table USING GIN (to_tsvector('english', column));

The column must be of tsvector type or must be converted to this type with to_tsvector function. We can populate the column of tsvector type using database triggers. With searches across associated tables, we have to do some extra work to build such indexes.

We could use database denormalization and triggers to ensure data integrity. This would give us up to date indexes but would introduce extra complexity and would slow down updates. Another solution is materialized view.

Quick intro to materialized views

A View is a virtual table created by a query based on one or more tables. It can be used for wrapping commonly used complex queries. Materialized Views are special kind of View that persist results in table-like form.

They give us faster access to data but increase database size and data are not always current. They are perfect in scenarios when data does not have to be always fresh or when we have more or less static data. For example, a job aggregator which imports new posts a couple of times per day. In this case, we can refresh data after each import.

We can generate fresh data with:

REFRESH MATERIALIZED VIEW my_materialized_view;

Materialized views in Rails with Scenic gem

Scenic gem adds methods to create and manage database views (and materialized views) in Rails.

rails generate scenic:view job_post_search --materialized
      create  db/views/job_post_searches_v01.sql
      create  db/migrate/[TIMESTAMP]_update_job_post_searches_to_version_1.rb

job_post_searches_v01.sql defines a query we will use to build a materialized view. We have to build a view with two columns, job_post_id and tsv_document. tsv_document is a combination of associated fields in tsvector data type.

-- db/views/job_post_searches_v01.sql

SELECT
  job_posts.id AS job_post_id,
  (
    to_tsvector('english', coalesce(job_posts.title, ''))
    || to_tsvector('english', coalesce(job_posts.description, ''))
    || to_tsvector('english', coalesce(companies.name, ''))
    || to_tsvector('english', coalesce(string_agg(skills.name, ' ; '), ''))
  ) AS tsv_document
FROM job_posts
JOIN companies ON companies.id = job_posts.company_id
JOIN job_post_skills ON job_post_skills.job_post_id = job_posts.id
JOIN skills ON skills.id = job_post_skills.skill_id
GROUP BY job_posts.id, companies.id;

The above query returns the following results:

 job_post_id |                                                                                                            tsv_document
-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           1 | 'applic':17 'block':31 'dach':30 'develop':5 'framework':18 'licens':25 'mit':24 'parker':28 'rail':4,8,10 'rubi':2,6,21,32 'senior':1 'server':14 'server-sid':13 'side':15 'smith':27 'spacex':26 'von':29 'web':16 'written':19

...more rows

Scenic adds create_view method. It creates a materialized view based on the corresponding SQL statement. Finally, we can also create a GIN index on tsv_document column.

class CreateJobPostSearches < ActiveRecord::Migration
  def change
    create_view :job_post_searches, materialized: true

    # below line added by us
    add_index :job_post_searches, :tsv_document, using: :gin
  end
end

Full-text search using materialized view

Thanks to ActiveRecord, a model can be backed by a view. We can define search scope on such model in the same way we did with JobPost model. This time, we want to search against tsvector type column, instead of using an expression (which is used by default). It won't create tsvector during each search and will use a previously created index.

class JobPostSearch < ApplicationRecord
  self.primary_key = :job_post_id

  include PgSearch::Model
  pg_search_scope(
    :search,
    against: :tsv_document,
    using: {
      tsearch: {
        dictionary: 'english',
        # specify tsvector column name
        tsvector_column: 'tsv_document',
      },
    },
  )
end

class JobPost < ApplicationRecord
  ... # omitted lines

  def self.faster_search(query)
    # protip: when using `select` instead of `pluck` we have one query less
    where(id: JobPostSearch.search(query).select(:job_post_id))
  end
end

Results

> JobPost.search("ruby on rails")
JobPost Load (1076.5ms)  SELECT  "job_posts".* FROM "job_posts" ...

> JobPost.faster_search("ruby on rails")
JobPost Load (1.2ms)  SELECT  "job_posts".* FROM "job_posts" ...

Refreshing materialied view

There is one more thing that we will have to take care of. We will have to refresh the materialized view periodically. Scenic gives us a handy method to do that.

When the refresh is running in nonconcurrent mode, the view is locked for selects. We can avoid that with the concurrent mode. The concurrent mode requires at least PostgreSQL 9.4 and view to have at least one unique index that covers all rows.

class JobPostSearch < ApplicationRecord
  ... # omitted lines

  def self.refresh_materialized_view
    Scenic.database.refresh_materialized_view(
      :job_post_searches,
      concurrently: true,
    )
  end
end

We can add an index to our view as to any other table.

class AddUniqueIndexToJobPostSerches < ActiveRecord::Migration
  def change
    add_index :job_post_searches, :job_post_id, unique: true
  end
end

Now we can run the below method when we want to generate fresh data.

> JobPostSearch.refresh_materialized_view
(1411.2ms)  REFRESH MATERIALIZED VIEW CONCURRENTLY "job_post_searches";

Comments

If you have any comments, you can reach me via email or twitter.

DEV Community: Kacper Goliński

Playing with GPX tracks in Elixir and PostGIS

GPX intro

PostGIS intro

Intial project setup

PostGIS in Phoenix framework

Tracks context

Tracks importer

Converting track to GeoJSON

Tracks controller

Interactive web map

Comments

Links

Optimizing full-text search with Postgres materialized view in Rails

Quick intro to full-text search

Schema

Seed data

Full text with `pg_search` gem

Potential optimizations

Quick intro to materialized views

Materialized views in Rails with Scenic gem

Full-text search using materialized view

Results

Refreshing materialied view

Comments

Links

DEV Community: Kacper Goliński

Playing with GPX tracks in Elixir and PostGIS

GPX intro

PostGIS intro

Intial project setup

PostGIS in Phoenix framework

Tracks context

Tracks importer

Converting track to GeoJSON

Tracks controller

Interactive web map

Comments

Links

Optimizing full-text search with Postgres materialized view in Rails

Quick intro to full-text search

Schema

Seed data

Full text with pg_search gem

Potential optimizations

Quick intro to materialized views

Materialized views in Rails with Scenic gem

Full-text search using materialized view

Results

Refreshing materialied view

Comments

Links

Full text with `pg_search` gem