Postgis with Phoenix: package geo_postgis

## Guide on how to use `Postgis` with `Phoenix`

Once you created the Postgis extension,
```elixir
# 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:

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

Configure it. In the "config.exs", if you use `Jason`, declare:
```elixir
# /config/config.exs
config :geo_postgis,
json_library: Jason
```

and declare the Postgis types:

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

so create a module `my_app/postgres_types.ex`:

```elixir
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:

```elixir
#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](https://www.crunchydata.com/blog/a-deep-dive-into-postgis-nearest-neighbor-search), you may want to use a special [spatial index GIST](http://postgis.net/workshops/postgis-intro/indexing.html). This will accelerate the spatial queries (at the cost of space in your db) and allow the usage of the [distance operator <->](https://postgis.net/docs/geometry_distance_knn.html).
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.

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

and in your schema, you can use it:

```elixir
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.

```sql
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:
```sql
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).

```elixir
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
```