Rockset

    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"