• 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 - how to import them and how to reason about 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(0, 100)`
`{"__rockset_type": "GEOGRAPHY", "value": {"coordinates": [-180.0, 80.00000000000001], "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. 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.0], [10.0, 0.0], [10.0, 10.000000000000005], [0.0, 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.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` 