~ihabunek/triglav

ref: d1b5915f3e023d04850eabf702bda7207a337302 triglav/lib/triglav/zet/gtfs.ex -rw-r--r-- 1.9 KiB
d1b5915fIvan Habunek Validate platform distance from route 7 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
defmodule Triglav.Zet.Gtfs do
  alias Triglav.Repo
  alias Triglav.Schemas.Zet

  import Ecto.Query
  import Geo.PostGIS

  def get_feed_info do
    Zet.FeedInfo |> Repo.one!()
  end

  def list_routes() do
    Zet.Route |> Repo.all()
  end

  def get_route(id) do
    Zet.Route
    |> where(id: ^id)
    |> Repo.one!()
  end

  def list_stops_by_id(stop_ids) do
    Zet.Stop
    |> where([s], s.id in ^stop_ids)
    |> Repo.all()
  end

  @spec list_distinct_trips([{:route_id, binary()}]) :: [Zet.DistinctTrip.t()]
  def list_distinct_trips(opts \\ []) do
    route_id = Keyword.get(opts, :route_id)

    from(t in Zet.DistinctTrip)
    |> maybe_filter_by_route_id(route_id)
    |> Repo.all()
  end

  defp maybe_filter_by_route_id(query, nil), do: query
  defp maybe_filter_by_route_id(query, route_id), do: where(query, [t], t.route_id == ^route_id)

  @doc """
  Returns a list of all stops contained in any trip in the given route.
  """
  @spec fetch_distinct_stops(Zet.Route.t()) :: [Zet.Stop.t()]
  def fetch_distinct_stops(%Zet.Route{} = route) do
    from(t in Zet.Trip,
      where: t.route_id == ^route.id,
      join: st in Zet.StopTime,
      on: st.trip_id == t.id,
      join: s in Zet.Stop,
      on: s.id == st.stop_id,
      select: s,
      distinct: true
    )
    |> Repo.all()
  end

  @spec find_closest_stop(Geo.Point.t() | Geo.LineString.t()) :: Zet.Stop.t() | nil
  def find_closest_stop(geom) do
    # TODO: speed up this query or replace with a materialized view
    # see: https://www.postgis.net/workshops/postgis-intro/knn.html

    # Casting to `geography` makes distance be returned in meters rather than degrees

    from(s in Zet.Stop,
      select: {s, st_distance(fragment("?::geography", s.geom), fragment("?::geography", ^geom))},
      order_by: [
        asc: st_distance(fragment("?::geography", s.geom), fragment("?::geography", ^geom))
      ],
      limit: 1
    )
    |> Repo.one()
  end
end