Skip to content

UDF Accessors

Jump to bottom Edit New page
Sarah Ambrose edited this page Mar 26, 2015 · 7 revisions

Accessors

ST_Area

Overloads:

  • ST_Area(ST_Polygon) returns the area of polygon or multipolygon

Example:

SELECT ST_Area(ST_Polygon(1,1, 1,4, 4,4, 4,1)) FROM src LIMIT 1;  --  9.0

ST_Centroid

Overloads:

  • ST_Centroid(polygon) returns the point that is the center of the polygon's envelope

Example:

> SELECT ST_Centroid(ST_GeomFromText('polygon ((0 0, 3 6, 6 0, 0 0))')) FROM src LIMIT 1;  -- POINT(3 3)
> SELECT ST_Centroid(ST_GeomFromText('polygon ((0 0, 0 8, 8 0, 0 0))')) FROM src LIMIT 1;  -- POINT(4 4)

ST_CoordDim

Overloads:

  • ST_CoordDim(geometry) return count of coordinate components

Example:

> SELECT ST_CoordDim(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 2
> SELECT ST_CoordDim(ST_PointZ(1.5,2.5, 3) FROM src LIMIT 1;  -- 3
> SELECT ST_CoordDim(ST_Point(1.5, 2.5, 3., 4.)) FROM src LIMIT 1;  -- 4

ST_Dimension

Overloads:

  • ST_Dimension(geometry) return spatial dimension of geometry

Example:

> SELECT ST_Dimension(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 0
> SELECT ST_Dimension(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- 1
> SELECT ST_Dimension(ST_Polygon(2,0, 2,3, 3,0)) FROM src LIMIT 1;  -- 2

ST_Distance

Overloads:

  • ST_Distance(ST_Geometry1, ST_Geometry2) returns the distance between 2 ST_Geometry objects

Example:

SELECT ST_Distance(ST_Point(0.0,0.0), ST_Point(3.0,4.0)) FROM src LIMIT 1;  --  5.0

ST_GeodesicLengthWGS84

Overloads:

  • ST_GeodesicLengthWGS84(line) Returns distance along line on WGS84 spheroid, in meters, for geographic coordinates. Requires the geometry to be in in WGS84 spatial reference, else returns NULL.

Example:

SELECT ST_GeodesicLengthWGS84(ST_SetSRID(ST_Linestring(0.0,0.0, 0.3,0.4), 4326)) FROM src LIMIT 1; -- 55km
SELECT ST_GeodesicLengthWGS84(ST_GeomFromText('MultiLineString((0.0 80.0, 0.3 80.4))', 4326)) FROM src LIMIT 1; -- 45km

ST_GeometryN

Overloads:

  • ST_GeometryN(ST_GeometryCollection, n) return the nth ST_Geometry in the collection (1-based index)

Example:

SELECT ST_GeometryN(ST_GeomFromText('multipoint ((10 40), (40 30), (20 20), (30 10))'), 3) FROM src LIMIT 1;  -- ST_Point(20 20)
SELECT ST_GeometryN(ST_GeomFromText('multilinestring ((2 4, 10 10), (20 20, 7 8))'), 2) FROM src LIMIT 1;  -- ST_Linestring(20 20, 7 8)

ST_Is3D

Overloads:

  • ST_Is3D(geometry) return true if the geometry object is three-dimensional

Example:

> SELECT ST_Is3D(ST_Polygon(1,1, 1,4, 4,4, 4,1)) FROM src LIMIT 1;  -- false
> SELECT ST_Is3D(ST_LineString(0.,0., 3.,4., 0.,4., 0.,0.)) FROM src LIMIT 1;  -- false
> SELECT ST_Is3D(ST_Point(3., 4.)) FROM src LIMIT 1;  -- false
> SELECT ST_Is3D(ST_PointZ(3., 4., 2)) FROM src LIMIT 1;  -- true

ST_IsClosed

Overloads:

  • ST_IsClosed(ST_[Multi]LineString) return true if the linestring or multi-line is closed

Example:

SELECT ST_IsClosed(ST_LineString(0.,0., 3.,4., 0.,4., 0.,0.)) FROM src LIMIT 1;  -- true
SELECT ST_IsClosed(ST_LineString(0.,0., 3.,4.)) FROM src LIMIT 1;  -- false

ST_IsEmpty

Overloads:

  • ST_IsEmpty(geometry) return true if the geometry object is empty of geometric information

Example:

> SELECT ST_IsEmpty(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- false
> SELECT ST_IsEmpty(ST_GeomFromText('point empty')) FROM src LIMIT 1;  -- true

ST_IsMeasured

Overloads:

  • ST_IsMeasured(geometry) return true if the geometry object is three-dimensional

Example:

> SELECT ST_IsMeasured(ST_Polygon(1,1, 1,4, 4,4, 4,1)) FROM src LIMIT 1;  -- false
> SELECT ST_IsMeasured(ST_LineString(0.,0., 3.,4., 0.,4., 0.,0.)) FROM src LIMIT 1;  -- false
> SELECT ST_IsMeasured(ST_Point(3., 4.)) FROM src LIMIT 1;  -- false
> SELECT ST_IsMeasured(ST_PointM(3., 4., 2)) FROM src LIMIT 1;  -- true

ST_IsSimple

Overloads:

  • ST_IsSimple(geometry) return true if geometry is simple

Example:

> SELECT ST_IsSimple(ST_Point(1.5, 2.5)) FROM src LIMIT 1; -- true
> SELECT ST_IsSimple(ST_LineString(0.,0., 1.,1., 0.,1., 1.,0.)) FROM src LIMIT 1; -- false

ST_Length

Overloads:

  • ST_Length(line) returns the length of line

Example:

SELECT ST_Length(ST_Line(0.0,0.0, 3.0,4.0)) FROM src LIMIT 1;  --  5.0

ST_M

Overloads:

  • ST_M(geometry) return true if the geometry object is three-dimensional

Example:

> SELECT ST_M(ST_PointM(3., 4., 2)) FROM src LIMIT 1;  -- 2

ST_MaxM

Overloads:

  • ST_MaxM(geometry) returns the maximum M coordinate of geometry

Example:

SELECT ST_MaxM(ST_PointM(1.5, 2.5, 2)) FROM src LIMIT 1;  -- 2
SELECT ST_MaxM(ST_LineString('linestring m (1.5 2.5 2, 3.0 2.2 1)')) FROM src LIMIT 1;  -- 1

ST_MaxX

Overloads:

  • ST_MaxX(geometry) returns the maximum X coordinate of geometry

Example:

> SELECT ST_MaxX(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 1.5
> SELECT ST_MaxX(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- 3.0

ST_MaxY

Overloads:

  • ST_MaxY(geometry) returns the maximum Y coordinate of geometry

Example:

> SELECT ST_MaxY(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 2.5
> SELECT ST_MaxY(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- 2.5

ST_MaxZ

Overloads:

  • ST_MaxZ(geometry) returns the maximum Z coordinate of geometry

Example:

SELECT ST_MaxZ(ST_PointZ(1.5, 2.5, 2)) FROM src LIMIT 1;  -- 2
SELECT ST_MaxZ(ST_LineString('linestring z (1.5 2.5 2, 3.0 2.2 1)')) FROM src LIMIT 1;  -- 1

ST_MinM

Overloads:

  • ST_MinM(geometry) returns the minimum M coordinate of geometry

Example:

SELECT ST_MinM(ST_PointM(1.5, 2.5, 2)) FROM src LIMIT 1;  -- 2
SELECT ST_MinM(ST_LineString('linestring m (1.5 2.5 2, 3.0 2.2 1)')) FROM src LIMIT 1;  -- 1

ST_MinX

Overloads:

  • ST_MinX(geometry) returns the minimum X coordinate of geometry

Example:

> SELECT ST_MinX(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 1.5
> SELECT ST_MinX(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- 3.0

ST_MinY

Overloads:

  • ST_MinY(geometry) returns the minimum Y coordinate of geometry

Example:

> SELECT ST_MinY(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 2.5
> SELECT ST_MinY(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- 2.2

ST_MinZ

Overloads:

  • ST_MinZ(geometry) returns the minimum Z coordinate of geometry

Example:

SELECT ST_MinZ(ST_PointZ(1.5, 2.5, 2)) FROM src LIMIT 1;  -- 2
SELECT ST_MinZ(ST_LineString('linestring z (1.5 2.5 2, 3.0 2.2 1)')) FROM src LIMIT 1;  -- 1

ST_NumGeometries

Overloads:

  • ST_NumGeometries(ST_GeometryCollection) return the number of geometries in the geometry collection

Example:

SELECT ST_NumGeometries(ST_GeomFromText('multipoint ((10 40), (40 30), (20 20), (30 10))')) FROM src LIMIT 1;  -- 4
SELECT ST_NumGeometries(ST_GeomFromText('multilinestring ((2 4, 10 10), (20 20, 7 8))')) FROM src LIMIT 1;  -- 2

ST_NumInteriorRing

Overloads:

  • ST_NumInteriorRing(ST_Polygon) return the number of interior rings in the polygon

Example:

SELECT ST_NumInteriorRing(ST_Polygon(1,1, 1,4, 4,1)) FROM src LIMIT 1;  -- 0
SELECT ST_NumInteriorRing(ST_Polygon('polygon ((0 0, 8 0, 0 8, 0 0), (1 1, 1 5, 5 1, 1 1))')) FROM src LIMIT 1;  -- 1

ST_NumPoints

Overloads:

  • ST_NumPoints(geometry) return the number of points in the geometry

Example:

> SELECT ST_NumPoints(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  -- 1
> SELECT ST_NumPoints(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- 2
> SELECT ST_NumPoints(ST_GeomFromText('polygon ((0 0, 10 0, 0 10, 0 0))')) FROM src LIMIT 1;  -- 4

ST_PointN

Notes on Hive usage of ST_PointN

Overloads:

  • ST_PointN(ST_Geometry, n) returns the point that is the nth vertex in an ST_Linestring or ST_MultiPoint (1-based index)

Example:

SELECT ST_PointN(ST_LineString(1.5,2.5, 3.0,2.2), 2) FROM src LIMIT 1;  -- POINT(3.0 2.2)

ST_IsRing

Overloads:

  • ST_IsRing(ST_LineString) return true if the linestring is closed & simple

Example:

SELECT ST_IsRing(ST_LineString(0.,0., 3.,4., 0.,4., 0.,0.)) FROM src LIMIT 1;  -- true
SELECT ST_IsRing(ST_LineString(0.,0., 1.,1., 1.,2., 2.,1., 1.,1., 0.,0.)) FROM src LIMIT 1;  -- false
SELECT ST_IsRing(ST_LineString(0.,0., 3.,4.)) FROM src LIMIT 1;  -- false

ST_SRID

Overloads:

  • ST_SRID(ST_Geometry) get the Spatial Reference ID of the geometry

Example:

SELECT ST_SRID(ST_Point(1.5, 2.5)) FROM src LIMIT 1  -- returns SRID 0

ST_StartPoint

Overloads:

  • ST_StartPoint(geometry) returns the first point of an ST_Linestring

Example:

> SELECT ST_StartPoint(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- POINT(1.5 2.5)

ST_EndPoint

Overloads:

  • ST_EndPoint(geometry) returns the last point of an ST_Linestring

Example:

> SELECT ST_EndPoint(ST_LineString(1.5,2.5, 3.0,2.2)) FROM src LIMIT 1;  -- POINT(3.0 2.0)

ST_X

Overloads:

  • ST_X(point) returns the X coordinate of point

Example:

SELECT ST_X(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  --  1.5

ST_Y

Overloads:

  • ST_Y(point) returns the Y coordinate of point

Example:

SELECT ST_Y(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  --  2.5

ST_Z

Overloads:

  • ST_Z(point) returns the Z coordinate of point

Example:

SELECT ST_Z(ST_Point(1.5, 2.5)) FROM src LIMIT 1;  --  1.5
Add a custom footer

JSON Utilities

Hive Spatial

Other

Toolkit Libraries

Clone this wiki locally