Geographical Functions

Function description

Table 1 describes the basic geospatial geometric elements.

Table 1 Basic geospatial geometric element table

Geospatial geometric elements

Description

Example Value

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 Value

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))

DLI 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

Return Type

Description

ST_DISTANCE(point_1, point_2)

DOUBLE

Calculates the Euclidean distance between the two geographical points.

The following provides an example:

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.

The following provides an example:

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

ST_PERIMETER(polygon)

DOUBLE

Calculates the circumference of a polygon.

The following provides an example:

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.

The following provides an example:

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.

The following provides an example:

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.

The following provides an example:

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).

The following provides an example:

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.

The following provides an example:

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.

The following provides an example:

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.

The following provides an example:

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, DLI 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

Return 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. The following provides an example:

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. The following provides an example:

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. The following provides an example:

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. The following provides an example:

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. The following provides an example:

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. The following provides an example:

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))

DLI 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 Value

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

Note

Currently, only IPv4 addresses are supported.

Table 6 IP functions

Function

Return Type

Description

IP_TO_COUNTRY

STRING

Obtains the name of the country where the IP address is located.

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 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.

    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 name returned by the function for the province is the short name.

IP_TO_CITY

STRING

Obtains the name of the city where the IP address is located.

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.

last updated: 2024-11-20 15:34 UTC - commit: c856450509a511f9057fb9577287974ed6fce406