Label
PostgreSQL, pipelineDB, streaming, independent event correlation, public opinion analysis, real-time status analysis, recursive queries, time series data
background
People's bodies and machines are similar. With the increase of age, organs are aging, and more and more faults will occur. On the one hand, attention should be paid to maintenance. On the other hand, real-time monitoring and warning should be paid to solve the problem in its germinating state.
In the past, we had to go to a hospital or a professional physical examination institution for physical examination. With the progress of science and technology, the monitoring of some health indicators has become more convenient, such as the handring, which is also a very popular and fast monitoring and detection terminal (can detect heart rate, temperature, exercise and other indicators at present). In the future, more and more items can be detected by this terminal in real time.
If the area of health detection in the civil field of hand-ring calculation, in the professional field, such as some hospitals or nursing homes, they have more sensors, videos, spatial detection and other means, which can make real-time monitoring of more indicators for patients or the elderly in hospitals or nursing homes.
For example:
1. How long does a person's behavior track remain fixed in a certain range and continue to be alerted?
2. A person in bed space, lying for more than 12 hours, alert.
3. A person's height is less than 40cm and lasts for 5 minutes, alerting (there are people around, not alerting).(such as high blood pressure, thieves)
4. Alert a person in the tight space of the toilet for more than 30 minutes.
All of the above sensors support such data collection.
Framework
The data flow is divided into five parts:
1. Data (from sensors)
2. Data transformation (formatting, stateful)
Optional steps. The goal of data conversion is to make the data easier to recognize and process. There are three methods for real-time data conversion.
2.1. Conversion rules are defined in the UDF of a database and are translated in real time when they are brought into the database through triggers or rules.
2.2. Conversion rules are defined in the UDF of the database and converted through pipeline Transforms.
2.3. Convert at the application level, and the converted data is repositored.
3. Define rules.
Define early warning rules, such as the one mentioned earlier:
A person in the tight space of the toilet, more than 30 minutes, alert.
To simplify the access interface, rules can be defined in the UDF of a database and presented through a view.
4. Real-time rule query.
Query defined rules and alert.
5. Early warning.
Optimizing ideas
1. The same data (a record may contain attributes of multiple dimensions such as height, location, heart rate) may be calculated on multiple rule dimensions, such as an early warning rule on heart rate and an early warning rule on location.
Reducing the amount of data scanned is a common optimization method, and a similar optimization method is used at the 9.6 kernel level.
PostgreSQL 9.6 Kernel Optimized Aggregation Code Optimized OP Reuse
However, this example is more complex because different rules may involve different ranges of records, such as a spatial dimension in bed that may not alert for 12 hours (there may be thousands of records), and a height dimension that may require an alert for 5 minutes.
2. Data from the same sensor should be stored as independently as possible to reduce the cost of data scanning.That is, one table per sensor.Similar optimization method references
3. Since early warning does not require all records to be preserved, you can use rotate to export historical data to an OSS external table.(Occasionally when you need to pinpoint details, you can query, and when you need a lot of analysis, you can directly dock HybridDB for PostgreSQL for analysis)
Introduction to PostgreSQL data rotate usage - covering historical data by time
4. Dynamic flow calculation rules to reduce computational load.For example, when a user enters a state, the corresponding rule is triggered.For example, a user enters the toilet space before performing a flow calculation rule operation in the toilet space.
DEMO
Take this example to show how to warn:
A person in the tight space of the toilet, more than 30 minutes, alert.
DEMO is not so complex, regardless of optimization factors.
Building tables
create table sensor_info( sid int, -- sensor ID pos point, -- Relative coordinates of sensors crt_time timestamp -- Upload Time -- Other attributes are slight for demonstration purposes. ); create index idx_sensor_info on sensor_info (sid,crt_time desc); create table userinfo ( uid -- Corresponding relationship table between user and sensor, omitted sid ); create table statistic_obj_info ( objid int, -- Static object spatial information, such as bed, toilet pos_range box -- The spatial extent of the object, if used postgis，Please use geometry To represent an interval. );
Generate 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)
Define Rules
A person in the tight space of the toilet, more than 30 minutes, alert.
Each toilet defines a relative coordinate range that monitors the rules for the elderly when the user is sensed to enter.
Geometric operation please refer to
https://www.postgresql.org/docs/10/static/functions-geometry.html
perhaps
http://postgis.net/documentation/
Use UDF to define rules and output a JSON.
create or replace function matong_rule( v_sid int, -- sensor ID pos_range box, -- Spatial Interval , If used postgis，Please use geometry To label a space ts interval -- Duration, using interval type ) returns jsonb as $$ declare v sensor_info; -- Temporary type e timestamp; -- Last Time s timestamp; -- Earliest time 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;
Example,
1 is the sensor ID of the elderly, the middle BOX is the interval range of the toilet, and the third parameter is the duration.
When an elderly person is detected to enter a static object space that needs to be monitored (for example, into the bed space, into the toilet space), the above rules are triggered to query.
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
In fact, the possibilities of sensor upload are not relative coordinates, but direct upload of static objects (toilet, bed,....).Unique identification (e.g. full numbering of static objects and corresponding sensors can sense the presence of elderly people in this space and report monitoring data in real time.)
This makes it simpler, not even necessary for interval judgments, and more elegant.However, the disadvantage is that if the elderly leave the monitoring area, they cannot be monitored, or they have to rely on the seniors'sensors.
A summary of these two forms:
1. Light terminal, heavy server.
The terminal's capability is weak, it is only a basic data collection, all sent to the server for calculation.
2. Heavy terminal, light server.
The terminal has a strong ability to collect data, and has some computing power. At the same time, a static terminal (such as bed and toilet) is added to the building to link the seniors (sensors) who enter this space and report more accurate judgment data.
Query Rules
Output the status of all users.
For example, query seniors with sensor IDs 1-100 for status of activity in toilet space.
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
early warning
Alert if there are log returns.
Another interesting feature for PostgreSQL is asynchronous messages, which can be used for asynchronous alerts.
"Notfy/listen - database asynchronous broadcasting from radio meter to database applet"
From WeChat Applets to Database Applets, Ghost knows what I've been through
"Posgres+socket.io+nodejs Real-time Map Application Practice"
If you are using pipelinedb, you can also choose the real-time alert function of transform.
http://docs.pipelinedb.com/continuous-transforms.html#built-in-transform-triggers
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();
Activity Market
Track query shows how long an elderly person stays in each place, with multiple records in the same place, and only two (arrival and departure times) are kept when drawing a large disk.
The method is as follows.
Case of Vehicle Networking, Track Cleaning - Aliyun RDS PostgreSQL Best Practices - Window Functions
Introduction to Optimizing Point 1
An optimization of IO amplification was mentioned earlier. By storing all sensor data separately, the problem of IO amplification can be completely eliminated.Let's see how the IO cancellation works:
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, --Sensor ID postgres(# pos_range box, --Spatial interval postgres(# ts interval -- duration, using interval type postgres(# ) returns jsonb as $$ postgres$# declare postgres$# v sensor_info; --Temporary type postgres$# e timestamp; --last time postgres$# s timestamp; --earliest time 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
Increased to 0.6 milliseconds compared to 11 milliseconds per query.
Summary
Nursing homes are one of the typical cases of great health care, which involves a lot of spatial data, time data, and a lot of rules.Need a powerful database to support, otherwise all data will be moved to the APP level for processing, which is inefficient.
PostgreSQL supports the real-time detection of the health of the elderly in nursing homes, the application of scenarios such as health report, track query, etc. At the same time, historical sensor data can be communicated with HybridDB for PostgreSQL through OSS to achieve one-stop warning and analysis requirements.
Reference resources
PostgreSQL 9.6 Kernel Optimized Aggregation Code Optimized OP Reuse
Case of Vehicle Networking, Track Cleaning - Aliyun RDS PostgreSQL Best Practices - Window Functions
Pan Jinlian Changed History - An Application of PostgreSQL Public Opinion Event Analysis
Data Inbound Real-Time Conversion - trigger, rule
Introduction to PostgreSQL data rotate usage - covering historical data by time