CnosDB has added support for spatiotemporal functions in the recently released version 2.4.0 .
Overview
The Spatiotemporal function is a function used to describe the structure and evolution of space-time. It has wide applications in fields such as physics, mathematics and computer science. Space-time functions can describe the position, speed, acceleration and other related properties of an object in space and time.
Usage
CnosDB will use a new data type Geometry to support geographic information systems. When creating a table, you can specify the column type as Geometry :
create table geo_table (
geo geometry(<object_type>, )
);
Currently, CnosDB uses the WKT format to store the Geometry type. There are 7 types of geometric objects under the Geometry type:
WKT is an open international standard, spelled out as Well-Known Text.
Example
Support functions
In order to better manipulate the Geometry type, CnosDB provides the ST_Geometry SQL series of functions to calculate the properties of the geometry and the relationship between the geometries
ST_AsBinary(geometry)
Convert geometric objects to WKB format. The WKB format is defined by the OpenGIS specification and is used to exchange geometric data in the form of a binary stream. The binary stream is represented by a BLOB value containing geometric WKB information.
Parameter type : Geometry
Return type : Binary
Example:
select ST_AsBinary('POINT (1 1)')
The return result is in hexadecimal:
+--------------------------------------------+
| st_AsBinary(Utf8("POINT (1 1)")) |
+--------------------------------------------+
| 0101000000000000000000f03f000000000000f03f |
+--------------------------------------------+
The first byte indicates the endianness of the data: 01
The next 4 bytes represent the geometry type of the object: 01000000
POINT values X and Y are represented by 8-byte double type data: 000000000000F03F, 000000000000F03F
ST_GeomFromWKB
Function : Convert WKB format binary to Geometry type
Parameter type : Binary
Return type : Geometry
Example:
SELECT ST_GeomFromWKB(ST_AsBinary('POINT(0 3)'))
Returned results:
+-------------------------------------------------+
| st_GeomFromWKB(st_AsBinary(Utf8("POINT(0 3)"))) |
+-------------------------------------------------+
| POINT(0 3) |
+-------------------------------------------------+
ST_Distance(geometry1, geometry2)
Function : ST_Distance returns the minimum Euclidean distance between the 2D projections of two geometries.
Parameter type : Binary
Return type : Double
Example:
distance between two points
SELECT ST_Distance('POINT(1 0)', 'POINT(0 0)',);
+----------------------------------------------------+
| st_distance(Utf8("POINT(1 0)"),Utf8("POINT(0 0)")) |
+----------------------------------------------------+
| 1.0 |
+----------------------------------------------------+
Return results:
+----------------------------------------------------+
| st_distance(Utf8("POINT(1 0)"),Utf8("POINT(0 0)")) |
+----------------------------------------------------+
| 1.0 |
+----------------------------------------------------+
point to straight line distance
SELECT ST_Distance('POINT(0 0)', 'LINESTRING (30 10, 10 30, 40 40)');
Return results:
+--------------------------------------------------------------------------+
| st_distance(Utf8("POINT(0 0)"),Utf8("LINESTRING (30 10, 10 30, 40 40)")) |
+--------------------------------------------------------------------------+
| 28.284271247461902 |
+--------------------------------------------------------------------------+
distance between planes
SELECT ST_Distance('POLYGON((0 2,1 1,0 -1,0 2))', 'POLYGON((-1 -3,-2 -1,0 -3,-1 -3))') as distance;
Return results
+--------------------+
| distance |
+--------------------+
| 1.4142135623730951 |
+--------------------+
ST_Area(geometry)
Function : Returns the Cartesian area of the 2D projection of the geometric object. The area units are the same as those used to express the coordinates of the input geometry. For points, linestrings, multipoints, and multilinestrings, this function returns 0. For a collection of geometries, it returns the sum of the areas of the geometries in the collection.
Parameter type : Geometry
Return type : Double
Example:
SELECT ST_Area('POLYGON ((40 40, 20 45, 45 30, 40 40))') as area;
+------+ | area | +------+ | 87.5 | +------+
Return results
+------+
| area |
+------+
| 87.5 |
+------+
Note: Some geometries do not support area calculation, and calculating the area for these geometries will return 0, such as: Point, MultiPoint, LineString, MultiLineString, Line. If the parameter content format is illegal, the return value is NULL.
Case: IoT scene monitoring vehicle entering the electronic fence
There is a table car below.
The table car records the real-time location of the vehicle, id uniquely indicates a vehicle, and location is the real-time coordinates of the vehicle.
create table car (
location: geometry(point, 0),
tags(id)
);
There is a circular electronic fence with coordinates 117.20, 39.12 and a radius of 0.0008.
1. Calculate the number of vehicles in the electronic fence at this time
select count(*)
from(
select max(time), id, location
from car
group by id, location
)
where st_distance(location, 'POINT (117.20, 39.12)') < 0.0008;
First, find the latest location record of each vehicle by max aggregating the time column.
Then use the spatial function st_distance to find vehicles whose distance from the electronic fence is less than the radius of the electronic fence, and finally use count to calculate the number.
2. Calculate the time when car A5678 first entered the electronic fence and the last time it was inside the electronic fence.
select min(time), max(time)
from car
where id = 'A5678' and st_distance(location, 'POINT (117.20, 39.12') < 0.0008;
Filter out the records of the specified car by id, then use the spatial function st_distance to filter out the records of the car’s coordinates in the electronic fence, and finally use the min max aggregate function to find the time.
The above are the functions of CnosDB2.4 about spatiotemporal functions. You can use these functions to process and analyze spatiotemporal data. Using spatiotemporal functions can help you solve many problems related to geographical location, whether in logistics planning, location analysis, or geographic information systems. Whether in other fields, valuable information can be extracted from spatiotemporal data to help you make smarter decisions, optimize business processes, and provide better services.
Whether you are a professional GIS expert or an ordinary user interested in geographic location data, spatiotemporal functions will provide you with powerful and useful query and analysis tools. Let’s use spatiotemporal functions together to tap the unlimited potential of geographic data! Welcome to this wonderful world of space-time analysis!