ST_attrNullFilter-ApsaraDB RDS(RDS)-阿里云帮助中心

Filters trajectory points where the specified attribute field value is null, and returns a new trajectory object containing only those points.

Syntax

trajectory ST_attrNullFilter(trajectory traj, cstring attr_field_name);

Parameters

Parameter	Type	Description
`traj`	trajectory	The source trajectory object.
`attr_field_name`	cstring	The name of the attribute field to filter on.

Usage notes

Supports all attribute field types.

Example

This example creates a trajectory with 16 points. The heading attribute (float, nullable) has null values at indices 3, 6, 9, and 14 (zero-based). ST_attrNullFilter extracts those four points and returns them as a new trajectory.

-- Build the source trajectory
WITH traj AS (
  SELECT 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',
      '2017-01-15 09:17:48', '2017-01-15 09:19:48',
      '2017-01-15 09:23:19', '2017-01-15 09:30:28',
      '2017-01-15 09:34:40', '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 09:48:49', '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]}}}'
  ) AS t
)
-- Extract the 4 points where heading is null (indices 3, 6, 9, 14)
SELECT ST_attrNullFilter(t, 'heading') FROM traj;

The result is a trajectory with 4 points, one for each null heading value:

{
  "trajectory": {
    "version": 1,
    "type": "STPOINT",
    "leafcount": 4,
    "start_time": "2017-01-15 09:16:28",
    "end_time": "2017-01-15 09:48:49",
    "spatial": "LINESTRING(-179.46183 51.75378,-179.44845 51.77186,-179.41001 51.81941,-179.39734 51.83398)",
    "timeline": [
      "2017-01-15 09:16:28",
      "2017-01-15 09:23:19",
      "2017-01-15 09:36:59",
      "2017-01-15 09:48:49"
    ],
    "attributes": {
      "leafcount": 4,
      "heading": {
        "type": "float",
        "length": 4,
        "nullable": true,
        "value": [null, null, null, null]
      }
    }
  }
}

What's next

ST_attrFilter: Filter trajectory points by a specific attribute value.
ST_attrRangeFilter: Filter trajectory points where an attribute value falls within a specified range.