TSDB now supports space-time services to help users process spatially geo-related data more efficiently.

Instructions

Current SQL query interface of TSDB supports a variety of spatially geo-related functions, including 2D calculation and spherical calculation. With the strong abilities of such functions, users can easily calculate and analyze spatial geo-related data by using SQL, to mine data value.

Scenario 1: Determine whether a device with a device ID of ABC123 is in a specific area

The following image represents the device position data stored by TSDB: the location information for each device is stored in two fields (field), namely, x_value and y_value, x_value and y_value represent the coordinates of the device in a two-dimensional coordinate system, and licenseID (Tag) is used to represent the device number. The data may be considered as a two-dimensional table (see below), for which space-time services are used by constructing spatial functions.

For example: Determine whether a device with a device ID of ABC123 is in a specific area (excluding boundaries); and assume the area is a quadrilateral surrounded by four points, namely, (200, 200), (400, 200), (400, 400) and (200, 400).

SQL statement:

select * from DeviceLocation where ST_Contains(ST_GeometryFromText('POLYGON ((200 200,400 200,400 400,200 400,200 200))'), ST_Point(x_value,y_value)) and LicenseID = ‘ABC123’

Return to get the following data to prove that the device with the device ID of ABC123 is in the specified area at the time of 1523973720000. ~~~~

Scenario 2: Calculate whether a vehicle passes an area within a certain radius centered on a certain point in the moving

TSDB's space-time service also supports spherical distance calculation. Vehicle location and trajectory are usually located by GPS information, so the real trajectory needs to be calculated by spherical calculation. Referring to the example above, we can replace the x_value and y_value in the example above with the actual longitude and latitude to indicate actual location of the vehicle:

For example: Calculate whether a vehicle passes an area within a radius of 200m centered on a point (40.17222, 80.175) in the moving. SQL statement:

select * from VehicleLocation where LicenseID=’BJAFR673’and ST_Distance(to_spherical_geography(ST_Point(longitude, latitude)),to_spherical_geography(ST_Point(40.17222, 80.175))) <= 200

Return to get the following data to prove that the vehicle with the license ID Jing AFR673 passes the specified area at the time of 1523973720000.

Actual Application

Vehicle Monitoring

[^_^]:
	1. * Required. This section is a three-level task, and three-level task is only required in Baidu AI Cloud Documentation.
	2. Application scenario, precondition and notes may be written.
	3. See Markdown cheat sheet if you have no idea of links. Links include: in-page links, inter-page links, other product links and image links.
	4. Results that can be viewed must be given in the last step.

Use the Time Series Database (TSDB) to store time and space data. TSDB can monitor the real-time current driving route of the vehicle with the help of space-time services. Besides, it can give a timely alarm in exceptional circumstances, such as the vehicle being off the established route, and can view history driving tracks of the vehicle when needed. In the scenario of maintenance car operation, use the TSDB to determine whether the maintenance car is operating in the specified area, whether the maintenance car is driving off the scheduled track, while identifying which rescue vehicle is the nearest to a site, to realize the overall optimization and control of the vehicle.

Track Analysis

Use the Time Series Database (TSDB) to store time and space data. TSDB can calculate and analyze the relationship between various types of tracks with the help of space-time services, for example, analyzing and summarizing the hot spots where the crowd is most densely gathered, the time periods at which the most congestion takes place on a particular road, and whether exceptional people and vehicles enter a particular area. Track analysis can be adopted in a wide variety of business scenarios, such as community safety, on-demand location (e.g. office buildings, hospitals, and shopping malls), and traffic diversion.

Reference to Space-time Service Functions

For a reference to the use of SQL statements, see Support SQL Query.

Constructing Functions of Space-time Services

• ST_Point

Statement

Geometry ST_Point (double X, double Y)

Function

Return a geometry (Geometry) object of a point (Point) type based on the specified coordinate value (X, Y).

Example

ST_Point(1.0，2.0)

Return a geometry (Geometry) object of a point (Point) type with an x-coordinate of 1.0 and a y-coordinate of 2.0.

• ST_LineFromText

Statement

Geometry ST_LineFromText (varchar WKT)

Function

Return a geometry (Geometry) object of a linestring (LineString) type from a string expression of Well-Known Text (WKT).

Example

ST_LineFromText('LINESTRING(1.0 1.5，1.0 0.5)') 

Return a geometry (Geometry) object of a linestring (LineString) type with a starting point (1.0,1.5) and an end point of (1.0,0.5).

• ST_Polygon

Statement Geometry ST_Polygon (varchar WKT) Function

Return a geometry (Geometry) object of a polygon (Polygon) type from a string expression of Well-Known Text (WKT).

Example

ST_Polygon('POLYGON ((1 1，1 4，4 4，4 1))') 

Return a geometry (Geometry) object of a polygon (Polygon) type defined by points (1, 1), (1, 4), (4, 4), and (4, 1).

• ST_GeometryFromText

Statement

Geometry ST_GeometryFromText (varchar WKT)

Function

Return a geometry (Geometry) object from a string expression of Well-Known Text (WKT).

Example

ST_GeometryFromText('POLYGON ((0 0，1 0，1 1，0 1，0 0))') 

Return a geometry (Geometry) object of a polygon (Polygon) type defined by points (0, 0), (0, 1), (1, 1), and (1, 0).

• to_spherical_geography

Statement

SphericalGeography to_spherical_geography (Geometry geometry)

Function

