Move Centerline Crashes to Service Road Polygon Centroids Where Appropriate

[patrickm02L]

This is related to issue #6363.

The VZ team has asked for an additional enhancement related to the lambda function. They would like crashes which are associated to service road polygons by the lambda function also be spatially moved so that the crash position is adjusted to the centroid of the polygon to which they are being associated.

Acceptance Criteria:

• Identify centroid via GraphQL query in lambda function
• Mutate crash position to centroid when a centerline crash is associate to a service road polygon

[frankhereford]

I think I'm going to have to rework my approach into a couple of queries, which isn't ideal in a lambda function, but the queries can be made to be quick. That said, here's what I've tried:

At the root of it, I wanted to run this query:

with target_location as (
  select ST_X(ST_Centroid(l.shape)) as centroid_longitude,
  ST_Y(ST_Centroid(l.shape)) as centroid_latitude,
  ST_Centroid(l.shape) as centroid_geometry
  from atd_txdot_locations l
  where l.location_id = <input_target_location::text>
  )
update atd_txdot_crashes 
set longitude_primary = t.centroid_longitude,
    latitude_primary = t.centroid_latitude,
    position = t.centroid_geometry
from target_location t 
where crash_id = <input_crash_id>

Hasura doesn't expose the ST_Centroid, ST_X, and ST_Y functions, and they recommend that you wrap something that needs these PostGIS functions up in a custom query -- totally reasonable. So you get this:

CREATE OR REPLACE FUNCTION public.move_crash_to_location_centroid(input_crash_id integer, input_target_location text)
 RETURNS SETOF atd_txdot_crashes
 LANGUAGE sql
 volatile
AS $function$
with target_location as (
  select ST_X(ST_Centroid(l.shape)) as centroid_longitude,
  ST_Y(ST_Centroid(l.shape)) as centroid_latitude,
  ST_Centroid(l.shape) as centroid_geometry
  from atd_txdot_locations l
  where l.location_id = input_target_location::text
  )
update atd_txdot_crashes 
set longitude_primary = t.centroid_longitude,
    latitude_primary = t.centroid_latitude,
    position = t.centroid_geometry
from target_location t 
where crash_id = input_crash_id
returning atd_txdot_crashes.*
$function$
;

Sadly however, you can not track VOLATILE queries in the 1.X releases of Hasura, which we run for the VZDB. They added the ability to track a VOLATILE query as a mutation in 2.0.

So, I'm now going to rework my approach to get the latitude and longitude via a custom, tracked STABLE function, and then use a mutation to set those. This will, in turn, kick off another round of the lambda function AND the atd_txdot_crashes_update_audit_log() function which will use ST_Point() to set the geometry in the position field.

The order of these things will matter, and I'm not sure how to control which trigger gets priority in the postgres level, let alone manage that through Hasura. If the Hasura trigger beats the audit_log() trigger to the punch, the wrong "position" will be used to select a polygon to link the crash to.

A second infinite loop condition will also exist if a crash wants to be moved to a centroid of a polygon, but that centroid itself rests on a centerline of a level 5 road. Very unlikely, but not impossible.

I would be curious for anyone's thoughts; I think this may be a complication of keeping crash locations in two places and having to keep them in sync, in the latitude_primary, longitude_primary pair of fields as well as the native PostGIS geometry format.

@patrickm02L Let's sync when you have a second to game-plan how this should move forward in light of the upcoming release.

[patrickm02L]

We're going to table this until we can do a Hasura 2.0 upgrade.

[patrickm02L]

After further discussion, we're decoupling this issue from a Hasura 2.0 upgrade, and will go with the work around to get the next release launched.

[patrickm02L]

via @frankhereford

For the upcoming launch, we want two things:

1. Have tests ready to confirm that the lambda function is working as expected.
2. Have a query or python script that moves all existing crashes which are on main-lane and would be candidates to move to a service road location's centroid.

Also, add these to the release notes.