~ihabunek/triglav

06e2cc28929796b848adc447295f6285caabddfa — Ivan Habunek 1 year, 8 months ago f66c431 stuff
WIP
M lib/triglav/derived/public_transport.ex => lib/triglav/derived/public_transport.ex +35 -22
@@ 20,6 20,14 @@ defmodule Triglav.Derived.PublicTransport do
    from(t in Trip) |> Repo.all()
  end

  def list_route_trips(route_id) do
    from(t in Trip,
      where: t.route_id == ^route_id,
      preload: [:relation, :route, platforms: [:node, :way, :stop]]
    )
    |> Repo.all()
  end

  def list_platforms() do
    from(p in Platform,
      join: t in Trip,


@@ 60,22 68,22 @@ defmodule Triglav.Derived.PublicTransport do

  defp persist(trips, platforms) do
    Multi.new()
    |> Multi.delete_all(:delete_trips, Trip)
    |> persist_trips(trips)
    |> Multi.delete_all(:delete_platforms, Platform)
    |> persist_platforms(platforms)
    |> Multi.delete_all(:delete_trips, Trip)
    |> Multi.insert_all(:insert_trips, Trip, trips)
    |> Multi.insert_all(:insert_platforms, Platform, platforms)
    |> add_distances()
    |> Repo.transaction()
  end

  defp persist_trips(multi, trips) do
    Enum.reduce(trips, multi, fn trip, multi ->
      Multi.insert(multi, {:trip, trip.relation.id}, trip)
    end)
  end

  defp persist_platforms(multi, platforms) do
    Enum.reduce(platforms, multi, fn platform, multi ->
      Multi.insert(multi, {:platform, platform.relation_id, platform.sequence_id}, platform)
  defp add_distances(multi) do
    Multi.run(multi, :add_distances, fn repo, _changes ->
      repo.query("""
        UPDATE public_transport_platforms p
        SET distance_from_route = st_distance(t.geometry::geography, p.geometry::geography)
        FROM public_transport_trips t
        WHERE t.id = p.trip_id;
      """)
    end)
  end



@@ 83,6 91,7 @@ defmodule Triglav.Derived.PublicTransport do
    {geometry, exact} = trip_geometry(relation, ways_map)
    stop_ids = trip_stop_ids(relation, platform_member_map)
    route = Map.get(routes_map, Relation.ref(relation))
    route_id = if route, do: route.id

    sample_trip_id =
      with %Route{} <- route,


@@ 90,9 99,10 @@ defmodule Triglav.Derived.PublicTransport do
        distinct_trip.sample_trip_id
      end

    %Trip{
      relation: relation,
      route: route,
    %{
      id: relation.id,
      relation_id: relation.id,
      route_id: route_id,
      geometry: geometry,
      exact: exact,
      stop_ids: stop_ids,


@@ 101,12 111,14 @@ defmodule Triglav.Derived.PublicTransport do
  end

  defp generate_platform(platform_member, stops_map, routes_map, relations_map) do
    node = if platform_member.member_type == "N", do: platform_member.member
    way = if platform_member.member_type == "W", do: platform_member.member
    node_id = if platform_member.member_type == "N", do: platform_member.member_id
    way_id = if platform_member.member_type == "W", do: platform_member.member_id
    stop = Map.get(stops_map, platform_member.member.tags["gtfs:stop_id"])
    stop_id = if stop, do: stop.id

    relation = Map.fetch!(relations_map, platform_member.relation_id)
    route = Map.get(routes_map, relation.tags["ref"])
    route_id = if route, do: route.id

    geometry =
      case platform_member.member_type do


@@ 114,12 126,13 @@ defmodule Triglav.Derived.PublicTransport do
        "W" -> platform_member.member.linestring
      end

    %Platform{
    %{
      relation_id: platform_member.relation_id,
      node: node,
      way: way,
      stop: stop,
      route: route,
      trip_id: platform_member.relation_id,
      node_id: node_id,
      way_id: way_id,
      stop_id: stop_id,
      route_id: route_id,
      sequence_id: platform_member.sequence_id,
      geometry: geometry
    }

M lib/triglav/schemas/public_transport/platform.ex => lib/triglav/schemas/public_transport/platform.ex +1 -4
@@ 24,9 24,6 @@ defmodule Triglav.Schemas.PublicTransport.Platform do

    field :sequence_id, :integer
    field :geometry, Geo.PostGIS.Geometry

    # TODO: maybe make this a real field, we'd need to import data first, to get
    # it indexed, and then populate this field
    field :distance_from_route, :float, virtual: true
    field :distance_from_route, :integer
  end
end

M lib/triglav/schemas/public_transport/trip.ex => lib/triglav/schemas/public_transport/trip.ex +2 -0
@@ 7,6 7,7 @@ defmodule Triglav.Schemas.PublicTransport.Trip do

  alias Triglav.Schemas.Osmosis
  alias Triglav.Schemas.Zet
  alias Triglav.Schemas.PublicTransport.Platform

  @derive {Inspect, only: [:id, :relation_id, :sample_trip_id]}



@@ 15,6 16,7 @@ defmodule Triglav.Schemas.PublicTransport.Trip do
  schema "public_transport_trips" do
    belongs_to :relation, Osmosis.Relation
    belongs_to :route, Zet.Route, type: :binary
    has_many :platforms, Platform
    field :geometry, Geo.PostGIS.Geometry
    field :exact, :boolean
    field :stop_ids, {:array, :string}

M lib/triglav_web/controllers/zet/routes_controller.ex => lib/triglav_web/controllers/zet/routes_controller.ex +9 -3
@@ 1,6 1,7 @@
defmodule TriglavWeb.Zet.RoutesController do
  use TriglavWeb, :controller

  alias Triglav.Derived.PublicTransport
  alias Triglav.GeoJSON
  alias Triglav.Import.Geofabrik
  alias Triglav.Osm


@@ 54,13 55,18 @@ defmodule TriglavWeb.Zet.RoutesController do
    relation_errors = Enum.filter(errors, & &1.relation_id) |> Enum.group_by(& &1.relation_id)
    route_errors = Enum.filter(errors, &is_nil(&1.relation_id))

    derived_trips = PublicTransport.list_route_trips(id)

    platform_members =
      relations
      |> Enum.map(& &1.id)
      |> Osmosis.list_platform_members()
      |> Enum.group_by(& &1.relation_id)

    trips = Gtfs.list_distinct_trips(route_id: route.id)
    trips =
      Gtfs.list_distinct_trips(route_id: route.id)
      |> IO.inspect()

    trips_by_stop_ids = trips |> Map.new(&{&1.stops, &1})

    relations_by_stop_ids =


@@ 79,7 85,6 @@ defmodule TriglavWeb.Zet.RoutesController do
      Enum.map(relations_by_stop_ids, fn {stops, relation} ->
        Map.put(relation, :matched_trip, Map.get(trips_by_stop_ids, stops))
      end)
      |> IO.inspect()

    stops =
      trips


@@ 111,7 116,8 @@ defmodule TriglavWeb.Zet.RoutesController do
      routes_geojson: routes_geojson,
      stops: stops,
      stops_json: stops_json,
      trips: annotated_trips
      trips: annotated_trips,
      derived_trips: derived_trips
    )
  end


M lib/triglav_web/templates/zet/routes/detail.html.eex => lib/triglav_web/templates/zet/routes/detail.html.eex +61 -49
@@ 6,6 6,8 @@
    max-width:  1024px;
    margin: 1rem 0;
  }

  .bl { border-left: 5px solid gray }
</style>

<script id="stops-json" type="application/json">


@@ 66,59 68,69 @@
  <% end %>

  <section>
    <h3>Route relations</h3>

    <div style="display: flex; flex-direction: row; flex-wrap: wrap;">
      <%= for relation <- @relations, Relation.is_route(relation) do %>
        <% members = @platform_members[relation.id] || [] %>
        <div style="margin-left: 1rem;">
          <table>
            <thead>
              <tr>
                <th colspan="4" class="text-center">
                  <%= osm_link(relation, name: true) %>
                </th>
              </tr>
              <tr>
                <th>#</th>
                <th>Name</th>
                <th>gtfs:stop_id</th>
                <th>JOSM</th>
    <%= for trip <- @derived_trips do %>
      <h3><%= osm_link(trip.relation, name: true) %></h3>
      <div style="margin-left: 1rem;">
        <table>
          <thead>
            <tr>
              <th colspan="5" class="text-center">OSM Platform</th>
              <th colspan="2" class="text-center bl">GTFS Stop</th>
            </tr>
            <tr>
              <th>#</th>
              <th>Name</th>
              <th>Stop ID</th>
              <th>Distance from route</th>
              <th>JOSM</th>
              <th class="bl">ID</th>
              <th>Name</th>
            </tr>
          </thead>
          <tbody>
            <%= for platform <- trip.platforms do %>
            <% member = platform.node || platform.way %>
            <tr>
              <td><%= osm_link(member) %></td>
              <td><%= member.tags["name"] %></td>
              <td class="<%= if !platform.stop_id, do: "bg-red" %>">
                <%= platform.stop_id || "Missing" %>
              </td>
              <td class="<%= if platform.distance_from_route > 20, do: "bg-red" %>">
                <%= platform.distance_from_route %>m
              </td>
              <td><%= josm_load_objects([member]) %></td>
              <%= if platform.stop do %>
                <td class="bl"><%= platform.stop.id %></td>
                <td><%= platform.stop.name %></td>
              <% else %>
                <td class="bl"></td>
                <td></td>
              <% end %>
            </tr>
            <% end %>
          </tbody>
          <tfoot>
            <%= if trip.sample_trip_id do %>
              <tr class="callout">
                <td colspan="7" class="text-green">
                  ✔ Matches ZET trip <b><%= trip.sample_trip_id %></b>
                </td>
              </tr>
            </thead>
            <tbody>
              <%= for m <- members do %>
              <tr>
                <td><%= osm_link(m.member) %></td>
                <td><%= m.member.tags["name"] %></td>
                <td class="<%= if !m.member.tags["gtfs:stop_id"], do: "bg-red" %>">
                  <%= if m.member.tags["gtfs:stop_id"] do %>
                    <%= m.member.tags["gtfs:stop_id"] %>
                  <% else %>
                    Missing
                  <% end %>
            <% else %>
              <tr class="bg-red">
                <td colspan="7">
                  ✖ No GTFS trip matched.
                  <a href="<%= Routes.zet_routes_path(TriglavWeb.Endpoint, :match, @route.id, trip.relation.id) %>">
                    Find a match
                  </a>
                </td>
                <td><%= josm_load_objects([m.member]) %></td>
              </tr>
              <% end %>
            </tbody>
            <% end %>
          </table>

          <%= if relation.matched_trip do %>
            <div class="callout">
              <span class="text-green">✔ Matches a GTFS trip</span>
            </div>
          <% else %>
            <div class="callout error">
              ✖ No GTFS trip matched.
              <a href="<%= Routes.zet_routes_path(TriglavWeb.Endpoint, :match, @route.id, relation.id) %>">
                Find a match
              </a>
            </div>
          <% end %>
        </div>
      <% end %>
    </div>
        </tfoot>
      </div>
    <% end %>
  </section>

  <hr />

M priv/repo/migrations/20210227082725_create_public_transport_tables.exs => priv/repo/migrations/20210227082725_create_public_transport_tables.exs +3 -2
@@ 12,7 12,7 @@ defmodule Triglav.Repo.Migrations.CreatePublicTransportTables do
    end

    create table("public_transport_platforms") do
      add :trip_id, references("public_transport_trips")
      add :trip_id, references("public_transport_trips"), null: false
      add :relation_id, :bigint, null: false
      add :route_id, :string
      add :stop_id, :string


@@ 20,6 20,7 @@ defmodule Triglav.Repo.Migrations.CreatePublicTransportTables do
      add :way_id, :bigint
      add :sequence_id, :integer, null: false
      add :geometry, :geometry, null: false
      add :distance_from_route, :integer
    end

    create index("public_transport_trips", [:sample_trip_id], unique: true)


@@ 30,7 31,7 @@ defmodule Triglav.Repo.Migrations.CreatePublicTransportTables do
  end

  def down do
    drop table("public_transport_trips")
    drop table("public_transport_platforms")
    drop table("public_transport_trips")
  end
end