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)

{"__rockset_type": "GEOGRAPHY", "value": {"coordinates": [-122.325133, 37.566564], "type": "Point"}}

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

SELECT

    ST_GEOGPOINT(190, 0)

{"__rockset_type": "GEOGRAPHY", "value": {"coordinates": [-170.0, 0.0], "type": "Point"}}

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)

{"__rockset_type": "GEOGRAPHY", "value": {"coordinates": [-170.0, 80], "type": "Point"}}

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

-122.325133

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

37.566564

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": [12.0, 34.0]}}

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": [[12.0, 34.0], [56.0, 78.0], [42.0, 42.0]]}}

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

{"__rockset_type": "GEOGRAPHY", "value": {"type": "Polygon", "coordinates": [[[0.0, 0.0], [10.0, 0.0], [10.0, 10.000000000000005], [0.0, 10.0], [0.0, 0.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. 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)

    )

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

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

null

SELECT

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

null

SELECT

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

null

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]]]}}'

        )

    )

"Invalid geography: Edge 0 crosses edge 2. Please ensure that linestrings contain no edges with length of 0 or 180 degrees. Ensure polygons meet the requirements in https://github.com/google/s2geometry/blob/master/src/s2/s2polygon.h#L95"