amf.public

Parameters

Name	Type	Mode
_scraper_id	uuid	IN
_dozer_serial	text	IN
_dozer_id	uuid	IN
_day_id	integer	IN
_tenant_id	uuid	IN
_distance_treshold	integer	IN
_azimuth_treshold	integer	IN

Definition

WITH scraper_raw_gps AS (
        SELECT
            id AS scraper_gps_id,
            time,
            coord AS scraper_coord,
            LEAD(coord, 1) OVER (ORDER BY time) AS next_scraper_coord,
            dumpster_open
        FROM sms_teltonika_scraper_gps_data
        WHERE scraper_id = _scraper_id
            AND time > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
            AND time < TO_DATE(_day_id::TEXT, 'YYYYMMDD') + 1
            AND tenant_id = _tenant_id
        ORDER BY time ASC
    ),

scraper_azimuth_calculated AS (
        SELECT
            scraper_gps_id,
            time,
            scraper_coord,
            next_scraper_coord,
            dumpster_open,
            CASE
                WHEN ST_Equals(scraper_coord, next_scraper_coord) THEN 0
                ELSE DEGREES(ST_AZIMUTH(scraper_coord, next_scraper_coord))
            END AS scraper_azimuth
        FROM scraper_raw_gps
    ),

scraper_azimuth_prev AS (
        SELECT
            *,
            LAG(scraper_azimuth, 1) OVER (ORDER BY time) AS scraper_azimuth_prev1,
            LAG(scraper_azimuth, 2) OVER (ORDER BY time) AS scraper_azimuth_prev2,
            LAG(scraper_azimuth, 3) OVER (ORDER BY time) AS scraper_azimuth_prev3
        FROM scraper_azimuth_calculated
    ),

scraper_azimuth_gaps_filled AS (
        SELECT
            scraper_gps_id,
            time,
            scraper_coord,
            next_scraper_coord,
            dumpster_open,
            CASE
                WHEN scraper_azimuth = 0 AND scraper_azimuth_prev1 <> 0 THEN scraper_azimuth_prev1
                WHEN scraper_azimuth = 0 AND scraper_azimuth_prev1 = 0 THEN scraper_azimuth_prev2
                WHEN scraper_azimuth = 0 AND scraper_azimuth_prev1 = 0 AND scraper_azimuth_prev2 = 0 THEN scraper_azimuth_prev3
                ELSE scraper_azimuth
            END AS scraper_azimuth
        FROM scraper_azimuth_prev
        ORDER BY time
    ),

dozer_raw_gps AS (
        SELECT
            id AS dozer_gps_id,
            time,
            coord AS dozer_coord,
            LEAD(coord, 1) OVER (ORDER BY time) AS next_dozer_coord
        FROM sms_teltonika_gps_data
        WHERE serial_number = _dozer_serial
            AND time > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
            AND time < TO_DATE(_day_id::TEXT, 'YYYYMMDD') + 1
            AND tenant_id = _tenant_id
        ORDER BY time ASC
    ),

dozer_azimuth_calculated AS (
        SELECT
            dozer_gps_id,
            time,
            dozer_coord,
            next_dozer_coord,
            CASE
                WHEN ST_Equals(dozer_coord, next_dozer_coord) THEN 0
                ELSE DEGREES(ST_AZIMUTH(dozer_coord, next_dozer_coord))
            END AS dozer_azimuth
        FROM dozer_raw_gps
    ),

dozer_azimuth_prev AS (
        SELECT
            *,
            LAG(dozer_azimuth, 1) OVER (ORDER BY time) AS dozer_azimuth_prev1,
            LAG(dozer_azimuth, 2) OVER (ORDER BY time) AS dozer_azimuth_prev2,
            LAG(dozer_azimuth, 3) OVER (ORDER BY time) AS dozer_azimuth_prev3
        FROM dozer_azimuth_calculated
    ),

dozer_azimuth_gaps_filled AS (
        SELECT
            dozer_gps_id,
            time,
            dozer_coord,
            next_dozer_coord,
            CASE
                WHEN dozer_azimuth = 0 AND dozer_azimuth_prev1 <> 0 THEN dozer_azimuth_prev1
                WHEN dozer_azimuth = 0 AND dozer_azimuth_prev1 = 0 AND dozer_azimuth_prev2 <> 0 THEN dozer_azimuth_prev2
                WHEN dozer_azimuth = 0 AND dozer_azimuth_prev1 = 0 AND dozer_azimuth_prev2 = 0 THEN dozer_azimuth_prev3
                ELSE dozer_azimuth
            END AS dozer_azimuth
        FROM dozer_azimuth_prev
        ORDER BY time
    ),

