amf.public

Parameters

Name	Type	Mode
_scraper_id	uuid	IN
_day_id	integer	IN
_tenant_id	uuid	IN
_site_id	uuid	IN
_parameter_id	uuid	IN

Definition

/** Select new data **/
    WITH filtered AS (
        SELECT
            id,
            time,
            ST_DistanceSphere(coord, LEAD(coord, 1) OVER (ORDER BY time)) AS distance,
            EXTRACT(EPOCH FROM (LEAD(time, 1) OVER (ORDER BY time) - time)) AS duration,
            dumpster_open,
            dozer_nearby,
            dozer_id
        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
    ),

-- Format distance and duration and compute and format speed from computed distance and duration
    calculated AS (
        SELECT
            gps.id,
            gps.time,
            gps.coord,
            CASE
                WHEN filtered.distance IS NULL THEN 0.00
                ELSE ROUND((filtered.distance)::numeric, 2)
            END AS filtered_distance,
            CASE
                WHEN filtered.duration IS NULL THEN 0.00
                ELSE ROUND((filtered.duration)::numeric, 2)
            END AS filtered_duration,
            CASE
                WHEN filtered.distance IS NULL THEN 0.00
                ELSE ROUND((3.6 * (filtered.distance / filtered.duration))::numeric, 2)
            END AS filtered_speed,
            gps.ign,
            filtered.dumpster_open,
            filtered.dozer_nearby,
            filtered.dozer_id
        FROM sms_teltonika_scraper_gps_data AS gps
        JOIN filtered ON filtered.id = gps.id
        ORDER BY time
    ),

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,
            LEAD(dozer_nearby, 1) OVER (ORDER BY time) AS next1,
            LEAD(dozer_nearby, 2) OVER (ORDER BY time) AS next2
        FROM calculated
    ),

dozer_nearby_gaps_filled AS (
        SELECT
            id,
            time,
            coord,
            filtered_distance,
            filtered_speed,
            filtered_duration,
            ign,
            dumpster_open,
            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 prev2 IS TRUE AND prev1 IS FALSE AND next1 IS TRUE THEN TRUE
                ELSE dozer_nearby
            END AS dozer_nearby,
            dozer_id
        FROM dozer_nearby_prev_next
        ORDER BY time
    ),

-- Categorize records by move type
    typed AS (
    SELECT
            *,
            (
                SELECT sd.id
                FROM sms_shift_day AS sd
                LEFT JOIN sms_shift AS s ON s.id = sd.shift_id
                LEFT JOIN tenants AS t on t.id = _tenant_id
                WHERE dozer_nearby_gaps_filled.time AT TIME ZONE t.time_zone >= TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.start_hour, ':', sd.start_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE t.time_zone
                AND dozer_nearby_gaps_filled.time AT TIME ZONE t.time_zone <= TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.end_hour, ':', sd.end_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE t.time_zone
                AND SUBSTRING(REVERSE(sd.day_mask::BIT(7)::VARCHAR), EXTRACT(isodow from time)::INT, 1) = '1'
                AND
                (
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
                    ) OR
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to IS NULL
                    )
                )
                AND s.tenant_id = _tenant_id
                AND sd.tenant_id = _tenant_id
                AND s.site_id = _site_id
                LIMIT 1
            ) AS shift_day_id,
            (
                SELECT sd.name
                FROM sms_shift_day AS sd
                LEFT JOIN sms_shift AS s ON s.id = sd.shift_id
                LEFT JOIN tenants AS t on t.id = _tenant_id
                WHERE dozer_nearby_gaps_filled.time::timestamp AT TIME ZONE (SELECT current_setting('TIMEZONE')) AT TIME ZONE t.time_zone >= TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.start_hour, ':', sd.start_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS')
                AND dozer_nearby_gaps_filled.time::timestamp AT TIME ZONE (SELECT current_setting('TIMEZONE')) AT TIME ZONE t.time_zone < TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.end_hour, ':', sd.end_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS')
                AND SUBSTRING(REVERSE(sd.day_mask::BIT(7)::VARCHAR), EXTRACT(isodow from time)::INT, 1) = '1'
                AND
                (
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
                    ) OR
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to IS NULL
                    )
                )
                AND s.tenant_id = _tenant_id
                AND sd.tenant_id = _tenant_id
                AND s.site_id = _site_id
                LIMIT 1
            ) AS shift_day_name,
            CASE
                WHEN ign IS FALSE THEN 'engine_off'
                WHEN coord IS NULL THEN 'no_gps_data'
                WHEN dozer_nearby IS TRUE THEN 'loading'
                WHEN dumpster_open IS TRUE THEN 'dumpster_open'
                WHEN filtered_speed <= 1.0 THEN 'motionless'
                WHEN filtered_speed > 1.0 THEN 'travel'
                ELSE 'unknown'
            END AS type
        FROM dozer_nearby_gaps_filled
        ORDER BY time
    ),

