- Notifications
You must be signed in to change notification settings - Fork161
UDF Accessors
Sarah Ambrose edited this pageMar 26, 2015 ·7 revisions
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 srcLIMIT1;-- 9.0
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 srcLIMIT1;-- POINT(3 3)>SELECT ST_Centroid(ST_GeomFromText('polygon ((0 0, 0 8, 8 0, 0 0))'))FROM srcLIMIT1;-- POINT(4 4)
Overloads:
ST_CoordDim(geometry)return count of coordinate components
Example:
>SELECT ST_CoordDim(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 2>SELECT ST_CoordDim(ST_PointZ(1.5,2.5,3)FROM srcLIMIT1;-- 3>SELECT ST_CoordDim(ST_Point(1.5,2.5,3.,4.))FROM srcLIMIT1;-- 4
Overloads:
ST_Dimension(geometry)return spatial dimension of geometry
Example:
>SELECT ST_Dimension(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 0>SELECT ST_Dimension(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- 1>SELECT ST_Dimension(ST_Polygon(2,0,2,3,3,0))FROM srcLIMIT1;-- 2
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 srcLIMIT1;-- 5.0
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 srcLIMIT1;-- 55kmSELECT ST_GeodesicLengthWGS84(ST_GeomFromText('MultiLineString((0.0 80.0, 0.3 80.4))',4326))FROM srcLIMIT1;-- 45km
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 srcLIMIT1;-- ST_Point(20 20)SELECT ST_GeometryN(ST_GeomFromText('multilinestring ((2 4, 10 10), (20 20, 7 8))'),2)FROM srcLIMIT1;-- ST_Linestring(20 20, 7 8)
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 srcLIMIT1;-- false>SELECT ST_Is3D(ST_LineString(0.,0.,3.,4.,0.,4.,0.,0.))FROM srcLIMIT1;-- false>SELECT ST_Is3D(ST_Point(3.,4.))FROM srcLIMIT1;-- false>SELECT ST_Is3D(ST_PointZ(3.,4.,2))FROM srcLIMIT1;-- true
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 srcLIMIT1;-- trueSELECT ST_IsClosed(ST_LineString(0.,0.,3.,4.))FROM srcLIMIT1;-- false
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 srcLIMIT1;-- false>SELECT ST_IsEmpty(ST_GeomFromText('point empty'))FROM srcLIMIT1;-- true
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 srcLIMIT1;-- false>SELECT ST_IsMeasured(ST_LineString(0.,0.,3.,4.,0.,4.,0.,0.))FROM srcLIMIT1;-- false>SELECT ST_IsMeasured(ST_Point(3.,4.))FROM srcLIMIT1;-- false>SELECT ST_IsMeasured(ST_PointM(3.,4.,2))FROM srcLIMIT1;-- true
Overloads:
ST_IsSimple(geometry)return true if geometry is simple
Example:
>SELECT ST_IsSimple(ST_Point(1.5,2.5))FROM srcLIMIT1;-- true>SELECT ST_IsSimple(ST_LineString(0.,0.,1.,1.,0.,1.,1.,0.))FROM srcLIMIT1;-- false
Overloads:
ST_Length(line)returns the length of line
Example:
SELECT ST_Length(ST_Line(0.0,0.0,3.0,4.0))FROM srcLIMIT1;-- 5.0
Overloads:
ST_M(geometry)return true if the geometry object is three-dimensional
Example:
>SELECT ST_M(ST_PointM(3.,4.,2))FROM srcLIMIT1;-- 2
Overloads:
ST_MaxM(geometry)returns the maximum M coordinate of geometry
Example:
SELECT ST_MaxM(ST_PointM(1.5,2.5,2))FROM srcLIMIT1;-- 2SELECT ST_MaxM(ST_LineString('linestring m (1.5 2.5 2, 3.0 2.2 1)'))FROM srcLIMIT1;-- 1
Overloads:
ST_MaxX(geometry)returns the maximum X coordinate of geometry
Example:
>SELECT ST_MaxX(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 1.5>SELECT ST_MaxX(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- 3.0
Overloads:
ST_MaxY(geometry)returns the maximum Y coordinate of geometry
Example:
>SELECT ST_MaxY(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 2.5>SELECT ST_MaxY(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- 2.5
Overloads:
ST_MaxZ(geometry)returns the maximum Z coordinate of geometry
Example:
SELECT ST_MaxZ(ST_PointZ(1.5,2.5,2))FROM srcLIMIT1;-- 2SELECT ST_MaxZ(ST_LineString('linestring z (1.5 2.5 2, 3.0 2.2 1)'))FROM srcLIMIT1;-- 1
Overloads:
ST_MinM(geometry)returns the minimum M coordinate of geometry
Example:
SELECT ST_MinM(ST_PointM(1.5,2.5,2))FROM srcLIMIT1;-- 2SELECT ST_MinM(ST_LineString('linestring m (1.5 2.5 2, 3.0 2.2 1)'))FROM srcLIMIT1;-- 1
Overloads:
ST_MinX(geometry)returns the minimum X coordinate of geometry
Example:
>SELECT ST_MinX(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 1.5>SELECT ST_MinX(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- 3.0
Overloads:
ST_MinY(geometry)returns the minimum Y coordinate of geometry
Example:
>SELECT ST_MinY(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 2.5>SELECT ST_MinY(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- 2.2
Overloads:
ST_MinZ(geometry)returns the minimum Z coordinate of geometry
Example:
SELECT ST_MinZ(ST_PointZ(1.5,2.5,2))FROM srcLIMIT1;-- 2SELECT ST_MinZ(ST_LineString('linestring z (1.5 2.5 2, 3.0 2.2 1)'))FROM srcLIMIT1;-- 1
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 srcLIMIT1;-- 4SELECT ST_NumGeometries(ST_GeomFromText('multilinestring ((2 4, 10 10), (20 20, 7 8))'))FROM srcLIMIT1;-- 2
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 srcLIMIT1;-- 0SELECT ST_NumInteriorRing(ST_Polygon('polygon ((0 0, 8 0, 0 8, 0 0), (1 1, 1 5, 5 1, 1 1))'))FROM srcLIMIT1;-- 1
Overloads:
ST_NumPoints(geometry)return the number of points in the geometry
Example:
>SELECT ST_NumPoints(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 1>SELECT ST_NumPoints(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- 2>SELECT ST_NumPoints(ST_GeomFromText('polygon ((0 0, 10 0, 0 10, 0 0))'))FROM srcLIMIT1;-- 4
Notes on Hive usage ofST_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 srcLIMIT1;-- POINT(3.0 2.2)
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 srcLIMIT1;-- trueSELECT ST_IsRing(ST_LineString(0.,0.,1.,1.,1.,2.,2.,1.,1.,1.,0.,0.))FROM srcLIMIT1;-- falseSELECT ST_IsRing(ST_LineString(0.,0.,3.,4.))FROM srcLIMIT1;-- false
Overloads:
ST_SRID(ST_Geometry)get the Spatial Reference ID of the geometry
Example:
SELECT ST_SRID(ST_Point(1.5,2.5))FROM srcLIMIT1-- returns SRID 0
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 srcLIMIT1;-- POINT(1.5 2.5)
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 srcLIMIT1;-- POINT(3.0 2.0)
Overloads:
ST_X(point)returns the X coordinate of point
Example:
SELECT ST_X(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 1.5
Overloads:
ST_Y(point)returns the Y coordinate of point
Example:
SELECT ST_Y(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 2.5
Overloads:
ST_Z(point)returns the Z coordinate of point
Example:
SELECT ST_Z(ST_Point(1.5,2.5))FROM srcLIMIT1;-- 1.5