PostgreSQL Independent Event Correlation Analysis — User-Car Fitting

Background

Independent event correlation analysis is very interesting. For example, the “Passing By” function points of some apps can indicate that you’ve been in a similar location with a different person at a different time. Independent event correlation analysis can also be used for public opinion analysis, sales portfolio for commodities, and user-car fitting.

User-car fitting in the safety system processes spatio-temporal data for fitting drivers, passengers and vehicles.

User-Car Fitting

1. Create a table structure

User

Car

2. Generate test data

Taking Hangzhou as an example, the latitude and longitude ranges are as follows:

The assumed value of the active number:

2.1. Write the activity location data of the user, partition it by day and keep it for one year.

The BRIN index, which is most commonly used in time-series data, is adopted.

Create an index of user + time.

2.2. Write the activity location data of the car, partition it by day and keep it for one year.

The BRIN index, which is most commonly used in time-series data, is adopted.

Create an index of car + time.

3. Find the fitting of people and cars in a certain time interval

3.1. During the driving of the car, N points are captured and the time and position are returned.

Return the intersection of users near the N points in the corresponding time interval

Example:

3.2. During the movement of the user, N points are captured and the time and position are returned.

Return the intersection of cars near the N points in the corresponding time interval

Example:

Summary

1. Optimization ideas of storage and index.

“Time truncation + spatial sorting” storage

For example:

After the storage is modified, the BTree or BRIN index of the structure above is created.

When searching for records that appear near a certain point at a certain time, parallel search can be performed, and the number of data blocks searched is relatively small because of dense storage.

2. Other requirements: complement of missing positions. In some cases, the location information of the cars or users may not be collected, such as when passing through congested roads or during dead angles of the collector.

In the case of a vacancy in location acquisition, pgrouting, together with the road network information, is used to generate several paths to complete the missing points. And, estimate the time to obtain points and the time passing through these points.

3. Other requirements: correction of abnormal positions.

4. Fitting performance, and day partitions. 10 million users, and 10 million cars. The number of user tracks is 1 billion a day. The number of car tracks is 100 million a day.

Fitting response of millisecond level can be achieved.

Original Source

https://www.alibabacloud.com/blog/postgresql-independent-event-correlation-analysis---user-car-fitting_594989?spm=a2c41.13103877.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store