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

Filters trajectory points where the specified attribute field is not null and returns a new trajectory object composed of the retained points.

Syntax

trajectory ST_attrNotNullFilter(trajectory traj, cstring attr_field_name);

Returns	`trajectory`
traj	The input trajectory object.
attr_field_name	The name of the attribute field to filter on.

Parameters

Parameter	Type	Description
`traj`	trajectory	The input trajectory object.
`attr_field_name`	cstring	The name of the attribute field to filter on. All attribute field types are supported.

Description

ST_attrNotNullFilter scans every point in traj and drops points where attr_field_name is null. The remaining points form a new trajectory object that preserves the original spatial path and timeline for the retained points.

All attribute field types are supported.

Example

The following example builds a 16-point trajectory with a heading attribute. Four points (at positions 4, 7, 10, and 15 in the array, with values null) are filtered out, leaving a 12-point trajectory.

SELECT ST_attrNotNullFilter(
  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:13:39',
      '2017-01-15 09:14:48',
      '2017-01-15 09:16:28',  -- null heading, filtered out
      '2017-01-15 09:17:48',
      '2017-01-15 09:19:48',
      '2017-01-15 09:23:19',  -- null heading, filtered out
      '2017-01-15 09:30:28',
      '2017-01-15 09:34:40',
      '2017-01-15 09:36:59',  -- null heading, filtered out
      '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 09:48:49',  -- null heading, filtered out
      '2017-01-15 21:18:30'
    ],
    '{"leafcount": 16, "attributes": {"heading": {"type": "float", "length": 4, "nullable": true, "value": [23.0,23.0,23.0,null,21.0,21.0,null,72.0,72.0,null,73.0,74.0,73.0,73.0,null,73.0]}}}'
  ),
  'heading'
);

Output (12 points retained, leafcount reduced from 16 to 12):

{"trajectory":{"version":1,"type":"STPOINT","leafcount":12,"start_time":"2017-01-15 09:06:39","end_time":"2017-01-15 21:18:30","spatial":"LINESTRING(-179.48077 51.72814,-179.46731 51.74634,-179.46502 51.74934,-179.45943 51.75736,-179.4556 51.76273,-179.43419 51.78977,-179.41259 51.81643,-179.40751 51.82223,-179.40497 51.82505,-179.40242 51.82796,-179.39981 51.83095,-179.39499 51.83709)","timeline":["2017-01-15 09:06:39","2017-01-15 09:13:39","2017-01-15 09:14:48","2017-01-15 09:17:48","2017-01-15 09:19:48","2017-01-15 09:30:28","2017-01-15 09:34:40","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"],"attributes":{"leafcount":12,"heading":{"type":"float","length":4,"nullable":true,"value":[23.0,23.0,23.0,21.0,21.0,72.0,72.0,73.0,74.0,73.0,73.0,73.0]}}}}
(1 row)