Help Center> Cloud Stream Service> SQL Syntax Reference> Geographical Functions
View PDF

Geographical Functions

Description

Table 1 describes the basic geospatial geometric elements.

Table 1 Basic geospatial geometric element table

Geospatial geometric elements

Description

Example

ST_POINT(latitude, longitude)

Indicates a geographical point, including the longitude and latitude.

ST_POINT(1.12012, 1.23401)

ST_LINE(array[point1...pointN])

Indicates a geographical line formed by connecting multiple geographical points (ST_POINT) in sequence. The line can be a polygonal line or a straight line.

ST_LINE(ARRAY[ST_POINT(1.12, 2.23), ST_POINT(1.13, 2.44), ST_POINT(1.13, 2.44)])

ST_POLYGON(array[point1...point1])

Indicates a geographical polygon, which is a closed polygon area formed by connecting multiple geographical points (ST_POINT) with the same start and end points in sequence.

ST_POLYGON(ARRAY[ST_POINT(1.0, 1.0), ST_POINT(2.0, 1.0), ST_POINT(2.0, 2.0), ST_POINT(1.0, 1.0)])

ST_CIRCLE(point, radius)

Indicates a geographical circle that consists of ST_POINT and a radius.

ST_CIRCLE(ST_POINT(1.0, 1.0), 1.234)

You can build complex geospatial geometries based on basic geospatial geometric elements. Table 2 describes the related transformation methods.

Table 2 Transformation methods for building complex geometric elements based on basic geospatial geometric elements

Transformation Method

Description

Example

ST_BUFFER(geometry, distance)

Creates a polygon that surrounds the geospatial geometric elements at a given distance. Generally, this function is used to build the road area of a certain width for yaw detection.

ST_BUFFER(ST_LINE(ARRAY[ST_POINT(1.12, 2.23), ST_POINT(1.13, 2.44), ST_POINT(1.13, 2.44)]),1.0)

ST_INTERSECTION(geometry, geometry)

Creates a polygon that delimits the overlapping area of two given geospatial geometric elements.

ST_INTERSECTION(ST_CIRCLE(ST_POINT(1.0, 1.0), 2.0), ST_CIRCLE(ST_POINT(3.0, 1.0), 1.234))

ST_ENVELOPE(geometry)

Creates the minimal rectangle polygon including the given geospatial geometric elements.

ST_ENVELOPE(ST_CIRCLE(ST_POINT(1.0, 1.0), 2.0))

CS provides multiple functions used for performing operations on and determining locations of geospatial geometric elements. Table 3 describes the SQL scalar functions.

Table 3 SQL scalar function table

Function

Returned Data Type

Description

ST_DISTANCE(point_1, point_2)

DOUBLE

Calculates the Euclidean distance between the two geographical points.

An example is provided as follows:

Select ST_DISTANCE(ST_POINT(x1, y1), ST_POINT(x2, y2)) FROM input

ST_GEODESIC_DISTANCE(point_1, point_2)

DOUBLE

Calculates the shortest distance along the surface between two geographical points.

An example is provided as follows:

Select ST_GEODESIC_DISTANCE(ST_POINT(x1, y1), ST_POINT(x2, y2)) FROM input

ST_PERIMETER(polygon)

DOUBLE

Calculates the circumference of a polygon.

An example is provided as follows:

Select ST_PERIMETER(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]) FROM input

ST_AREA(polygon)

DOUBLE

Calculates the area of a polygon.

An example is provided as follows:

Select ST_AREA(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]) FROM input

ST_OVERLAPS(polygon_1, polygon_2)

BOOLEAN

Checks whether one polygon overlaps with another.

An example is provided as follows:

SELECT ST_OVERLAPS(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_INTERSECT(line1, line2)

BOOLEAN

Checks whether two line segments, rather than the two straight lines where the two line segments are located, intersect each other.

An example is provided as follows:

SELECT ST_INTERSECT(ST_LINE(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12)]), ST_LINE(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23)])) FROM input

ST_WITHIN(point, polygon)

BOOLEAN

Checks whether one point is contained inside a geometry (polygon or circle).

An example is provided as follows:

