Geographic Functions

This page documents functions to construct and manipulate geographic values in Rockset. The data types page details the geographic types, how to import them, and their semantics.

ST_GEOGPOINT

ST_GEOGPOINT(longitude, latitude) Constructs a new point with the given longitude and latitude.

SELECT ST_GEOGPOINT(-122.325133, 37.566564)

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

SELECT ST_GEOGPOINT(190, 0)

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

SELECT ST_GEOGPOINT(10, 100)

ST_X

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

SELECT ST_X(ST_GEOGPOINT(-122.325133, 37.566564))

ST_Y

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

SELECT ST_Y(ST_GEOGPOINT(-122.325133, 37.566564))

ST_GEOGFROMTEXT

ST_GEOGFROMTEXT(well_known_text) Converts a well known text to a geography.

This works for points, linestrings, polygons, and multipolygons. If the well known text (WKT) is malformed, it will throw an error. Multipart geometries (other than multipolygons) 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)')

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

SELECT ST_GEOGFROMTEXT('LINESTRING(12 34, 56 78, 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. Vertices in polygons must be unique, except that the last vertex is allowed to 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))')

Multipolygons are parsed as lists of polygons

SELECT ST_GEOGFROMTEXT('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10)),((20 20, 33 20, 25 15)))')

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

ST_DISTANCE

ST_DISTANCE(geography_a, geography_b) Returns the distance, in meters, between the closest points in the two geographies.

ST_DISTANCE(geography_list, geography_b) or ST_DISTANCE(geography_a, geography_list) Return the minumum distance, in meters, between a given geography and any geography in the list.

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

Example distance between two buildings in San Mateo:

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

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

Distance to a multipolygon is the same as the distance to the closest polygon

SELECT ST_DISTANCE(ST_GEOGFROMTEXT('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10)),((20 20, 33 20, 25 15)))'), ST_GEOGPOINT(10, -1))

SELECT ST_DISTANCE(ST_GEOGFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10))'), ST_GEOGPOINT(10, -1))

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.

ST_CONTAINS(geography_list, geography_b) Returns true if and only if geography_b is entirely contained within a geography from geography_list.

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

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

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

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

A polygon that is fully contained in a union of two separate polygons in a multipolygon but not by any one of them is not contained by a multipolygon.

SELECT ST_CONTAINS(ST_GEOGFROMTEXT('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10)),((10 0, 20 0, 20 10, 10 10)))'), ST_GEOGFROMTEXT('POLYGON((5 3, 15 3, 15 6, 5 6))'))

An example of a polygon contained by a multipolygon.

SELECT ST_CONTAINS(ST_GEOGFROMTEXT('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10)),((10 0, 20 0, 20 10, 10 10)))'), ST_GEOGFROMTEXT('POLYGON((5 3, 6 3, 6 6, 5 6))'))

ST_INTERSECTS

ST_INTERSECTS(geography_a, geography_b) Returns true if and only if geography_a has some overlap with geography_b.

ST_INTERSECTS(geography_list, geography_b) or ST_INTERSECTS(geography_a, geography_list) Return true if a given geography intersects a geography from the list.

For point geographies ST_INTERSECTS 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))

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

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

Linestrings which cross return true.

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

ST_VALIDATE_GEOGRAPHY

ST_VALIDATE_GEOGRAPHY(geo_object) Given a geographic object, validates the structure for usage in queries. If the parameter is valid, null is returned. Otherwise, a string is returned with an error message.

This function validates the semantics of a geographic Point, Polygon or LineString.

SELECT ST_VALIDATE_GEOGRAPHY(ST_GEOGPOINT(1.0, 12.0))

SELECT ST_VALIDATE_GEOGRAPHY(ST_GEOGFROMTEXT('POLYGON ((0 0, 1 0, 0.5 0.1))'))

SELECT ST_VALIDATE_GEOGRAPHY(ST_GEOGFROMTEXT('LINESTRING(0 1, 2 2, 4 6)'))

SELECT
    ST_VALIDATE_GEOGRAPHY(JSON_PARSE(
        '{"__rockset_type": "GEOGRAPHY", "value": {"type": "Polygon", "coordinates": [[[0, 0], [1, 1], [0.5, 0.5], [-0.5, 0.5]]]}}'
    ))

List of functions defined in this section: