Implementation in Elixir

Concept of implementing the filtering/sorting based on geo is going to be same, you can implement the same in any programming languages (e.g Javascript, Python). Here I would like to share, how we can implement the same in Elixir programming language.

Implement dynamic location filtering using PostGIS

In this section, we are exploring an example to see how provider dynamic location filtering will work in action with the PostGIS. And we are leveraging the library GeoPostGI for this implementation. I am assuming that you have mix project with PostgreSQL database configured already:

1. Enable/Create PostGIS Extension in PostgreSQL Schema

Be sure to enable the PostGIS extension if you haven't already done so (you might need DBA help to enable the extension if you are using AWS Aurora).

defmodule Data.Repo.Migrations.EnablePostgisExtension do
  use Ecto.Migration
 
  def change do
    execute "CREATE EXTENSION IF NOT EXISTS postgis", "DROP EXTENSION IF EXISTS postgis"
  end
end

2. Install GeoPostGIS library

The package can be installed by adding :geo_postgis to your list of dependencies in mix.exs.

def deps do
  [
    ...,
    {:geo_postgis, "~> 3.5"}
  ]
end

3. Add a column of type "geometry" in your table

Assuming you have enabled the PostGIS extension in your schema. Now you can create Ecto migration for creating a table with the geometry column.

defmodule Data.Repo.Migrations.CreateTableServiceProviders do
 @moduledoc """
  We are choosing `geography` data type although the calculations on a sphere are
  computationally far more expensive than Cartesian calculations (geometry).
  In our case, we are going to rely on `geography` type and in order to avoid
  unnecessary heavy casting (while querying/indexing).
  """
  
  use Ecto.Migration
  
  def change do 
    create table("service_providers", primary_key: [name: id, type: :uuid] do
     add :name, :string, null: false
     add :verified_status, :string, null: false
     ...
     add :physical_location_geopoints, :geography
    end
  end
end

4. Define a column with type geometry for Ecto Schema and extend the typing

defmodule Data.Schemas.ServiceProviderSchema do
  @moduledoc """
  Represents Ecto schema for 'service_providers' table.
  """
  use Ecto.Schema
 
  @primary_key {:global_id, :binary_id, autogenerate: false}
 
  schema "service_providers" do
    field :name, :string
    field :verified_status, :string
    ...
    # Spatial data type `Geo.PostGIS.Geometry` represents all the PostGIS types available
    # for Geometry/Geography types ie, Geo.Point, Geo.MultiPoint.
    # Cast all the physical locations long/lat in following struct:
    # %Geo.MultiPoint{coordinates: [{0, 0}, {20, 20}], srid: 4326}
    field :physical_location_geopoints, Geo.PostGIS.Geometry
  end
end

Geo.PostGIS.Geometry type is not available by default so we need to extend the Ecto typings by creating a new Module.

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

and update the repo config:

config :my_project, Data.Repo,
  database: "my_geo_search",
  username: "my_geo_search",
  hostname: "localhost",
  types: MyProject.PostgresTypes

5. Insert multiple points (longitude/latitude) into the column

We can add a collection of points (longitude/latitude pair) into the column, we just need to cast a map to a struct in the following format:

%Geo.MultiPoint{coordinates: [{long, lat}], srid: 4326}

since our column type is Geo.PostGIS.Geometry and this can support all the Geometry types i.e. Point, MultiPoint, Polygon.

In PostGIS, Order of longitude and latitude matter in the pair, first should be longitude and then second should be latitude otherwise you might get incorrect results.

6. Write filters/sort query in PostGIS

Internally geo_postgis function st_dwithin_in_meters casts the geometries given to geographies to check for distance in meters if they are’t in geographies.

In order to filter & sort the Service provider by their closeness, we need center point ( means longitude/latitude) and radius in meters.

center = %Geo.Point{coordinates: {-97.733330, 30.266666}, srid: 4326}
radius = 50_000

query =
      from sp in ServiceProviderSchema,
      where: st_dwithin_in_meters(sp.physical_location_geopoints, ^center, ^radius),
      order_by: [
            st_distance_in_meters(sp.physical_location_geopoints, ^center),
            desc: p.updated_at
      ],
      select: %{id: sp.id}

7. Create Index to Speed-up the geo-distance queries in PostGIS

Creating a Spatial index to boost up the geospatial query performance and our column physical_locations is a type of geography we can directly create an index on this column but If we need to query on geometry type we need to create a spatial index by casting into geometry type.

defmodule Data.Repo.Migrations.CreateServiceProvidersPhysicalLocationsGeographyIndex do
  @doc """
  `physical_location_geopoints` is type of `geography` and creating index for
  `geography` type column.
 
  Reference: [Spatial Index](http://postgis.net/workshops/postgis-intro/indexing.html)
  """
  use Ecto.Migration
 
  @disable_ddl_transaction true
  @disable_migration_lock true
 
  def up do
    execute """
    CREATE INDEX CONCURRENTLY IF NOT EXISTS service_providers_physical_location_geopoints_geography_index
    ON providers ( physical_location_geopoints )
    """
  end
 
  def down do
    execute """
    DROP INDEX IF EXISTS service_providers_physical_location_geopoints_geography_index
    """
  end
end

💡 In PostgreSQL, Index name should be under 64 characters, otherwise remaining characters will be truncated.