amf.public

Parameters

Name	Type	Mode
_day_id	integer	IN
_tenant_id	uuid	IN
_eps	numeric	IN
_buffer_radius	numeric	IN

Definition

WITH clusters AS (
        SELECT
            id,
            centroid,
            type,
            ST_ClusterDBSCAN(ST_Transform(centroid, 3857), eps := _eps, minPoints := 1) OVER (ORDER BY start_date) cluster_id
        FROM sms_truck_move
        WHERE (type = 'motionless' OR type = 'loading' OR type = 'dumping')
            AND day_id = _day_id
            AND tenant_id = _tenant_id
    ),

geometries AS (
        SELECT
            gen_random_uuid() AS id,
            ST_ConvexHull(ST_COLLECT(moves.centroid)) AS geometry
        FROM clusters
        JOIN sms_truck_move AS moves ON moves.id = clusters.id
        GROUP BY clusters.cluster_id
    ),

polygons AS (
        SELECT id,
        CASE
            WHEN ST_GeometryType(geometry) = 'ST_LineString' THEN ST_Buffer(geometry, _buffer_radius, 'endcap=round join=round')
            WHEN ST_GeometryType(geometry) = 'ST_Point' THEN ST_Buffer(geometry, _buffer_radius, 'quad_segs=8')
            ELSE geometry
        END AS polygon
        FROM geometries
    ),

daily_zones AS (
        SELECT
            *,
            ST_CENTROID(polygon) AS center,
            _tenant_id AS tenant_id,
            _day_id AS day_id,
            CONCAT(_day_id::TEXT, '_daily_zone') AS name
        FROM polygons
    ),

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 > TO_DATE(_day_id::TEXT, 'YYYYMMDD') OR deleted_at IS NULL)
    ),

zone_intersections AS (
        SELECT
            dz.id AS daily_zone_id,
            z.id AS zone_id,
            ST_Area(ST_Intersection(dz.polygon, ST_SetSRID(z.polygon, 4326))) AS surface
        FROM daily_zones AS dz, active_zones AS z
        WHERE ST_IsEmpty(ST_Intersection(dz.polygon, ST_SetSRID(z.polygon, 4326))) = false
            AND dz.day_id = _day_id
            AND z.tenant_id = _tenant_id
            AND dz.tenant_id = _tenant_id
    ),

filtered_zone_intersections AS (
        SELECT a.*
        FROM zone_intersections AS a
        INNER JOIN (
            SELECT zone_id, MAX(surface) AS surface
            FROM zone_intersections
            GROUP BY zone_id
        ) AS b on a.zone_id = b.zone_id AND a.surface = b.surface
    )

INSERT INTO sms_truck_daily_zone(tenant_id, day_id, name, polygon, center, zone_id)
    SELECT
        dz.tenant_id,
        dz.day_id,
        dz.name,
        dz.polygon,
        dz.center,
        fzi.zone_id
    FROM daily_zones AS dz
    LEFT JOIN filtered_zone_intersections AS fzi ON dz.id = fzi.daily_zone_id;

UPDATE sms_truck_move
    SET start_daily_zone_id = daily_intersections.daily_zone_id
    FROM (SELECT
            tm.id AS truck_move_id,
            tdz.id AS daily_zone_id
        FROM sms_truck_daily_zone AS tdz, sms_truck_move AS tm
        WHERE ST_INTERSECTS(tdz.polygon, tm.centroid) = true
            AND (tm.type = 'motionless' OR tm.type = 'loading' OR tm.type = 'dumping')
            AND tm.day_id = _day_id AND tdz.day_id = _day_id
            AND tm.tenant_id = _tenant_id AND tdz.tenant_id = _tenant_id
        ) AS daily_intersections
    WHERE sms_truck_move.id = daily_intersections.truck_move_id;

sms_insert_truck_daily_zone_data(IN _day_id integer, IN _tenant_id uuid, IN _eps numeric, IN _buffer_radius numeric)

Parameters

Definition