Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL Best Practices: Real-Time Monitoring and Alerts for Senior Citizen Healthcare

PostgreSQL Best Practices: Real-Time Monitoring and Alerts for Senior Citizen Healthcare

This article discusses managing healthcare for elderly people using big data and talks about optimization methods for real-time monitoring and alerts.

By Digoal

Background

The human body is like a machine. When people get older, age-related health problems become more prevalent. Paying attention to health can prevent some problems. With the rise of big data technologies, people can use real-time monitoring and alerts to address health issues early on.

In the past, a visit to a hospital or professional physical examination clinic was necessary for health check-ups, which could be time-consuming and troublesome. With technological developments, monitoring health indicators became more convenient. Monitoring and detection wearables such as bracelets that record health indicators such as heartbeat, temperature, and activity levels have quickly gained popularity. These devices could check more and more health indicators in the future.

Wearables, such as bracelets, are devices that provide nonprofessional health monitoring. Professional institutions, such as hospitals and nursing homes, use sophisticated monitoring instruments such as sensors, cameras, and spatial detection instruments. These instruments help monitor an increasingly large number of health indicators in real time for patients in hospitals and older people in nursing homes.

For example, we can:

1) Monitor a person's movements and send an alert if they are stationary for a specified period.

2) Send an alert if the monitored person stays in bed for over 12 hours.

3) Send an alert if the monitored person kneels, stoops, or falls over and remains less than 40 cm in height for over 5 minutes (no alert is sent if people are around).

4) Send an alert if the person stays in a small space (for example, a restroom stall) for over 30 minutes.

Sensors collect data in the above situations. With all these data, how should we go about optimizing them for accurate real-time monitoring? In this blog, we'll show you how you can achieve this using PostgreSQL

Architecture

Data traffic is divided into five parts:

4

1) Data (from sensors)

2) Data conversion (formatting and stateful conversion). As an optional step, data conversion is aimed at making data identification and processing easier. Three methods are available for real-time data conversion.

2.1) Define a conversion rule using a database’s UDFs and convert data in real time when data is written into the database according to a trigger or specified rule.

2.2) Define a conversion rule using UDFs of a database and convert the data through pipeline transformation.

2.3) Convert data at the application layer and write the converted data into the database.

3) Rule definition

Define alert rules, for example, as mentioned above:

Send an alert if the person stays in a restroom stall for over 30 minutes.

To simplify access APIs, define the alert rules using a database UDFs and display them in a dashboard.

4) Real-time rule query

Query for defined rules and give alerts.

5) Alert

Optimization Methods

1) Analyze the same data record (one record may contain multidimensional attributes such as height, location, and heart rate) in multiple rule-based dimensions. For example, an alert may trigger when a monitored person's heart rate or location value meets the specified thresholds. The most common optimization method is to reduce the amount of data to be scanned, and a similar optimization method is also applied to the 9.6 kernel layer. The case provided in this article is more complicated because different rules require different record ranges. For example, an alert (probably involving thousands of records) may be given after a person stays in bed for 12 hours. However, an alert may generate if the person stays in a position that is lower than the specified height for five minutes.

2) Store the data of each sensor separately to reduce data scanning costs. That is, create a table for each sensor.

3) No need to keep all records for alerts. For example, export historical data to an external OSS table through table rotation. Query the external OSS table for detailed information, or directly connect to AnalyticDB for PostgreSQL to analyze the full data as required.

5

4) Define dynamic StreamCompute rules to reduce computation cost. For example, trigger a rule when a user (a monitored person) enters a specified state. Take the restroom scenario as an example. Data records are processed with the specified StreamCompute rules only when a user enters a restroom stall.

DEMO

Next, let’s see how an alert works in this scenario.

Send an alert if a person stays in a restroom stall for over 30 minutes.

The DEMO is simple, without considering optimization factors.

Creating a Table

create table sensor_info(  
  sid int,   -- 传感器ID  
  pos point, -- 传感器的相对坐标  
  crt_time timestamp  -- 上传时间  
  -- 其他属性略，为演示方便。  
);  
  
create index idx_sensor_info on sensor_info (sid,crt_time desc);  
  
create table userinfo (  
  uid  -- 用户和传感器的对应关系表，略  
  sid  
);  

create table statistic_obj_info (  
  objid  int,    -- 静态对象空间信息，例如床、马桶
  pos_range box  -- 对象的空间范围，如果使用postgis，请使用geometry来表示一个区间。  
);

Generating Data

insert into sensor_info select random()*1000, point(trunc((random()*10)::numeric,2), trunc((random()*10)::numeric,2)), now()+(id||' second')::interval from generate_series(1,10000000) t(id);  
  
postgres=# select * from sensor_info limit 10;  
 sid |     pos     |          crt_time            
