After a period of public testing and warm support from customers, Ali Cloud space-time database has been commercially sold on September 10, 2019!

Product introduction

The spatio-temporal database can store and manage data including time series and spatial geographic location. Our social production, economic activity and social interaction is closely related to the empty data at the same time, such as sensor networks, mobile Internet, radio frequency identification, global positioning system (GPS) devices such as moment output data, time and space data volume is growing so fast, which brings challenges for spatio-temporal data storage and management, the traditional database is difficult to deal with data. Spatial and temporal data is a kind of high dimensional data. The general relational database is more suitable for storing numeric and character data, but also lacks relevant operators. Ali Cloud spatiotemporal database has spatiotemporal data model, spatiotemporal index and spatiotemporal operator, which is fully compatible with SQL and SQL/MM standards. It supports integrated storage of spatiotemporal data and business data, seamless connection, and easy integration and use.

Applicable scenario

Traffic monitoring and analysis, logistics distribution, wearable device monitoring, new energy vehicle monitoring, LBS, map services, etc.

Product features

  • Time series data and spatial data are effectively unified to meet the requirements of large-scale spatio-temporal data storage and query, and facilitate the analysis and utilization of data from multiple dimensions.
  • Based on the PostgreSQL extension, the PostgreSQL index is improved to greatly improve the spatiotemporal search performance, and is compatible with the existing PostgreSQL ecosystem.
  • Storage relies on Aliyun Pangu system, with data reliability of more than 6 9;
  • Automatic backup and recovery capabilities;
  • With a perfect high availability architecture, support automatic switching between host and standby;
  • Senior experts in the field of time and space to provide support for the customer’s business escort;

Product purchase process

Before purchasing a spatio-temporal database, the following prerequisites must be met:

  • You have registered an Aliyun account and completed the real-name authentication. Otherwise, please register an Aliyun account first.
  • You already have aliYun Private network (VPC). You can only create an instance in a VPC (you can then access the instance through a VPC or a public network). If no VPC network exists, log in to the PRIVATE network (VPC) console to create a VPC, and then create a private network and switch in the region and availability zone. For details about creating a VPC, see Creating a VPC.

Taking “Huabei 1 (Hangzhou)” region as an example, the specific creation process is shown below.

ACTION1: Create a VPC instance in the specified area

For example, in Huabei 1(Hangzhou), select East China 1(Hangzhou). From the switch option, select the required availability zone, such as Hangzhou Availability Zone B. The concepts of Region, Availability Zone, VPC, and switch will be used in the subsequent creation of the spatio-temporal database instance.

After creating a VPC, you can view the VPC details

ACTION2: Create a spatio-temporal database instance


  1. Region: East China 1(Hangzhou)
  2. Availability zone: East China 1 Availability zone B
  3. Version: Select basic or high availability version. For details, refer to the documentation
  4. Private network (VPC): Select the newly created VPC
  5. Private network switch: Select the switch in the newly created VPC

ACTION3: After the purchase is successful, log in to the TSDB console to view the instance details



ACTION4: On the Instance Details page, view Public Network Address and VPC Network Address, and set the network whitelist.

Set the VPN and public network parameters to 0.0.0.0/0 for testing convenience.

ACTION5: On the Instance Details page, choose Account Management on the left to go to the page for creating an account and create a high-permission account



At this point, the entire spatiotemporal database has been initialized. You can connect to the spatiotemporal database through an external network or a VPC private network.

Data write query

Space-time database writing and query is very convenient, read and write using standard SQL, users can operate the database through JDBC/ODBC driver, read and write operations. Users can also write and query data to the spatio-temporal database through the PSQL interactive terminal. Here are a few simple examples:

Create a space-time table:

CREATE TABLE tsdb_test( uid bigint, time  timestamp, 
                       speed float, position geometry(Point,4326) );
SELECT create_hypertable('tsdb_test'.'time', chunk_time_interval => interval '1 hour');Copy the code

Write data:

