Skip to content

UDF Constructors

Jump to bottom Edit New page
Randall Whitman edited this page Dec 10, 2016 · 11 revisions

Constructors

ST_AsBinary

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)

ST_AsGeoJson

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]}

ST_AsJson

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}}

ST_AsShape

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)

ST_AsText

Notes on Hive usage of ST_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)

ST_GeomCollection

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 src LIMIT 1;  -- 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 supported

ST_GeomFromGeoJson

Overloads:

  • ST_GeomFromGeoJson(json) construct an ST_Geometry from GeoJSON

Example:

SELECT ST_GeomFromGeoJson('{"type":"Point", "coordinates":[1.2, 2.4]}') FROM src LIMIT 1;  -- constructs ST_Point
SELECT ST_GeomFromGeoJson('{"type":"LineString", "coordinates":[[1,2], [3,4]]}') FROM src LIMIT 1;  -- constructs ST_LineString

ST_GeomFromJSON

Overloads:

  • ST_GeomFromJSON(json) construct an ST_Geometry from Esri JSON

Example:

SELECT ST_GeomFromJSON('{"x":0.0,"y":0.0}') FROM src LIMIT 1;  -- constructs ST_Point

ST_GeomFromShape

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

ST_GeomFromText

Overloads:

  • ST_GeomFromText(wkt) construct an ST_Geometry from OGC well-known text

Example:

SELECT ST_GeomFromText('linestring (1 0, 2 3)') FROM src LIMIT 1;  -- constructs ST_Linestring
SELECT ST_GeomFromText('multipoint ((1 0), (2 3))') FROM src LIMIT 1;  -- constructs ST_MultiPoint

ST_GeomFromWKB

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 src LIMIT 1;  -- constructs ST_Linestring
SELECT ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('multipoint ((1 0), (2 3))'))) FROM src LIMIT 1;  -- constructs ST_MultiPoint

ST_GeometryType

Overloads:

  • ST_GeometryType(geometry) return type of geometry

Example:

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

ST_LineFromWKB

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 src LIMIT 1;  -- constructs ST_Linestring

ST_LineString

Overloads:

  • ST_LineString(x, y, [x, y]*) constructor for 2D line string
  • ST_LineString('linestring( ... )') constructor for 2D line string
  • ST_LineString(array(x+), array(y+)) constructor for 2D line string
  • ST_LineString(array(ST_Point(x,y)+))) constructor for 2D line string

Example:

SELECT ST_LineString(1, 1, 2, 2, 3, 3) from src LIMIT 1;
SELECT ST_LineString('linestring(1 1, 2 2, 3 3)') from src LIMIT 1;
SELECT ST_LineString(array(1,2,3), array (1,2,3)) from src LIMIT 1;
SELECT ST_LineString(array(ST_Point(1, 1), ST_Point(2,2), ST_Point(3,3))) from src LIMIT 1;

ST_MLineFromWKB

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 src LIMIT 1;  -- constructs ST_MultiLineString

ST_MPointFromWKB

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 src LIMIT 1;  -- constructs ST_MultiPoint

ST_MPolyFromWKB

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 src LIMIT 1;  -- constructs ST_MultiPolygon

ST_MultiLineString

Overloads:

  • ST_MultiLineString(array(x1, y1, x2, y2, ... ), array(x1, y1, x2, y2, ... ), ... ) constructor for 2D multi line string
  • ST_MultiLineString('multilinestring( ... )') constructor for 2D multi line string

Example:

SELECT ST_MultiLineString(array(1, 1, 2, 2), array(10, 10, 20, 20)) from src LIMIT 1;
SELECT ST_MultiLineString('multilinestring ((1 1, 2 2), (10 10, 20 20))', 0) from src LIMIT 1;

ST_MultiPoint

Overloads:

  • ST_MultiPoint(x1, y1, x2, y2, x3, y3) constructor for 2D multipoint
  • ST_MultiPoint('multipoint( ... )') constructor for 2D multipoint

Example:

SELECT ST_MultiPoint(1, 1, 2, 2, 3, 3) from src LIMIT 1; -- multipoint with 3 points
SELECT ST_MultiPoint('MULTIPOINT ((10 40), (40 30))') from src LIMIT 1; -- multipoint of 2 points

ST_MultiPolygon

Overloads:

  • ST_MultiPolygon(array(x1, y1, x2, y2, ... ), array(x1, y1, x2, y2, ... ), ... ) constructor for 2D multi polygon
  • ST_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 src LIMIT 1;
SELECT ST_MultiPolygon('multipolygon (((0 0, 0 1, 1 0, 0 0)), ((2 2, 2 3, 3 2, 2 2)))') from src LIMIT 1;

ST_Point

Overloads:

  • ST_Point(x, y) constructor for 2D point
  • ST_Point('point (x y)') constructor for 2D point

Example:

SELECT ST_Point(longitude, latitude) from src LIMIT 1;
SELECT ST_Point('point (0 0)') from src LIMIT 1;

ST_PointFromWKB

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 src LIMIT 1;  -- constructs ST_Point

ST_PointZ

Overloads:

  • ST_PointZ(x, y, z) constructor for 3D point

Example:

SELECT ST_PointZ(longitude, latitude, elevation) from src LIMIT 1;

ST_PolyFromWKB

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 src LIMIT 1;  -- constructs ST_Polygon

ST_Polygon

Overloads:

  • ST_Polygon(x, y, [x, y]*) constructor for 2D polygon
  • ST_Polygon('polygon( ... )') constructor for 2D polygon

Example:

SELECT ST_Polygon(1, 1, 1, 4, 4, 4, 4, 1) from src LIMIT 1;  -- creates a rectangle
SELECT ST_Polygon('polygon ((1 1, 4 1, 1 4))') from src LIMIT 1;  -- creates a triangle

ST_SetSRID

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 src LIMIT 1;
-- create a point and then set its SRID to 4326
Add a custom footer

JSON Utilities

Hive Spatial

Other

Toolkit Libraries

Clone this wiki locally