USER MANUALS

Spatial Functions

Spatial functions:

ST_AREA

Description

The ST_AREA function returns the area of wkb/wkt.

SELECT st_area(wkb)/st_area(wkt) FROM view

ST_AREA_METERS

Description

The ST_AREA_METERS function returns the area_meters of wkb/wkt. This function returns the area of wkb. The units of the result are square meters. The code identifies the CRS of the geometry

Example

SELECT st_area_meters(wkb, code)/st_area_meters(wkt, code) FROM view

ST_BOUNDARY

Description

The ST_BOUNDARY function returns a new geometry that represents the combined boundary of wkb/wkt.

Example

SELECT st_boundary(wkb)/st_boundary(wkt) FROM view

ST_BUFFER

Description

The ST_BUFFER function returns a new geometry that covers all points within a given distance from the input geometry. This function supports all the spatial data types.

SELECT st_buffer(wkb, <distance:double>)/st_buffer(wkt, <distance:double>) FROM view

Example

SELECT st_buffer('POINT(0 0)', 1.0) FROM view

  • The function returns a new geometry containing the points in the given distance of the input geometry.

ST_BUFFER_METERS

Description

The ST_BUFFER_METERS function returns a new geometry that covers all points within a given distance from the input geometry. The code identifies the CRS of the geometry. The unit of distance is meters.

When the standard unit of distance of the geometry CRS is different to meters, the geometry is projected to a UTM zone, this zone is determined by the coordinates of the centroid of the geometry. The new buffered geometry is calculated in the new UTM projection, whose unit is meters. Finally this geometry will be reprojected to the source CRS. If the unit of the input CRS is already meters, the buffered geometry is calculated without transformations.

Note that, if the distance parameter is too large and makes the buffered geometry exceed the limits of the UTM zone used for computing, a loss of accuracy could result. This function supports all the spatial data types.

SELECT ST_BUFFER_METERS(wkt, code, <distance:double>) FROM view

  • The code must start with the authority and two dots (:).

Example

SELECT ST_BUFFER_METERS('POINT(43.37 -8.41)','EPSG:4326',1000) FROM view

  • This function can also receive a blob as input. The blob must express a geometry in the Well-Known Binary (wkb) format.

ST_CENTROID

Description

The ST_CENTROID function returns the geometric center of wkb/wkt. This function supports all the spatial data types.

Example

SELECT st_centroid(wkb)/st_centroid(wkt) FROM view

ST_CONTAINS

Description

The ST_CONTAINS function returns true if the geometry wkb2 is completely contained by the geometry wkb1. This function supports all the spatial data types.

Example

SELECT st_contains(wkb1, wkb2)/st_contains(wkt1, wkt2) FROM view

  • if the geometry wkb2 is completely contained by the geometry wkb1 returns true (t).

ST_CONVEXHULL

Description

The ST_CONVEXHULL function computes the smallest convex Polygon that contains all the points in the Geometry. This function supports all the spatial data types.

Example

SELECT st_convexhull(wkb)/st_convexhull(wkt) FROM view

ST_CREATE_POINT

Description

The ST_CREATE_POINT function creates a point, the parameters are the abscissa(x) and the ordinate(y), which define the location of a point in two-dimensional rectangular space.

SELECT st_create_point(<x:DOUBLE>, <y:DOUBLE>) FROM view

ST_CROSSES

Description

The ST_CROSSES function returns true if the given geometries have some interior points in common, but not all of them. This function supports all the spatial data types.

Example

SELECT st_crosses(wkb1, wkb2)/st_crosses(wkt1, wkt2) FROM view

  • If the geometries have some interior points in common, the result will be true (t).

ST_DIFFERENCE

Description

The ST_DIFFERENCE function returns a new geometry that is the part of wkb1/wkt1 that does not intersect with wkb2/wkt2. This function supports all the spatial data types.

Example

SELECT st_difference(wkb1, wkb2)/st_difference(wkt1, wkt2) FROM view

ST_DIMENSION

Description

The ST_DIMENSION function returns the dimensions of wkb.

Example

SELECT st_dimension(wkb1, wkb2)/st_dimension(wkt1, wkt2) FROM view

ST_DISJOINT

Description

The ST_DISJOINT function returns true if the given geometries do not have a point in common. This function supports all the spatial data types.

Example

SELECT st_disjoint(wkb1, wkb2)/st_disjoint(wkt1, wkt2) FROM view

  • If the given geometries do not have a point in common, the result will be true (t). If they do, the result will be false (f).

ST_DISTANCE

Description

The ST_DISTANCE function returns the minimum distance between the geometry wkb1/wkt1 and the geometry wkb2/wkt2.

Example

SELECT st_distance(wkb1, wkb2)/st_distance(wkt1, wkt2) FROM view

ST_DISTANCE_METERS

Description

The ST_DISTANCE_METERS function returns the minimum distance between the geometry wkb1/wkt1 and the geometry wkb2/wkt2 expressed in meters. The codes identify the CRS of each geometry. If any of the CRS of the inputs are different to WGS84, then they are projected to WGS84, and when the two geometries are in WGS84, the orthodromic distance between the two geometries is calculated.

Example

SELECT st_distance_meters(wkb1, code1, wkb2, code2)/st_distance_meters(wkt1, code1, wkt2, code2) FROM view

ST_ENDPOINT

Description

The ST_ENDPOINT function returns the last point of wkb/wkt. Wkb/wkt should be a linestring.

SELECT st_endpoint(wkb)/st_endpoint(wkt) FROM view

Example

ST_ENDPOINT('LINESTRING(0 2, 4 5, 7 8)')

  • The result would be:

'POINT (7 8)'

ST_ENVELOPE

Description

The ST_ENVELOPE function returns a new geometry representing the envelope (bounding box) of wkb/wkt.

Example

SELECT st_envelope(wkb)/st_envelope(wkt) FROM view

ST_EQUALS

Description

The ST_EQUALS function returns true if the given geometries represent the same one. Directionality is ignored. This function supports all the spatial data types.

Example

SELECT st_equals(wkb, wkb)/st_equals(wkt, wkt) FROM view

ST_EXTERIORRING

Description

The ST_EXTERIORRING function returns a new geometry that represents the exterior ring of wkb/wkt. Wkb/wkt should be a polygon.

Example

SELECT st_exteriorring(wkb)/st_exteriorring(wkt) FROM view

ST_GEOMETRYTYPE

Description

The ST_GEOMETRYTYPE function returns the type of wkb/wkt.

SELECT st_geometrytype(wkb)/st_geometrytype(wkt) FROM view

ST_GEOM_TO_STRUCT

This function has two signatures:

  1. st_geom_to_struct(wkb): parses a blob value that represents a geometry and converts it into a structural representation of the geometry in terms of register and arrays.

  2. st_geom_to_struct(wkt): parses a text value that represents a geometry and converts it into a structural representation of the geometry in terms of register and arrays.

The structural representation of the geometry is a VDP register (struct) with the following fields:

  • type: the geometry type (point, linestring, polygon…).

  • bounding_box: a register with the coordinates of the minimum rectangle that contains the geometry.

  • point: a register with the coordinates x and y of the point, if the type field states that the geometry is a point.

  • linestring: an array with the points that form the linestring, if the type field states that the geometry is a linestring.

  • polygon: a register defined as the exterior linestring (a.k.a. shell) and an array with the holes (linestrings) in the polygon, if the type field states that the geometry is a polygon.

  • multipoint: an array with all its points, if the type field states that the geometry is a multipoint.

  • multilinestring: an array with all its linestrings, if the type field states that the geometry is a multilinestring.

  • multipolygon: an array with all its polygons, if the type field states that the geometry is a multipolygon.

This function can receive a string as input. The string must express a geometry in the Well-Known Text (wkt) format.

Example

For example, with input = ‘LINESTRING(1 1, 5 5, 10 10, 20 20)’, the expression to use this function should be something as:

st_geom_to_struct(input)

…and the result would be:

{ type = 'LineString',

bounding_box = {

   max_x = 20.0,

   max_y = 20.0,

   min_x = 1.0,

   min_y = 1.0 },

point = null,

linestring = Array [

   { x = 1.0, y = 1.0 },

   { x = 5.0, y = 5.0 },

   { x = 10.0, y = 10.0 },

   { x = 20.0, y = 20.0 } ],

polygon = null,

multipoint = null,

multilinestring = null,

multipolygon = null }

This function can also receive a blob as input. The blob must express a geometry in the Well-Known Binary (wkb) format.

ST_GEOMETRYN

Description

The ST_GEOMETRYN function returns the Nth Geometry, N is specified by position parameter.

SELECT st_geometryn(wkb, <position:INTEGER>)/st_geometryn(wkt, <position:INTEGER>) FROM view

ST_INTERIORRINGN

Description

The ST_INTERIORRINGN function returns the Nth interior ring of wkb/wkt, the N is specified by the position parameter. Wkb/wkt should be a polygon. Returns null if the geometry is not a polygon or the given position is out of range.

Example

SELECT st_interiorringn(wkb, <position:INTEGER>)/st_interiorringn(wkt, <position:INTEGER>) FROM view

ST_INTERSECTION

Description

The ST_INTERSECTION function returns a new geometry formed by the shared portion of wkt1 and wkt2. This function supports all the spatial data types.

Example

SELECT st_intersection(wkb1, wkb2)/st_intersection(wkt1, wkt2) FROM view

ST_INTERSECTS

Description

The ST_INTERSECTS function returns true if the intersection of the given geometries does not result in an empty set.

Example

SELECT st_intersects(wkb1, wkb2)/st_intersects(wkt1, wkt2) FROM view

  • If the geometries intersect, the result will be true (t). If they do not intersect, the result will be false (f).

ST_ISCLOSED

Description

The ST_ISCLOSED function returns true if the start point and the end point are coincident. Wkb/wkt must be a linestring or a multilinestring.

Example

SELECT st_isclosed(wkb)/st_isclosed(wkt) FROM view

  • if the start point and the end point are coincident, the result will be true (t).

ST_ISEMPTY

Description

The ST_ISEMPTY function returns true if wkb is an empty geometry.

Example

SELECT st_isempty(wkb)/st_isempty(wkt) FROM view

  • if the given geometry is empty, the result will be true (t).

ST_ISSIMPLE

Description

The ST_ISSIMPLE function returns true if wkb/wkt has not anomalous geometric points, such as self intersection or self tangency.

Example

SELECT st_issimple(wkb)/st_issimple(wkt) FROM view

  • if the given geometry has not anomalous geometric points, the result will be true (t).

ST_ISRING

Description

The ST_ISRING function returns true if wkb/wkt is closed and simple. Wkb/wkt should be a linestring.

Example

SELECT st_isring(wkb)/st_isring(wkt) FROM view

  • if the given geometry is closed and simple, the result will be true (t).

ST_LENGTH

Description

The ST_LENGTH function returns the length of wkb/wkt. Wkb/wkt should be a linestring or a multilinestring.

Example

SELECT st_length(wkb)/st_length(wkt) FROM view

ST_LENGTH_METERS

Description

The ST_LENGTH_METERS function returns the length of wkb/wkt, the units of the result are meters. Wkb should be a linestring or a multilinestring. The code identifies the CRS of the geometry.

Example

SELECT st_length_meters(wkb, <code:string>)/st_length_meters(wkt, <code:string>) FROM view

ST_NUMGEOMETRIES

Description

The ST_NUMGEOMETRIES function returns the number of geometries.

Example

SELECT st_numgeometries(wkb)/st_numgeometries(wkt) FROM view

  • if the given geometry is a simple geometry, then 1 is returned. If the given geometry is a GEOMETRYCOLLECTION or a MULTI subtype, the number of geometries is returned.

ST_NUMINTERIORRINGS

Description

The ST_NUMINTERIORRINGS function returns the number of interior rings of wkb/wkt. Wkb/wkt should be a polygon.

Example

SELECT st_numinteriorrings(wkb)/st_numinteriorrings(wkt) FROM view

ST_NUMPOINTS

Description

The ST_NUMPOINTS function returns the number of points of wkb/wkt. Wkb/wkt should be a linestring.

Example

SELECT st_numpoints(wkb)/st_numpoints(wkt) FROM view

ST_OVERLAPS

Description

The ST_OVERLAPS function returns true if the given geometries share space, are of the same dimension, but are not completely contained by each other.

Example

SELECT st_overlaps(wkb1, wkb2)/st_overlaps(wkt1, wkt2) FROM view

  • if the given geometries overlap each other, result will be true (t).

ST_POINTN

Description

The ST_POINTN function returns the Nth point of wkb/wkt, N is specified by position parameter. Wkb/wkt should be a linestring.

