SQL Reference > Geographic Functions

Geographic Functions

This page documents functions to construct and manipulate geographic values in Rockset. Refer to the data types page for more information about the geographic types.

#ST_GEOGPOINT

ST_GEOGPOINT(longitude, latitude)

Constructs a new point with the given longitude and latitude.

If the longitude is outside the range [-180, 180], it will wrap around.

If the latitude is outside the range [-90, 90], it will wrap around the poles, potentially changing the longitude as well.

#ST_X

ST_X(point)

Given a geographical point, returns its longitude. If the parameter is a linestring or polygon, and error is thrown.

#ST_Y

ST_Y(point)

Given a geographical point, returns its latitude. If the parameter is a linestring or polygon, and error is thrown.

#ST_GEOGFROMTEXT

ST_GEOGFROMTEXT(well_known_text)

Converts a well known text to a geography.

This works for points, linestrings, and polygons. If the well known text (WKT) is malformed, it will throw an error. Multipart geometries are not supported.

Each point is specified by two decimal numbers. Note that longitude is specified first, and latitude second. This is how you would create a point at 34N 12E:

SELECT
ST_GEOGFROMTEXT('POINT(12 34)')
{"__rockset_type":"GEOGRAPHY","value":{"type":"Point","coordinates":[34.00000000000001,12]}}

This example creates a linestring from 34N 12E to 78N 56E to 42N 42E:

SELECT
ST_GEOGFROMTEXT('LINESTRING(12 34, 56 78, 42 42)')
{"__rockset_type":"GEOGRAPHY","value":{"type":"LineString","coordinates":[[34.00000000000001,12],[78,56],[42,42]]}}

Polygons contain all of the space on the left side of their boundary, with respect to the order in which the points are specified. Therefore polygons smaller than half of the surface of the Earth should be specified in counterclockwise order. Polygons must not have duplicated vertices, except for the last vertex, which may be the same as the first. Here is an example of a roughly square polygon around the west coast of Africa:

SELECT
ST_GEOGFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10))')
{"__rockset_type":"GEOGRAPHY","value":{"type":"Polygon","coordinates":[[[0,0],[0,10],[10.000000000000004,10],[10,0]]]}}

#ST_ASTEXT

ST_ASTEXT(geography)

Converts a geography to a well known text.

This is approximately the inverse of ST_GEOGFROMTEXT.

SELECT
ST_ASTEXT(ST_GEOGFROMTEXT('POINT(12 34)'))
'POINT(12 34)'

#ST_DISTANCE

ST_DISTANCE(geography_a, geography_b)

Returns the distance, in meters, between the closest points in the two geographies.

This distance is approximate, as it assumes the Earth is spherical, while it is actually slightly ellipsoidal. This may result in an error up to 0.5%.

The distance between two buildings in San Mateo:

SELECT
ST_DISTANCE(
ST_GEOGPOINT(-122.325133, 37.566564),
ST_GEOGPOINT(-122.325924, 37.563300)
)
369.5764608286218

The distance between a linestring and point is the minimum distance between the two:

SELECT
ST_DISTANCE(
ST_GEOGFROMTEXT('LINESTRING(0 0, 20 0)'),
ST_GEOGPOINT(10, 0)
)
0

#ST_CONTAINS

ST_CONTAINS(geography_a, geography_b)

Returns true if and only if geography_b is entirely contained within geography_a.

If geography_a is not a polygon, it will always return false, as linestrings and points have no area. Polygons are considered to have geodesic edges on a spherical earth.

SELECT
ST_CONTAINS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))'),
ST_GEOGPOINT(0.5, 0.5)
)
true
SELECT
ST_CONTAINS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))'),
ST_GEOGPOINT(2, 2)
)
false

The second polygon overlaps with, but is not contained by, the first.

SELECT
ST_CONTAINS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))'),
ST_GEOGFROMTEXT('POLYGON((-1 -1, 2 -1, 2 2, -1 2))')
)
false

If we put the larger polygon first, geography_a contains geography_b.

SELECT
ST_CONTAINS(
ST_GEOGFROMTEXT('POLYGON((-1 -1, 2 -1, 2 2, -1 2))'),
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))')
)
true

#ST_INTERSECTS

ST_INTERSECTS(geography_a, geography_b)

Returns true if and only if geography_a has some overlap with geography_b.

If the second argument is a point, it is identical to ST_CONTAINS.

SELECT
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))'),
ST_GEOGPOINT(0.5, 0.5)
)
true
SELECT
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))'),
ST_GEOGPOINT(2, 2)
)
false

Though the second polygon is larger, they intersect.

SELECT
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1))'),
ST_GEOGFROMTEXT('POLYGON((0 0, 2 0, 2 1, 0 1))')
)
true

Linestrings which cross return true.

SELECT
ST_INTERSECTS(
ST_GEOGFROMTEXT('LINESTRING(0 0, 1 1)'),
ST_GEOGFROMTEXT('LINESTRING(0 1, 1 0))')
)
true