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