Postgis with Phoenix: package geo_postgis

[ndrean]

Guide on how to use Postgis with Phoenix

Once you created the Postgis extension,

# migration
def up do
    execute("CREATE EXTENSION IF NOT EXISTS postgis")
...
end

you may want to use the package geo_postgis to use the types Postgis provides as fields in migration and schema

Add it to your mix:

# Mix.project
defp deps do
    {:geo_postgis, "~> 3.4"}
end

Configure it. In the "config.exs", if you use Jason, declare:

# /config/config.exs
config :geo_postgis,
  json_library: Jason

and declare the Postgis types:

# /config/config.dev.exs
config :my_app, MyApp.Repo:
  username:.....
  types: MyApp.PostgresTypes.  <-----  add this

so create a module my_app/postgres_types.ex:

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

Now you are ready to use fields such as Geo.PostGIS.Geometry in your schemas. For example, I want to create the type "LINESTRING" with GEOGRAPHY and the 4326 projection (the one used by the GPS), so in a migration:

#migrartion
def up do
   ...
  execute("ALTER TABLE events ADD COLUMN coordinates geography(LINESTRING, 4326);")
end

NB: if you are looking for distances between geometries in your dataset, for example a nearest neighbour search, you may want to use a special spatial index GIST. This will accelerate the spatial queries (at the cost of space in your db) and allow the usage of the distance operator <->. Tested on finding among 1000 geometries the nearest geometries within a given distance to a point. The results for the first search give a response of < 100ms, and then < 10ms for consecutive searches.

# migration
def up do
   ...
   execute("CREATE INDEX  events_gix ON events USING GIST (coordinates);")
end

and in your schema, you can use it:

use Ecto.Schema

schema "my_table" do
   ...
   field :coordinates, Geo.PostGIS.Geometry
end

You are also likely to use GeoJSON format (but not restricted to). You can render this format with Postgis. An example of query using the distance <-> operator, the ST_Distance function, the ST_MakePoint function and rendering in GeoJSON format. You will be able to send this data directly to a Javascript library (LeafletJS or maplibre) and render the GeoJSON format easily.

SELECT json_build_object(
        'type', 'FeatureCollection',
        'features', json_agg(ST_AsGeoJSON(t.*)::json)
      )
      FROM (
        SELECT events.id, users.email, events.ad1, events.ad2, events.date, events.color, events.coordinates, events.distance,
        coordinates  <-> ST_MakePoint($1,$2) AS sphere_dist
        FROM events
        INNER JOIN users on events.user_id = users.id
        WHERE ST_Distance(ST_MakePoint($1, $2),coordinates)  < $3
        AND events.date >= $4::date AND events.date < $5::date
      ) AS t(id, email, ad1, ad2, date, color, coordinates, distance);

If you don't want GeoJSON formatted results but rather use your schema, prepare this query:

SELECT events.id, events.user_id, users.email, events.ad1, events.ad2,  events.date, events.color, events.coordinates, 
events.coordinates  <-> ST_MakePoint($1,$2) AS sphere_graphy
    FROM events
    INNER JOIN users ON events.user_id = users.id
    INNER JOIN event_participants AS ep on events.id = ep.event_id
    WHERE events.date >= $4::date AND events.date <= $5::date
    AND
    ST_Distance(ST_MakePoint($1,$2),events.coordinates)  < $3;

And run this query with Ecto and load the results using your schema: (note that the Postgres placeholders $i are interpolated with a list, the second argument of Repo.query function).

case Repo.query(query, [lng, lat, distance, start_date, end_date], log: true) do
      {:ok, %Postgrex.Result{columns: columns, rows: rows}} ->
        Enum.map(rows, fn row ->
          Repo.load(Event, {columns, row})
          |> Repo.preload(:event_participants)
        end)

      {:error, %Postgrex.Error{postgres: %{message: message}}} ->
        Logger.debug(message)
    end

[nelsonic]

Relevant real-world project where we used this: https://github.com/dwyl/phoenix-uk-postcode-finder-example 👌

[ndrean]

I understand you have a DIY method and calculate the distance yourself and check against the whole ETS/DB. You could have saved on computations in your calc_distance by doing a flat world approximation since you are working locally instead of doing spherique computations. I did use Postgres functions. It is programmed in C so way faster than the old Erlang. I observed that the query becomes quickly more efficient as you submit more queries, and very quickly the response time is almost independent of the number of new entries (talking < 20ms for 500 entries). This means it builds up a spatial GIST index very efficiently but I understood this is costly in terms of disk space.

[nelsonic]

Good feedback. Thanks! 👌