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

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

Syntax

int8[] ST_attrIntFilter(trajectory traj, cstring attr_field_name, cstring operator, int8 value);
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 filter operator. Valid values: `'='`, `'!='`, `'>'`, `'<'`, `'>='`, `'<='`, `'[]'`, `'(]'`, `'[)'`, `'()'`.
`value`, `value1`	The lower bound or fixed value for the filter.
`value2`	The upper bound for range operators.

Usage notes

Only integer attribute fields are supported.

Use the two-argument form for single-value comparisons (=, !=, >, <, >=, <=) and the three-argument form for range operators ([], (], [), ()).

Range operators follow standard interval notation:

Operator	Interval type	Includes lower bound	Includes upper bound
`'[]'`	Closed	Yes	Yes
`'[)'`	Half-open	Yes	No
`'(]'`	Half-open	No	Yes
`'()'`	Open	No	No

Examples

Set up the sample table:

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]}}}'
  )
);

The following examples all filter on the heading attribute, which contains values 0–15.

Single-value operators

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

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

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

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

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

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 operators

SELECT st_attrIntFilter(traj, 'heading', '()', 5, 8) FROM traj WHERE id = 1;
--  {6,7}

SELECT st_attrIntFilter(traj, 'heading', '(]', 5, 8) FROM traj WHERE id = 1;
--  {6,7,8}

SELECT st_attrIntFilter(traj, 'heading', '[)', 5, 8) FROM traj WHERE id = 1;
--  {5,6,7}

SELECT st_attrIntFilter(traj, 'heading', '[]', 5, 8) FROM traj WHERE id = 1;
--  {5,6,7,8}