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"