paired AS (
        SELECT
            *,
            (
                SELECT dozer_gps_id
                FROM dozer_azimuth_gaps_filled
                WHERE time >= (scraper_azimuth_gaps_filled.time - INTERVAL '5 second') AND time <= (scraper_azimuth_gaps_filled.time + INTERVAL '5 second')
                LIMIT 1
            ),
            (
                SELECT dozer_coord
                FROM dozer_azimuth_gaps_filled
                WHERE time >= (scraper_azimuth_gaps_filled.time - INTERVAL '5 second') AND time <= (scraper_azimuth_gaps_filled.time + INTERVAL '5 second')
                LIMIT 1
            ),
            (
                SELECT dozer_azimuth
                FROM dozer_azimuth_gaps_filled
                WHERE time >= (scraper_azimuth_gaps_filled.time - INTERVAL '5 second') AND time <= (scraper_azimuth_gaps_filled.time + INTERVAL '5 second')
                LIMIT 1
            )
        FROM scraper_azimuth_gaps_filled
    ),

calculated AS (
        SELECT
            *,
            CASE
                WHEN scraper_coord IS NULL THEN NULL
                WHEN dozer_coord IS NULL THEN NULL
                ELSE ST_DistanceSphere(scraper_coord, dozer_coord)
            END AS machine_distance,
            CASE
                WHEN scraper_azimuth IS NULL THEN NULL
                WHEN dozer_azimuth IS NULL THEN NULL
                WHEN scraper_azimuth = 0 AND dozer_azimuth = 0 THEN NULL
                ELSE ABS(scraper_azimuth - dozer_azimuth)
            END AS azimuth_delta
        FROM paired
    ),

dozer_nearby_detected AS (
        SELECT
            *,
            CASE
                WHEN machine_distance <= _distance_treshold AND azimuth_delta <= _azimuth_treshold THEN TRUE
                ELSE FALSE
            END AS dozer_nearby
        FROM calculated
    ),

dozer_nearby_prev_next AS (
        SELECT
            *,
            LAG(dozer_nearby, 1) OVER (ORDER BY time) AS prev1,
            LAG(dozer_nearby, 2) OVER (ORDER BY time) AS prev2,
            LAG(dozer_nearby, 3) OVER (ORDER BY time) AS prev3,
            LEAD(dozer_nearby, 1) OVER (ORDER BY time) AS next1,
            LEAD(dozer_nearby, 2) OVER (ORDER BY time) AS next2,
            LEAD(dozer_nearby, 3) OVER (ORDER BY time) AS next3
        FROM dozer_nearby_detected

dozer_nearby_gaps_filled AS (
        SELECT
            scraper_gps_id,
            CASE
                WHEN prev1 IS TRUE AND next1 IS TRUE THEN TRUE
                WHEN prev1 IS TRUE AND next1 IS FALSE AND next2 IS TRUE THEN TRUE
                WHEN prev1 IS TRUE AND next1 IS FALSE AND next2 IS TRUE THEN TRUE
                WHEN prev1 IS TRUE AND next1 IS FALSE AND next2 IS FALSE AND next3 IS TRUE THEN TRUE
                WHEN prev1 IS FALSE AND prev2 IS TRUE AND next1 IS FALSE AND next2 IS TRUE THEN TRUE
                WHEN prev1 IS FALSE AND prev2 IS FALSE AND prev3 IS TRUE AND next1 IS TRUE THEN TRUE
                WHEN prev1 IS FALSE AND prev2 IS TRUE AND next1 IS TRUE THEN TRUE
                WHEN prev1 IS FALSE AND next1 IS FALSE THEN FALSE -- removing short loadings
                ELSE dozer_nearby
            END AS dozer_nearby
        FROM dozer_nearby_prev_next
    )

UPDATE sms_teltonika_scraper_gps_data
    SET dozer_nearby = dozer_nearby_gaps_filled.dozer_nearby, dozer_id = _dozer_id
    FROM dozer_nearby_gaps_filled
    WHERE sms_teltonika_scraper_gps_data.id = dozer_nearby_gaps_filled.scraper_gps_id
    AND dozer_nearby_gaps_filled.dozer_nearby IS TRUE;

sms_update_scraper_gps_data_v2(IN _scraper_id uuid, IN _dozer_serial text, IN _dozer_id uuid, IN _day_id integer, IN _tenant_id uuid, IN _distance_treshold integer, IN _azimuth_treshold integer)

Parameters

Definition