-----+-------------+----------------------------  
 888 | (1.43,5.58) | 2017-07-31 17:23:04.620488  
 578 | (5.6,2.01)  | 2017-07-31 17:23:05.620488  
 186 | (6.98,9.91) | 2017-07-31 17:23:06.620488  
  99 | (4.1,7.46)  | 2017-07-31 17:23:07.620488  
  30 | (6.25,6.07) | 2017-07-31 17:23:08.620488  
 403 | (5.12,6.26) | 2017-07-31 17:23:09.620488  
  60 | (9.8,8)     | 2017-07-31 17:23:10.620488  
 654 | (1.83,5.41) | 2017-07-31 17:23:11.620488  
 731 | (5.72,4.67) | 2017-07-31 17:23:12.620488  
 230 | (4.99,8.3)  | 2017-07-31 17:23:13.620488  
(10 rows)  
postgres=# select * from sensor_info where sid=1 order by crt_time desc limit 10;  
 sid |     pos     |          crt_time            
-----+-------------+----------------------------  
   1 | (9.83,6.18) | 2017-11-24 10:40:35.620488  
   1 | (3.18,9.82) | 2017-11-24 10:39:30.620488  
   1 | (1.79,6.24) | 2017-11-24 10:35:15.620488  
   1 | (3.13,8.42) | 2017-11-24 10:21:35.620488  
   1 | (5.11,4.17) | 2017-11-24 10:09:22.620488  
   1 | (9.51,3.41) | 2017-11-24 10:04:00.620488  
   1 | (2.24,2.35) | 2017-11-24 09:50:33.620488  
   1 | (7.2,8.67)  | 2017-11-24 09:44:18.620488  
   1 | (2.32,4.48) | 2017-11-24 08:45:22.620488  
   1 | (0.33,9.33) | 2017-11-24 08:44:50.620488  
(10 rows)

Defining a Rule

Send an alert if a person stays in a restroom stall for over 30 minutes.

Define a relative coordinate space (box) for each restroom stall and monitor a user (a monitored elderly person) who enters any box according to the specified rule.

For more information about geometric operations, visit:

Define a rule using UDFs and generate a JSON string.

create or replace function matong_rule(  
  v_sid int,   -- 传感器ID  
  pos_range box,  -- 空间区间 , 如果使用postgis，请使用geometry来标注一个空间 
  ts interval     -- 持续时间，采用interval类型  
) returns jsonb as $$  
declare  
  v sensor_info;  -- 临时类型  
  e timestamp;    -- 最后时间  
  s timestamp;    -- 最前时间  
begin  
  for v in select * from sensor_info_1 where sid=v_sid order by crt_time desc   
  loop  
    if pos_range @> v.pos then  
      if e is null then e := v.crt_time; end if;  
      s := v.crt_time;  
    else  
      exit;  
    end if;  
  end loop;  
  
  if e-s >= ts then  
     return jsonb_build_object('sid', v_sid, 'pos_range', pos_range, 'start_time', s, 'end_time', e, 'interval', e-s);  
  else  
    return null;  
  end if;  
end;  
$$ language plpgsql strict;

In the following example,

1 in (1,'(10,10),(0,0)','1 sec') represents the sensor ID of the elderly person, '(10,10),(0,0)' represents the box parameter range of the restroom stall, and '1 sec' is the duration.

When a sensor detects an elderly person entering a monitored static object space (for example, a bed or restroom stall), the preceding rule triggers and a query executes according to the rule.

postgres=# select matong_rule(1,'(10,10),(0,0)','1 sec');  
                                                                           matong_rule                                                                             
-----------------------------------------------------------------------------------------------------------------------------------------------------------------  
 {"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "115 days 17:08:19", "pos_range": "(10,10),(0,0)", "start_time": "2017-07-31T17:32:16.620488"}  
(1 row)  
  
Time: 23.200 ms  
postgres=# select matong_rule(1,'(10,10),(1,1)','1 sec');  
                                                                      matong_rule                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------  
 {"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "01:55:13", "pos_range": "(10,10),(1,1)", "start_time": "2017-11-24T08:45:22.620488"}  
(1 row)  
  
Time: 11.157 ms  
postgres=# select matong_rule(1,'(10,10),(2,2)','1 sec');  
                                                                      matong_rule                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------  
 {"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(2,2)", "start_time": "2017-11-24T10:39:30.620488"}  
(1 row)  
  
Time: 11.325 ms  
postgres=# select matong_rule(1,'(10,10),(3,3)','1 sec');  
                                                                      matong_rule                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------  
 {"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:39:30.620488"}  
(1 row)  
  
Time: 11.019 ms

On some occasions, the sensor does not upload relative coordinates. Instead, it directly uploads static objects’ unique identifiers (such as restroom stalls and beds). For example, all static objects are numbered and provided with corresponding sensors that sense the elderly people’s existence in the space and report the monitoring data in real time.

This is much simpler and elegant because even space judgment will not be necessary. The drawback is that the elderly people cannot be monitored after they leave the monitored space, and monitoring can rely only on the sensors that they wear.

Generally, there are two common practices:

1) Processing data on the server

In this practice, data is collected and uploaded to the server for computation.

2) Processing data at the terminal