Example

SELECT st_pointn(wkb, <position:integer>)/st_pointn(wkt, <position:integer>) FROM view

ST_RELATE

Description

  • The ST_RELATE function returns true if the first geometry is spatially related to the second geometry, by testing for intersections between the interior, boundary and exterior of wkb1 and wkb2 as specified by the values in the matrixPattern. The matrixPattern is a 9-character string that represents a matrix in the dimensionally-extended 9 intersection model (DE-9IM). Each character represents the type of intersection allowed at one of the nine intersections between the two geometries(interior, boundary and exterior). This function supports all the spatial data types.

st_relate(wkb1, wkb2, matrixPattern)/st_relate(wkt1, wkt2, matrixPattern)

Example

SELECT ST_RELATE('POINT(3 3)', ST_BUFFER('POINT(0 0)',3),'FF0FFF212') FROM view

The result will be:

true

  • The ST_RELATE function returns the maximum intersection matrix pattern that relates wkb1 and wkb2. The matrix pattern is a 9-character string that represents a matrix in the dimensionally-extended 9 intersection model(DE-9IM). Each character represents the type of intersection allowed at one of the nine intersections between the two geometries (interior, boundary and exterior). This function supports all the spatial data types.

st_relate(wkb1, wkb2)/st_relate(wkt1, wkt2)

Example

SELECT ST_RELATE('POINT(3 3)', ST_BUFFER('POINT(0 0)',3)) FROM view

The result will be:

'FF0FFF212'

ST_STARTPOINT

Description

The ST_STARTPOINT function returns the first point of wkb. Wkb should be a linestring.

SELECT st_startpoint(wkb)/st_startpoint(wkt) FROM view

Example

ST_STARTPOINT('LINESTRING(0 2, 4 5, 7 8)')

  • The result would be:

'POINT (0 2)'

ST_SYMDIFFERENCE

Description

The ST_SYMDIFFERENCE function returns a new geometry that is the portion of wkb1/wkt1 and wkb2/wkt2 that do not intersect. This function supports all the spatial data types.

SELECT st_symdifference(wkb1, wkb2)/st_symdifference(wkt1, wkt2) FROM view

ST_TOUCHES

Description

The ST_TOUCHES function returns true if the intersection of the given geometries have at least one point in common, but their interiors do not have any points in common. This function applies to all possible relationships between the different spatial data types except the pair point with point.

Example

SELECT st_touches(wkb1, wkb2)/st_touches(wkt1, wkt2) FROM view

  • if the given geometries have at least one point in common, but their interiors do not have any points in common, the result will be true (t).

ST_TRANSFORM

Description

The ST_TRANSFORM function transforms a geometry from a Coordinate Reference System (CRS) to another Coordinate Reference System. source_code identifies the coordinate reference system(CRS) of the input geometry and target_code indicates the CRS expected for the result.

Example

SELECT st_transform(wkb, <source_code:string>, <target_code:string>)/st_transform(wkt, <source_code:string>, <target_code:string>) FROM view

ST_UNION

Description

The ST_UNION function returns a new geometry formed by the union of wkt1 and wkt2. This function supports all the spatial data types.

Example

SELECT st_union(wkb1, wkb2)/st_union(wkt1, wkt2) FROM view

ST_WITHIN

Description

The ST_WITHIN function returns true if the geometry wkb1 is completely inside geometry wkb2. This function supports all the spatial data types.

Example

SELECT st_within(wkb1, wkb2)/st_within(wkt1, wkt2) FROM view

ST_WKBTOWKT

Description

The ST_WKBTOWKT function transforms a well-known binary (wkb) into a well-known text (wkt).

Example

SELECT st_wkbtowkt(wkb) FROM view

ST_WKTTOWKB

Description

The ST_WKTTOWKB function transforms a well-known text (wkt) into a well-known binary (wkb).

Example

SELECT st_wkttowkb(wkt) FROM view

ST_X

Description

The ST_X function returns the X coordinate of wkb/wkt. Wkb/wkt should be a point.

Example

SELECT st_x(wkb)/st_x(wkt) FROM view

ST_Y

Description

The ST_Y function returns the Y coordinate of wkb/wkt. Wkb/wkt should be a point.

Example

SELECT st_y(wkb)/st_y(wkt) FROM view
Add feedback