SELECT ST_WITHIN(ST_POINT(x11, y11), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_CONTAINS(polygon_1, polygon_2)

BOOLEAN

Checks whether the first geometry contains the second geometry.

An example is provided as follows:

SELECT ST_CONTAINS(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_COVERS(polygon_1, polygon_2)

BOOLEAN

Checks whether the first geometry covers the second geometry. This function is similar to ST_CONTAINS except the situation when judging the relationship between a polygon and the boundary line of polygon, for which ST_COVER returns TRUE and ST_CONTAINS returns FALSE.

An example is provided as follows:

SELECT ST_COVERS(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON([ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_DISJOINT(polygon_1, polygon_2)

BOOLEAN

Checks whether one polygon is disjoint (not overlapped) with the other polygon.

An example is provided as follows:

SELECT ST_DISJOINT(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

The World Geodetic System 1984 (WGS84) is used as the reference coordinate system for geographical functions. Due to offsets, the GPS coordinates cannot be directly used in the Baidu Map (compliant with BD09) and the Google Map (compliant with GCJ02). To implement switchover between different geographical coordinate systems, CS provides a series of functions related to coordinate system conversion as well as functions related to conversion between geographical distances and the unit meter. For details, see Table 4.

Table 4 Functions for geographical coordinate system conversion and distance-unit conversion

Function

Returned Data Type

Description

WGS84_TO_BD09(geometry)

Geospatial geometric elements in the Baidu Map coordinate system

Converts the geospatial geometric elements in the GPS coordinate system into those in the Baidu Map coordinate system. An example is provided as follows:

WGS84_TO_BD09(ST_CIRCLE(ST_POINT(x, y), r))

WGS84_TO_CJ02(geometry)

Geospatial geometric elements in the Google Map coordinate system

Converts the geospatial geometric elements in the GPS coordinate system into those in the Google Map coordinate system. An example is provided as follows:

WGS84_TO_CJ02(ST_CIRCLE(ST_POINT(x, y), r))

BD09_TO_WGS84(geometry)

Geospatial geometric elements in the GPS coordinate system

Converts the geospatial geometric elements in the Baidu Map coordinate system into those in the GPS coordinate system. An example is provided as follows:

BD09_TO_WGS84(ST_CIRCLE(ST_POINT(x, y), r))

BD09_TO_CJ02(geometry)

Geospatial geometric elements in the Google Map coordinate system

Converts the geospatial geometric elements in the Baidu Map coordinate system into those in the Google Map coordinate system. An example is provided as follows:

BD09_TO_CJ02(ST_CIRCLE(ST_POINT(x, y), r))

CJ02_TO_WGS84(geometry)

Geospatial geometric elements in the GPS coordinate system

Converts the geospatial geometric elements in the Google Map coordinate system into those in the GPS coordinate system. An example is provided as follows:

CJ02_TO_WGS84(ST_CIRCLE(ST_POINT(x, y), r))

CJ02_TO_BD09(geometry)

Geospatial geometric elements in the Baidu Map coordinate system

Converts the geospatial geometric elements in the Google Map coordinate system into those in the Baidu Map coordinate system. An example is provided as follows:

CJ02_TO_BD09(ST_CIRCLE(ST_POINT(x, y), r))

DEGREE_TO_METER(distance)

DOUBLE

Converts the distance value of the geographical function to a value in the unit of meter. In the following example, you calculate the circumference of a triangle in the unit of meter.

DEGREE_TO_METER(ST_PERIMETER(ST_POLYGON(ARRAY[ST_POINT(x1,y1), ST_POINT(x2,y2), ST_POINT(x3,y3), ST_POINT(x1,y1)])))

METER_TO_DEGREE(numerical_value)

DOUBLE

Convert the value in the unit of meter to the distance value that can be calculated using the geographical function. In the following example, you draw a circle which takes a specified geographical point as the center and has a radius of 1 km.

ST_CIRCLE(ST_POINT(x,y), METER_TO_DEGREE(1000))

CS also provides window-based SQL geographical aggregation functions specific for scenarios where SQL logic involves windows and aggregation. For details about the functions, see Table 5.

Table 5 Time-related SQL geographical aggregation function table

Function

Description

Example

AGG_DISTANCE(point)

Distance aggregation function, which is used to calculate the total distance of all adjacent geographical points in the window.

SELECT AGG_DISTANCE(ST_POINT(x,y)) FROM input GROUP BY HOP(rowtime, INTERVAL '1' HOUR, INTERVAL '1' DAY)

AVG_SPEED(point)

Average speed aggregation function, which is used to calculate the average speed of moving tracks formed by all geographical points in a window. The average speed is in the unit of m/s.

SELECT AVG_SPEED(ST_POINT(x,y)) FROM input GROUP BY TUMBLE(proctime, INTERVAL '1' DAY)

Precautions

None

Example

Example of yaw detection:

INSERT INTO yaw_warning
SELECT "The car is yawing"
FROM driver_behavior
WHERE NOT ST_WITHIN(ST_POINT(cast(Longitude as DOUBLE), cast(Latitude as DOUBLE)), ST_BUFFER(ST_LINE(ARRAY[ST_POINT(34.585555,105.725221),ST_POINT(34.586729,105.735974),ST_POINT(34.586492,105.740538),ST_POINT(34.586388,105.741651),ST_POINT(34.586135,105.748712),ST_POINT(34.588691,105.74997)]),0.001));

IP Functions

Table 6 IP functions

Function

Returned Data Type

Description

IP_TO_COUNTRY

STRING

Obtains the name of the country where the IP address is located. The value is returned in Chinese.

IP_TO_PROVINCE

STRING

Obtains the province where the IP address is located.

Usage:

  • IP_TO_PROVINCE(STRING ip): Determines the province where the IP address is located and returns the English province name.
  • IP_TO_PROVINCE(STRING ip, STRING lang): Determines the province where the IP is located and returns the province name of the specified language. If lang is EN, the English province name is returned. If lang is not EN, the Chinese province name is returned.
    NOTE:
    • If the province where the IP address is located cannot be obtained through IP address parsing, the country where the IP address is located is returned. If the IP address cannot be parsed, Unknown is returned.
    • The Chinese or English name returned by the function for the Chinese province is the short name, which is the same as that provided on the Chinese government official website.

      For details about the Chinese province names, visit the following website: http://www.gov.cn/guoqing/2005-09/13/content_5043917.htm

      For details about the English province names, visit the following website: http://english.gov.cn/archive/

IP_TO_CITY

STRING

Obtains the name of the city where the IP address is located. The value is returned in Chinese.

NOTE:

If the city where the IP address is located cannot be obtained through IP address parsing, the province or the country where the IP address is located is returned. If the IP address cannot be parsed, Unknown is returned.

IP_TO_CITY_GEO

STRING

Obtains the longitude and latitude of the city where the IP address is located. The parameter value is in the following format: Latitude, Longitude.

Usage:

IP_TO_CITY_GEO(STRING ip): Returns the longitude and latitude of the city where the IP address is located.