amf.public

Parameters

Name	Type	Mode
_loader_serial	text	IN
_loader_id	uuid	IN
_day_id	integer	IN
_tenant_id	uuid	IN
_site_id	uuid	IN
_parameter_id	uuid	IN

Definition

WITH
    -- Filter raw tracker data and compute distance and time period
    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
        FROM sms_teltonika_gps_data
        WHERE serial_number = _loader_serial::TEXT
            AND time >= TO_DATE(_day_id::TEXT, 'YYYYMMDD')
            AND time < TO_DATE(_day_id::TEXT, 'YYYYMMDD') + 1
        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
        FROM sms_teltonika_gps_data AS gps
        JOIN filtered ON filtered.id = gps.id
    ),

-- 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 calculated.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 calculated.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_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 calculated.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 calculated.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,
            (
                SELECT id
                FROM sms_shift_day
                WHERE EXTRACT(hour from time) * 100 + EXTRACT(minute from time) >= start_hour * 100 + start_minute
                AND EXTRACT(hour from time) * 100 + EXTRACT(minute from time) <= end_hour * 100 + end_minute
                AND day_mask = EXTRACT(isodow from time)
            ) AS shift_id,
            CASE
                WHEN filtered_speed <= 5.0 THEN 'motionless'
                WHEN filtered_speed > 5.0 THEN 'travel'
                ELSE 'unknown'
            END AS type
        FROM calculated
    ),

-- 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,
            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_loader_move(tenant_id, loader_id, day_id, loader_parameter_id, start_date, type, duration, distance, geometry, centroid, shift_day_id, shift_day_name)

-- Finalize selection
    SELECT
        _tenant_id AS tenant_id,
        _loader_id AS loader_id,
        _day_id AS day_id,
        _parameter_id AS loader_parameter_id,
        time AS start_date,
        type,
        duration,
        distance,
        geometry,
        centroid,
        shift_day_id,
        shift_day_name
    FROM geometry_calculated
    WHERE rank = 1

sms_insert_daily_loader_data(IN _loader_serial text, IN _loader_id uuid, IN _day_id integer, IN _tenant_id uuid, IN _site_id uuid, IN _parameter_id uuid)

Parameters

Definition