amf.public

Parameters

Name	Type	Mode
_day_id	integer	IN
_tenant_id	uuid	IN

Definition

WITH clusters AS (
        SELECT 
            id,
            centroid,
            type,
            ST_ClusterDBSCAN(centroid, eps := 0.0003, minPoints := 1) OVER (ORDER BY start_date) cluster_id
        FROM sms_loader_move
        WHERE type = 'motionless' AND day_id = _day_id AND tenant_id = _tenant_id
    ),

move_clusters AS (
        SELECT
            slm.*,
            clusters.cluster_id
        FROM sms_loader_move AS slm
        LEFT JOIN clusters ON clusters.id = slm.id
    ),

polygons AS (
        SELECT
            gen_random_uuid() AS id,
            ST_ConvexHull(ST_COLLECT(moves.centroid)) AS polygon
        FROM clusters
        JOIN sms_loader_move AS moves ON moves.id = clusters.id
        GROUP BY clusters.cluster_id
    ),

zones AS (
        SELECT
            *,
            ST_CENTROID(polygon) AS center
        FROM polygons WHERE polygon IS NOT NULL

)

INSERT INTO sms_loader_daily_zone(id, tenant_id, day_id, name, polygon, center)
    SELECT
        id,
        _tenant_id,
        _day_id,
        CONCAT(_day_id::TEXT, '_daily_zone'),
        polygon,
        center
    FROM zones;

UPDATE sms_loader_move
    SET start_daily_zone_id = intersections.zone_id
    FROM (SELECT 
            tm.id,
            tdz.id AS zone_id
        FROM sms_loader_daily_zone AS tdz, sms_loader_move AS tm
        WHERE ST_INTERSECTS(tdz.polygon, tm.centroid) = true
            AND tm.type = 'motionless'
            AND tm.day_id = _day_id AND tdz.day_id = _day_id
        ) AS intersections
    WHERE sms_loader_move.id = intersections.id;

sms_insert_loader_daily_zone_data(IN _day_id integer, IN _tenant_id uuid)

Parameters

Definition