INSERT INTO tsdb_test 
VALUES (1001, 'the 2019-03-11 16:34:15, 102.2, ST_SetSRID (ST_MakePoint (10.3, 20.1), 4326)), (1001,'the 2019-03-11 16:34:16', 100.1, ST_SetSRID (ST_MakePoint (10.4, 20.1), 4326)), (1002,'the 2019-03-11 16:34:17', 60.0, ST_SetSRID (ST_MakePoint (10.5, 20.2), 4326)), (1002,'the 2019-03-11 16:34:18', 61.0, ST_SetSRID (ST_MakePoint (10.6, 20.2), 4326)), (1003,'the 2019-03-11 16:34:20', 39.0, ST_SetSRID (ST_MakePoint (10.7, 20.2), 4326)), (1003,'the 2019-03-11 16:34:21', 30.0, ST_SetSRID (ST_MakePoint (10.8, 20.2), 4326));Copy the code

Users can query data through the interactive terminal as follows:

SELECT time,uid,speed,ST_AsText(position) 
FROM tsdb_test 
WHERE time >'the 2019-03-11 16:00:00' AND 
      time < 'the 2019-03-11 18:00:00'AND ST_Contains (ST_SetSRID (ST_MakeBox2D (ST_Point (2.4, 5.5), the ST_Point (13.0, 26.1)), 4326), the position). +---------------------+---------------+-----------------+---------------------+ | TIME | UID | SPEED | ST_ASTEXT | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 16:34:15 2019-03-11 | 1001 | | 102.2 16:34:16 POINT (10.3 20.1) | | 2019-03-11 | 1001 | | 100.1 POINT (10.4 20.1) | | 16:34:17 2019-03-11 | 1002 | | 60 16:34:18 POINT (10.5 20.2) | | 2019-03-11 | | 1002 | 61 POINT (10.6 20.2) | | 16:34:20 2019-03-11 | 1003 | | 39 POINT (10.7) 16:34:21 20.2) | | 2019-03-11 | 1003 | | 30 POINT (10.8 20.2) | +---------------------+---------------+-----------------+---------------------+Copy the code

Update data:

UPDATE tsdb_test 
setPosition = ST_SetSRID(ST_MakePoint(11.1,22.2),4326) WHERE uid=1002;Copy the code

Temporal and spatial analysis function

Users can use the time & space analysis function to analyze and query the tables in the spatio-temporal database. Taking the vehicle data in the shared vehicle platform as the background, several simple examples are given.

Aggregate by time window

Take 5 minutes as an aggregation time window to obtain the maximum speed of vehicles in the shared vehicle platform; Common aggregation functions include sum, Max, min, AVG, etc

SELECT uid,time_bucket('5 minutes', time) AS interval, max(speed) 
FROM tsdb_test 
WHERE uid='1002' and 
      time < 'the 2019-04-01 01:13:42'GROUP BY uid, interval ORDER BY interval DESC; +---------------+---------------------+---------------+ | UID | INTERVAL | MAX | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1002 | 2019-03-11 16:30:00 | 61 | +---------------+---------------------+---------------+Copy the code

Filter by time period and distance

Returns vehicles that are more than 17 m away from the specified object at some time.

SELECT time,uid,speed,ST_AsText(position) 
FROM tsdb_test 
WHERE time > 'the 2019-01-01 01:02:00' and 
      time < 'the 2019-04-01 01:11:02' and 
      ST_Distance('SRID=4326; POINT (2.4 5.5) ': : geometry, position) > 17.0; +---------------------+---------------+-----------------+---------------------+ | TIME | UID | SPEED | ST_ASTEXT | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 16:34:17 2019-03-11 | 1002 | | 60 16:34:18 POINT (11.1 22.2) | | 2019-03-11 | | 1002 | 61 POINT (11.1 22.2) | +---------------------+---------------+-----------------+---------------------+Copy the code

Common attribute value filtering

Returns the records of vehicles with “speed >60” in a certain period of time according to the numerical limit set by the user. For example: “>”, “<“, “=”, “<=”, “>=”, “! = “.

SELECT time,uid,speed,ST_AsText(position) 
FROM tsdb_test 
WHERE time > 'the 2019-03-01 01:02:00' and 
      time < 'the 2019-03-15 01:11:02'and speed > 60; +---------------------+---------------+-----------------+---------------------+ | TIME | UID | SPEED | ST_ASTEXT | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 16:34:15 2019-03-11 | 1001 | | 102.2 16:34:16 POINT (10.3 20.1) | | 2019-03-11 | 1001 | | 100.1 POINT (10.4 20.1) | | 16:34:18 2019-03-11 | | 1002 | 61 POINT (11.1 22.2) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +Copy the code

For the specific usage of space-time database, you can refer to ali Cloud space-time database – development guide

Ali Cloud spatiotemporal database is committed to promoting the ecological development of spatiotemporal domain, providing customers with low-cost and high-performance services, so that the value of spatiotemporal data online!

The original link

This article is the original content of the cloud habitat community, shall not be reproduced without permission.