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
_inclination_treshold	integer	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
    ),

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
    ),

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

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
    ),

loading_detected AS (
        SELECT
            scraper_gps_id,
            CASE
                WHEN dumpster_open IS TRUE AND machine_distance <= _distance_treshold AND azimuth_delta <= _azimuth_treshold THEN TRUE
                ELSE FALSE
            END AS dozer_nearby
        FROM calculated
    )

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

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

Parameters

Definition