In this practice, local terminals collect and process the data, for example, in restroom stalls or on beds. In this way, data can be more accurate.

Query Rule

Output the status of all users.

For example, query for the motion status of the elderly people equipped with sensors with IDs from 1 to 100 in the restroom.

postgres=# select matong_rule(id ,'(10,10),(3,3)','1 sec') from generate_series(1,10) t(id);  
                                                                      matong_rule                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------  
 {"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:39:30.620488"}  
 {"sid": 2, "end_time": "2017-11-24T10:43:06.620488", "interval": "00:10:36", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:32:30.620488"}  
   
   
   
(10 rows)  
  
Time: 115.501 ms

Alert

If a query is matched, send an alert.

PostgreSQL also provides an impressive feature: asynchronous messaging, used for asynchronous alerts.

If you use PipelineDB, choose the real-time alert feature of transform.

CREATE TABLE t (user text, value int);

CREATE OR REPLACE FUNCTION insert_into_t()
  RETURNS trigger AS
  $$
  BEGIN
    INSERT INTO t (user, value) VALUES (NEW.user, NEW.value);
    RETURN NEW;
  END;
  $$
  LANGUAGE plpgsql;

CREATE CONTINUOUS TRANSFORM ct AS
  SELECT user::text, value::int FROM stream WHERE value > 100
  THEN EXECUTE PROCEDURE insert_into_t();

Motion Dashboard

This feature enables the track query. If there are multiple time records about an elderly person staying in a monitored space, only two of them (the arrival time and departure time) are indicated in the dashboard.

Optimization 1

As mentioned earlier, data of all sensors is stored separately to eliminate I/O amplification. Let's see the performance after eliminating I/O amplification.

postgres=#       create table sensor_info_1 (like sensor_info including all);  
CREATE TABLE  
Time: 1.765 ms  
postgres=# insert into sensor_info_1 select * from sensor_info where sid=1;  
INSERT 0 9835  
Time: 39.805 ms  
postgres=# \d sensor_info_1  
                     Table "postgres.sensor_info_1"  
  Column  |            Type             | Collation | Nullable | Default   
----------+-----------------------------+-----------+----------+---------  
 sid      | integer                     |           |          |   
 pos      | point                       |           |          |   
 crt_time | timestamp without time zone |           |          |   
Indexes:  
    "sensor_info_1_sid_crt_time_idx" btree (sid, crt_time DESC)  
  
postgres=# create or replace function matong_rule(  
postgres(#   v_sid int,   -- 传感器ID  
postgres(#   pos_range box,  -- 空间区间  
postgres(#   ts interval     -- 持续时间，采用interval类型  
postgres(# ) returns jsonb as $$  
postgres$# declare  
postgres$#   v sensor_info;  -- 临时类型  
postgres$#   e timestamp;    -- 最后时间  
postgres$#   s timestamp;    -- 最前时间  
postgres$# begin  
postgres$#   for v in select * from sensor_info_1 where sid=v_sid order by crt_time desc   
postgres$#   loop  
postgres$#     if pos_range @> v.pos then  
postgres$#       if e is null then e := v.crt_time; end if;  
postgres$#       s := v.crt_time;  
postgres$#     else  
postgres$#       exit;  
postgres$#     end if;  
postgres$#   end loop;  
postgres$#   
postgres$#   if e-s >= ts then  
postgres$#      return jsonb_build_object('sid', v_sid, 'pos_range', pos_range, 'start_time', s, 'end_time', e, 'interval', e-s);  
postgres$#   else  
postgres$#     return null;  
postgres$#   end if;  
postgres$# end;  
postgres$# $$ language plpgsql strict;  
CREATE FUNCTION  
Time: 0.469 ms  
postgres=# select matong_rule(1,'(10,10),(3,3)','1 sec');  
                                                                      matong_rule                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------  
 {"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:39:30.620488"}  
(1 row)  
  
Time: 0.620 ms

The time required for a query decreases from 11 ms to 0.6 ms.

Summary

Nursing homes require massive amounts of spatial data, time data, and rules. They need a robust database to store their data. Otherwise, they will have to process the data in the app layer, which will be inefficient.

PostgreSQL is suitable for applications in various scenarios such as real-time detection, health reporting, and trace query for elderly people in nursing homes. You can also store historical sensor data to an external OSS table after connecting your AnalyticDB for a PostgreSQL instance to an OSS instance. This helps you conveniently meet alert and analysis needs.

PostgreSQL Database Healthcare Public Opinion Analysis StreamCompute PipelineDB Independent Event Correlation Real-time Status Analysis
0 0 0
Share on

Read previous post:

Accelerate Spatial Search Using Spatial Composite Indexing

Read next post:

Database Design and Implementation of a Ride Hailing Dispatch System

digoal

282 posts | 25 followers

You may also like

Comments

digoal

282 posts | 25 followers

Related Products

More Posts by digoal

See All