search

CityOfLosAngeles / aqueduct

A shared pipeline for building ETLs and batch jobs that we run at the City of LA for Data Science Projects. Built on Apache Airflow & Civis Platform
Apache License 2.0
21 stars 6 forks source link

update route and st_make_line with ordered route #71

Open hunterowens opened 5 years ago

hunterowens commented 5 years ago

some debugging with @thekaveman points out that we might not be guaranteed that the order of points in /routes in timestamp aware. Add an order by to the trigger and rerun

thekaveman commented 5 years ago

You can see how we implemented this in the newest trigger-based process here: https://github.com/CityofSantaMonica/mds-provider-services/blob/master/db/trips/process_trip_routes.sql#L20

We break the process into two steps on trip insert:

  1. create a CTE for the points from the trip's route
  2. process the points from the CTE to insert a new row in routes, including the sorting of points by time for the line
ian-r-rose commented 5 years ago

cc @RMK0110

RMK0110 commented 5 years ago

@ian-r-rose This looks to be resolved with L59 of https://github.com/CityofSantaMonica/mds-provider-services/blob/master/db/trips/process_trip_routes.sql#L59 using:

st_makeline(array_agg(geopoint order by timepoint)) as route_line,

Is there anything else needed? - RMK0110

ian-r-rose commented 5 years ago

@RMK0110 That function is not called in our Postgres instance. You should find our version of the same function under "functions" in pgadmin, with name "process_route"