ST_attrIntFilter-AnalyticDB(AnalyticDB)-阿里云帮助中心

Filters trajectory points by an integer attribute field and returns the matching attribute values as an array.

Syntax

Single-value filter — matches points where the attribute field satisfies a comparison against one value:

int8[] ST_attrIntFilter(trajectory traj, cstring attr_field_name, cstring operator, int8 value);

Range filter — matches points where the attribute field falls within a value range:

int8[] ST_attrIntFilter(trajectory traj, cstring attr_field_name, cstring operator, int8 value1, int8 value2);

Parameters

Parameter	Description
`traj`	The trajectory object.
`attr_field_name`	The name of the attribute field to filter on.
`operator`	The comparison or range operator. See the operator tables below for supported values.
`value`	(Single-value form) The value to compare against.
`value1`	(Range form) The lower bound of the range.
`value2`	(Range form) The upper bound of the range.

Supported operators

Comparison operators (single-value form):

Operator	Description
`'='`	Equal to
`'! ='`	Not equal to
`'>'`	Greater than
`'>='`	Greater than or equal to
`'<'`	Less than
`'<='`	Less than or equal to

Range operators (range form, requires value1 and value2):

Operator	Description
`'[]'`	Closed interval — includes both `value1` and `value2`
`'()'`	Open interval — excludes both `value1` and `value2`
`'[)'`	Left-closed, right-open — includes `value1`, excludes `value2`
`'(]'`	Left-open, right-closed — excludes `value1`, includes `value2`

Description

ST_attrIntFilter only works with integer-type attribute fields.

The function scans all points in the trajectory, applies the filter condition to the specified attribute field, and returns an int8[] array of the attribute values for all matching points.

Examples

The following examples use a trajectory with a heading attribute (integer, values 0–15).

Set up the table and data:

CREATE TABLE traj (id integer, traj trajectory);

INSERT INTO traj (traj) VALUES (
  ST_makeTrajectory(
    'STPOINT'::leaftype,
    'LINESTRING(-179.48077 51.72814,-179.46731 51.74634,-179.46502 51.74934,
     -179.46183 51.75378,-179.45943 51.75736,-179.45560 51.76273,
     -179.44845 51.77186,-179.43419 51.78977,-179.41259 51.81643,
     -179.41001 51.81941,-179.40751 51.82223,-179.40497 51.82505,
     -179.40242 51.82796,-179.39981 51.83095,-179.39734 51.83398,
     -179.39499 51.83709)'::geometry,
    ARRAY[
      '2017-01-15 09:06:39'::timestamp, '2017-01-15 09:14:48',
      '2017-01-15 09:13:39', '2017-01-15 09:16:28',
      '2017-01-15 09:19:48', '2017-01-15 09:17:48',
      '2017-01-15 09:23:19', '2017-01-15 09:34:40',
      '2017-01-15 09:30:28', '2017-01-15 09:36:59',
      '2017-01-15 09:38:09', '2017-01-15 09:39:18',
      '2017-01-15 09:40:40', '2017-01-15 09:47:38',
      '2017-01-15 21:18:30', '2017-01-15 09:48:49'
    ],
    '{"leafcount": 16, "attributes": {"heading": {"type": "integer", "length": 4, "nullable": false,
      "value": [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]}}}'
  )
);

Comparison filter examples:

-- Greater than
SELECT ST_attrIntFilter(traj, 'heading', '>', 5) FROM traj WHERE id = 1;
-- {6,7,8,9,10,11,12,13,14,15}

-- Greater than or equal to
SELECT ST_attrIntFilter(traj, 'heading', '>=', 5) FROM traj WHERE id = 1;
-- {5,6,7,8,9,10,11,12,13,14,15}

-- Less than
SELECT ST_attrIntFilter(traj, 'heading', '<', 5) FROM traj WHERE id = 1;
-- {0,1,2,3,4}

-- Less than or equal to
SELECT ST_attrIntFilter(traj, 'heading', '<=', 5) FROM traj WHERE id = 1;
-- {0,1,2,3,4,5}

-- Equal to
SELECT ST_attrIntFilter(traj, 'heading', '=', 5) FROM traj WHERE id = 1;
-- {5}

-- Not equal to
SELECT ST_attrIntFilter(traj, 'heading', '! =', 5) FROM traj WHERE id = 1;
-- {0,1,2,3,4,6,7,8,9,10,11,12,13,14,15}

Range filter examples (between 5 and 8, with different boundary inclusion):

-- Open interval (): excludes both 5 and 8
SELECT ST_attrIntFilter(traj, 'heading', '()', 5, 8) FROM traj WHERE id = 1;
-- {6,7}

-- Left-open, right-closed (]: excludes 5, includes 8
SELECT ST_attrIntFilter(traj, 'heading', '(]', 5, 8) FROM traj WHERE id = 1;
-- {6,7,8}

-- Left-closed, right-open [): includes 5, excludes 8
SELECT ST_attrIntFilter(traj, 'heading', '[)', 5, 8) FROM traj WHERE id = 1;
-- {5,6,7}

-- Closed interval []: includes both 5 and 8
SELECT ST_attrIntFilter(traj, 'heading', '[]', 5, 8) FROM traj WHERE id = 1;
-- {5,6,7,8}