- Notifications
You must be signed in to change notification settings - Fork161
UDF Constructors
Randall Whitman edited this pageDec 10, 2016 ·11 revisions
Overloads:
ST_AsBinary(ST_Geometry)return Well-Known Binary (WKB) representation of geometry
Example:
SELECT ST_AsBinary(ST_Point(1,2))FROM onerow;-- WKB representation of POINT (1 2)
Overloads:
ST_AsGeoJson(geometry)return GeoJson representation of geometry
Example:
SELECT ST_AsGeoJson(ST_Point(1.0,2.0))from onerow;-- {"type":"Point", "coordinates":[1.0, 2.0]}
Overloads:
ST_AsJSON(ST_Geometry)return JSON representation of ST_Geometry
Example:
SELECT ST_AsJSON(ST_Point(1.0,2.0))from onerow;-- {"x":1.0,"y":2.0}SELECT ST_AsJSON(ST_SetSRID(ST_Point(1,1),4326))from onerow;-- {"x":1.0,"y":1.0,"spatialReference":{"wkid":4326}}
Overloads:
ST_AsShape(ST_Geometry)return Esri shape representation of geometry
Example:
SELECT ST_AsShape(ST_Point(1,2))FROM onerow;-- Esri shape representation of POINT (1 2)
Notes on Hive usage ofST_AsText
Overloads:
ST_AsText(ST_Geometry)return Well-Known Text (WKT) representation of ST_Geometry
Example:
SELECT ST_AsText(ST_Point(1,2))FROM onerow;-- POINT (1 2)
Overloads:
ST_GeomCollection(wkt)construct a multi-part ST_Geometry from OGC well-known text
Example:
>SELECT ST_GeomCollection('multipoint ((1 0), (2 3))')FROM srcLIMIT1;-- constructs ST_MultiPoint
OGC Compliance Notes :
ST_GeomCollection on Hive does not support collections - only multi-part geometries.ST_GeomCollection('POINT(1 1), LINESTRING(2 0,3 0)') -- not supportedOverloads:
ST_GeomFromGeoJson(json)construct an ST_Geometry from GeoJSON
Example:
SELECT ST_GeomFromGeoJson('{"type":"Point", "coordinates":[1.2, 2.4]}')FROM srcLIMIT1;-- constructs ST_PointSELECT ST_GeomFromGeoJson('{"type":"LineString", "coordinates":[[1,2], [3,4]]}')FROM srcLIMIT1;-- constructs ST_LineString
Overloads:
ST_GeomFromJSON(json)construct an ST_Geometry from Esri JSON
Example:
SELECT ST_GeomFromJSON('{"x":0.0,"y":0.0}')FROM srcLIMIT1;-- constructs ST_Point
Overloads:
ST_GeomFromShape(shape)construct an ST_Geometry from Esri shape representation of geometry
Example:
SELECT ST_GeomFromShape(ST_AsShape(ST_Point(1,2)));-- constructs ST_Point
Overloads:
ST_GeomFromText(wkt)construct an ST_Geometry from OGC well-known text
Example:
SELECT ST_GeomFromText('linestring (1 0, 2 3)')FROM srcLIMIT1;-- constructs ST_LinestringSELECT ST_GeomFromText('multipoint ((1 0), (2 3))')FROM srcLIMIT1;-- constructs ST_MultiPoint
Overloads:
ST_GeomFromWKB(wkb)construct an ST_Geometry from OGC well-known binary
Example:
SELECT ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('linestring (1 0, 2 3)')))FROM srcLIMIT1;-- constructs ST_LinestringSELECT ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('multipoint ((1 0), (2 3))')))FROM srcLIMIT1;-- constructs ST_MultiPoint
Overloads:
ST_GeometryType(geometry)return type of geometry
Example:
>SELECT ST_GeometryType(ST_Point(1.5,2.5))FROM srcLIMIT1;-- ST_Point>SELECT ST_GeometryType(ST_LineString(1.5,2.5,3.0,2.2))FROM srcLIMIT1;-- ST_LineString>SELECT ST_GeometryType(ST_Polygon(2,0,2,3,3,0))FROM srcLIMIT1;-- ST_Polygon
Overloads:
ST_LineFromWKB(wkb)construct an ST_LineString from OGC well-known binary
Example:
SELECT ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('linestring (1 0, 2 3)')))FROM srcLIMIT1;-- constructs ST_Linestring
Overloads:
ST_LineString(x, y, [x, y]*)constructor for 2D line stringST_LineString('linestring( ... )')constructor for 2D line stringST_LineString(array(x+), array(y+))constructor for 2D line stringST_LineString(array(ST_Point(x,y)+)))constructor for 2D line string
Example:
SELECT ST_LineString(1,1,2,2,3,3)from srcLIMIT1;SELECT ST_LineString('linestring(1 1, 2 2, 3 3)')from srcLIMIT1;SELECT ST_LineString(array(1,2,3), array (1,2,3))from srcLIMIT1;SELECT ST_LineString(array(ST_Point(1,1), ST_Point(2,2), ST_Point(3,3)))from srcLIMIT1;
Overloads:
ST_MLineFromWKB(wkb)construct an ST_MultiLineString from OGC well-known binary
Example:
SELECT ST_MLineFromWKB(ST_AsBinary(ST_GeomFromText('multilinestring ((1 0, 2 3), (5 7, 7 5))')))FROM srcLIMIT1;-- constructs ST_MultiLineString
Overloads:
ST_MPointFromWKB(wkb)construct an ST_MultiPoint from OGC well-known binary
Example:
SELECT ST_MPointFromWKB(ST_AsBinary(ST_GeomFromText('multipoint ((1 0), (2 3))')))FROM srcLIMIT1;-- constructs ST_MultiPoint
Overloads:
ST_MPolyFromWKB(wkb)construct an ST_MultiPolygon from OGC well-known binary
Example:
SELECT ST_MPolyFromWKB(ST_AsBinary(ST_GeomFromText('multipolygon (((0 0, 1 0, 0 1, 0 0)), ((2 2, 1 2, 2 1, 2 2)))')))FROM srcLIMIT1;-- constructs ST_MultiPolygon
Overloads:
ST_MultiLineString(array(x1, y1, x2, y2, ... ), array(x1, y1, x2, y2, ... ), ... )constructor for 2D multi line stringST_MultiLineString('multilinestring( ... )')constructor for 2D multi line string
Example:
SELECT ST_MultiLineString(array(1,1,2,2), array(10,10,20,20))from srcLIMIT1;SELECT ST_MultiLineString('multilinestring ((1 1, 2 2), (10 10, 20 20))',0)from srcLIMIT1;
Overloads:
ST_MultiPoint(x1, y1, x2, y2, x3, y3)constructor for 2D multipointST_MultiPoint('multipoint( ... )')constructor for 2D multipoint
Example:
SELECT ST_MultiPoint(1,1,2,2,3,3)from srcLIMIT1;-- multipoint with 3 pointsSELECT ST_MultiPoint('MULTIPOINT ((10 40), (40 30))')from srcLIMIT1;-- multipoint of 2 points
Overloads:
ST_MultiPolygon(array(x1, y1, x2, y2, ... ), array(x1, y1, x2, y2, ... ), ... )constructor for 2D multi polygonST_MultiPolygon('multipolygon ( ... )')constructor for 2D multi polygon
Example:
SELECT ST_MultiPolygon(array(1,1,1,2,2,2,2,1), array(3,3,3,4,4,4,4,3))from srcLIMIT1;SELECT ST_MultiPolygon('multipolygon (((0 0, 0 1, 1 0, 0 0)), ((2 2, 2 3, 3 2, 2 2)))')from srcLIMIT1;
Overloads:
ST_Point(x, y)constructor for 2D pointST_Point('point (x y)')constructor for 2D point
Example:
SELECT ST_Point(longitude, latitude)from srcLIMIT1;SELECT ST_Point('point (0 0)')from srcLIMIT1;
Overloads:
ST_PointFromWKB(wkb)construct an ST_Point from OGC well-known binary
Example:
SELECT ST_PointFromWKB(ST_AsBinary(ST_GeomFromText('point (1 0))')))FROM srcLIMIT1;-- constructs ST_Point
Overloads:
ST_PointZ(x, y, z)constructor for 3D point
Example:
SELECT ST_PointZ(longitude, latitude, elevation)from srcLIMIT1;
Overloads:
ST_PolyFromWKB(wkb)construct an ST_Polygon from OGC well-known binary
Example:
SELECT ST_PolyFromWKB(ST_AsBinary(ST_GeomFromText('polygon ((0 0, 10 0, 0 10, 0 0))')))FROM srcLIMIT1;-- constructs ST_Polygon
Overloads:
ST_Polygon(x, y, [x, y]*)constructor for 2D polygonST_Polygon('polygon( ... )')constructor for 2D polygon
Example:
SELECT ST_Polygon(1,1,1,4,4,4,4,1)from srcLIMIT1;-- creates a rectangleSELECT ST_Polygon('polygon ((1 1, 4 1, 1 4))')from srcLIMIT1;-- creates a triangle
Overloads:
ST_SetSRID(<ST_Geometry>, SRID)set the Spatial Reference ID of the geometry
Example:
>SELECT ST_SetSRID(ST_Point(1.5,2.5),4326))FROM srcLIMIT1;-- create a point and then set its SRID to 4326