active_zones AS (
        SELECT *
        FROM sms_zone
        WHERE ((valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND valid_to > TO_DATE(_day_id::TEXT, 'YYYYMMDD')) OR
	    (valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND valid_to IS NULL)) AND deleted_at IS NULL
    ),

-- Intersect move coordinates with fixed zones
    intersected AS (
        SELECT
            t.id,
            z.id AS zone_id
        FROM typed AS t, sms_zone AS z
        WHERE
        (
            (
                z.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                z.valid_to > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
            ) OR
            (
                z.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                z.valid_to IS NULL
            )
        ) AND
        z.deleted_at IS NULL AND
        z.tenant_id = _tenant_id AND
        z.site_id = _site_id AND
        ST_IsEmpty(ST_Intersection(t.coord, ST_SetSRID(z.polygon, 4326))) = false
    ),

-- Partition zone-intersected moves
    intersected_partitioned AS (
        SELECT
            intersected.*,
            (ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)) AS numberer
        FROM intersected
    ),

-- Rank partioned zone-intersected moves
    intersected_ranked AS (
        SELECT
            intersected_partitioned.*,
            ROW_NUMBER() OVER (PARTITION BY id, numberer ORDER by id) AS rank
        FROM intersected_partitioned
        ORDER BY id
    ),

-- Filter ranked zone-intersected moves
    intersected_filtered AS (
        SELECT *
        FROM intersected_ranked
        WHERE rank = 1
    ),

-- Intersect move coordinates with fixed zones
    zoned AS (
        SELECT
            t.*,
            i.zone_id
        FROM typed AS t
        LEFT JOIN intersected_filtered AS i ON t.id = i.id
    ),

-- Partition records by row numberer
    partitioned AS (
        SELECT
            zoned.*,
            (ROW_NUMBER() OVER (ORDER BY time) - ROW_NUMBER() OVER (PARTITION BY type ORDER BY time)) AS numberer
        FROM zoned
    ),

-- Aggregate duration and distance and calculate centroid and linestring from aggregated positions
    linestring_calculated AS (
        SELECT
            time,
            type,
            numberer,
            ROW_NUMBER() OVER (PARTITION BY type, numberer ORDER by time) AS rank,
            SUM(filtered_duration) OVER (PARTITION BY type, numberer ORDER BY time DESC) AS duration,
            SUM(filtered_distance) OVER (PARTITION BY type, numberer ORDER BY time DESC) AS distance,
            ST_MakeLine(coord) OVER (PARTITION BY type, numberer ORDER BY time) as linestring,
            ST_Centroid(ST_UNION(coord) OVER (PARTITION BY type, numberer ORDER BY time)) AS centroid,
            zone_id,
            dozer_id,
            shift_day_id,
            shift_day_name
        FROM partitioned
        ORDER BY time
    ),

-- Filter last linestring or centroid if linestring is single point
    linestring_filtered AS (
        SELECT
            numberer,
            type,
            CASE
                WHEN ST_NumPoints(linestring) > 1 THEN linestring
                ELSE ST_Centroid(linestring)
            END AS geometry
        FROM linestring_calculated
        WHERE (numberer, rank, type) IN (SELECT numberer, MAX(rank) as rank, type FROM linestring_calculated GROUP BY type, numberer)
    ),

-- Add geometry from linestring_filtered to linestring_calculated
    geometry_calculated AS (
        SELECT
            lc.*,
            lf.geometry
        FROM linestring_calculated AS lc
        JOIN linestring_filtered AS lf ON lc.numberer = lf.numberer AND lc.type = lf.type
    )

/** Insert new data **/
    INSERT INTO sms_scraper_move(tenant_id, scraper_id, dozer_id, day_id, scraper_parameter_id, start_date, type, duration, distance, geometry, centroid, zone_id, shift_day_id, shift_day_name)

SELECT
        _tenant_id AS tenant_id,
        _scraper_id AS scraper_id,
        dozer_id,
        _day_id AS day_id,
        _parameter_id AS scraper_parameter_id,
        time AS start_date,
        type,
        duration,
        distance,
        geometry,
        centroid,
        zone_id,
        shift_day_id,
        shift_day_name
    FROM geometry_calculated
    WHERE rank = 1
    ORDER BY start_date

sms_insert_daily_scraper_data_v2(IN _scraper_id uuid, IN _day_id integer, IN _tenant_id uuid, IN _site_id uuid, IN _parameter_id uuid)

Parameters

Definition