Convert a geometry (Geometry) object to a spherical geography (SphericalGeography) object.

Example

to_spherical_geography(ST_Point(40.172, 80.175))

Returns a spherical geography object that identifies the point at 40.172 degrees east longitude and 80.175 degrees north latitude on the earth. -180, 0 is West Longitude, 0, 180 is East Longitude, -90, 0 is South Latitude, and 0, 90 is North Latitude.

Relationship Function of Space-time Services

• ST_Contains

Statement

boolean ST_Contains (Geometry A, Geometry B)

Function

If no point in Geometry B is contained in Geometry A, and at least one point inside Geometry B is contained inside Geometry A, return true, otherwise, return false.

Example

ST_Contains(ST_Polygon('POLYGON ((0 0,1 0,1 1,0 1))'), ST_Point(0.5，0.5))=true

Point (0.5, 0.5) falls completely within a polygon defined by points (0, 0), (0, 1), (1, 1), (1, 0), then return true.

• ST_Equals

Statement

boolean ST_Equals(Geometry A, Geometry B)

Function

If a geometry (Geometry) object A and a geometry (Geometry) object B indicate the same object, return true, otherwise, return false.

Example

ST_Equals(ST_GeometryFromText('POLYGON ((1 1, 1 3, 3 3, 3 1))'), ST_GeometryFromText('POLYGON ((3 3，3 1，1 1，1 3))')=true

As the two polygons completely overlap, return true.

• ST_Intersects

Statement

boolean ST_Intersects (Geometry A, Geometry B)

Function

If a geometry (Geometry) object A and a geometry (Geometry) object B intersect in two-dimensional space, return true, otherwise, return false.

Example

ST_Intersects(ST_GeometryFromText('POLYGON((1 1, 1 3, 3 3, 3 1))'), ST_GeometryFromText('POLYGON ((4 4, 4 5, 5 5, 5 4))'))=false

As two rectangles do not intersect, return false.

• ST_Overlaps

Statement

boolean ST_Overlaps (Geometry A, Geometry B)

Function

If a geometry (Geometry) object A and a geometry (Geometry) object B partially overlap, and one does not completely contain the other, return true, otherwise, return false.

Example

ST_Overlaps(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'), ST_GeometryFromText('LINESTRING (1 1, 4 4)'))=false

Since a line with the starting point (1, 1) and the end point (4, 4) is fully contained in a polygon defined by points (1, 1), (1, 4), (4, 4), (4, 1), even the two intersect, ST_Overlaps return false.

Access Functions of Space-time Services

• ST_Area

Statement

double ST_Area (Geometry A)

Function

Return an area of a geometry (Geometry) object A of a polygon (Polygon) type in two-dimensional space.

Example

ST_Area(ST_GeometryFromText('POLYGON ((2 2, 2 6, 6 6, 6 2))'))=16.0

Return an area of 16.0 of a polygon defined by points (2, 2), (2, 6), (6, 6), and (6, 2).

• ST_Area

Statement

double ST_Area (SphericalGeometry A)

Function

Return an area of a geometry (Geometry) object A of a polygon (Polygon) type on the Earth's sphere.

Example

ST_Area(to_spherical_geography(ST_Polygon('POLYGON ((0 0,0 90,90 0))')))=6.375825913974858E13

Return a spherical area of 6.375825913974858E13 square meters of a polygon defined by points (0, 0), (0, 90), and (90, 0) on the Earth's sphere. The Earth's radius herein is 6371.01 km.

• ST_Distance

Statement

double ST_Distance (Geometry A, Geometry B)

Function

Return the shortest Cartesian distance of a geometry (Geometry) object A and a geometry (Geometry) object B in two-dimensional space.

Example

ST_Distance(ST_Point(50, 100), ST_Point(150, 150))= 111.80339887498948

Return the shortest Cartesian distance of 111.80339887498948 between point (50, 100) and point (150, 150).

• ST_Distance

Statement

double ST_Distance (SphericalGeometry A, SphericalGeometry B)

Function

Return the shortest distance with units as meters of spherical geometry (SphericalGeometry) object A and spherical geometry (SphericalGeometry) object B on the Earth's sphere.

Example

ST_Distance(to_spherical_geography(ST_Point(40.175, 80.175)), to_spherical_geography(ST_Point(40.5, 80.5)))= 36643.77019025462

Return a spherical distance of 36643.77019025462 m between point (40.175, 80.175) and point (40.5, 80.5) on Earth. The Earth's radius herein is 6371.01 km.

• ST_Length

Statement

double ST_Length (LineString A)

Function

Return the length of a geometry (Geometry) object A of a linestring (LineString) type in two-dimensional space.

Example

ST_Length(ST_GeometryFromText('LINESTRING (0 0, 2 2)'))= 2.8284271247461903

Return the length of 2.8284271247461903 of a line with the starting point (0, 0) and the end point (2, 2).

• ST_X

Statement

double ST_X (Point A)

Function

Return an x-coordinate of a geometry (Geometry) object A of a point (Point) type.

Example

ST_X(ST_GeometryFromText('POINT (1 2)'))=1.0

Return the x-coordinate of the point (1, 2) as 1.0.

• ST_Y

Statement

double ST_Y (Point A)

Function

Return a y-coordinate of a geometry (Geometry) object A of a point (Point) type.

Example

ST_Y(ST_GeometryFromText('POINT (1 2)'))=2.0

Return the y-coordinate of the point (1, 2) as 2.0.