Help Center> MapReduce Service> Component Operation Guide (LTS)> Using HetuEngine> HetuEngine SQL Syntax> SQL Functions and Operators> Geospatial Function
Updated on 2022-11-18 GMT+08:00
View PDF

Geospatial Function

The HetuEngine Geospatial function starting with ST_ supports SQL and MM specifications and complies with the Open GIS specifications of Open Geospatial Consortium (OGC). Therefore, many HetuEngine Geospatial features require, or more accurately put, assume that the geometry to be manipulated is both simple and efficient. For example, it makes no sense to calculate the area of a polygon that defines holes outside the polygon, or to construct a polygon from non-simple boundary lines.

The HetuEngine geospatial function supports both known text (WKT) and known binary (WKB) forms of spatial objects:

  • POINT (0 0)
  • LINESTRING (0 0, 1 1, 1 2)
  • POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))
  • MULTIPOINT (0 0, 1 2)
  • MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))
  • MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))
  • GEOMETRYCOLLECTION (POINT(2 3), LINESTRING (2 3, 3 4))

Well-known Text (WKT) is a text markup language developed by the Open Geospatial Consortium (OGC (Open GIS Consortium)). It is used to represent vector geometric objects, spatial reference systems, and conversion between spatial reference systems.

Well-known Binary (WKB) is a binary representation of WKT. It solves the problem of redundant WKT expressions and facilitates the transmission and storage of the same information in the database.

GeoJSON is a feature information output format in JSON format. It can be easily processed by script languages such as JavaScript. Geographic databases such as OpenLayers use the GeoJSON format. In addition, more simplified extended formats such as TopoJSON are used.

Use the ST_GeometryFromText() and ST_GeomFromBinary() functions to create geometric objects from WKT or WKB.

The SphericalGeography type provides local support for spatial elements represented on geographic coordinates (sometimes referred to as geodetic coordinates or lat / lon or lon / lat). Geographical coordinates are spherical coordinates expressed in degrees. Geometric types are based on the plane. The shortest path between two points on the plane is a straight line. This means that Cartesian mathematics and linear vectors can be used to calculate geometric shapes (area, distance, length, intersection, etc.).

The SphericalGeography type is based on a sphere. The shortest path between two points on the sphere is the large arc. This means that more complex mathematical methods must be used to calculate the terrain (areas, distances, lengths, intersections, etc.) on the sphere. More accurate measurements that take into account the actual shape of the sphere are not supported.

Values returned by the measurement functions ST_Distance () and ST_Length () are in meters. ST_Area() returns a value in square meters.

Use the to_spherical_geography () function to convert a geometric object to a geographic object.

For example, ST_Distance(ST_Point(-71.0882, 42.3607), ST_Point(-74.1197, 40.6976)) return 3.4577 in units of input values on the Euclidean plane. ST_Distance (to_spherical_geography (ST_Point(-71.0882, 42.3607)), to_spherical_geography (ST_Point(-74.1197, 40.6976))) returns the value 312822.179 in meters.

Constructor

  • ST_AsBinary(Geometry)→ varbinary

    Description: Returns the binary representation of a geometry.

    select ST_AsBinary(ST_GeometryFromText('POINT(12 13)'));
                      _col0                      
---------------------------------------------------------------
01 01 00 00 00 00 00 00 00 00 00 28 40 00 00 00 00 00 00 2a 40                          
(1 row)
  • ST_AsText(Geometry) → varchar

    Description: Returns the WKT representation of a geometry. For an empty geometric figure, ST_AsText(ST_LineFromText('LINESTRING EMPTY')) generates 'MULTI LINE STRING EMPTY', and ST_AsText(ST_Polygon('POLYGON EMPTY')) generates 'MULTIPOLYGON EMPTY'.

    SELECT st_astext(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));-- LINESTRING (0 0, 1 1, 1 2)
  • ST_GeometryFromText(varchar)→ Geometry

    Description: Returns a geometric object represented by WKT.

    select ST_GeometryFromText('POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))');
--POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))
  • ST_GeomFromBinary(varbinary)→ Geometry

    Description: Returns a geometric type represented by WKB.

    select ST_geomFromBinary(ST_AsBinary(ST_GeometryFromText('POINT(12 13)')));-- POINT (12 13)
  • ST_LineFromText(varchar)→ LineString

    Description: Returns the line string object represented by WKT.

    select st_lineFromText('LINESTRING (0 0, 1 1, 1 2)');--  LINESTRING (0 0, 1 1, 1 2)
  • ST_Point(double, double)

    Description: Returns a geometric point object with a given coordinate value.

    select st_point(12.1,34.1); 
POINT (12.1 34.1)
  • ST_Polygon(varchar)

    Description: Returns the polygon represented by the WKT string.

    select ST_Polygon('POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))');
POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))

Operations

  • ST_Boundary(Geometry) → Geometry

    Description: Returns the boundary of a closed graph.

    select ST_boundary(ST_Polygon('POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))'));
MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))
  • ST_Buffer(Geometry, distance) → Geometry

    Description: Returns the geometry representing all points whose distance to the specified geometry is less than or equal to the specified distance.

    select ST_Buffer(ST_POINT(0,0),4);
POLYGON ((4 0, 3.9914356929544113 0.2616125169205717, 3.965779445495239 0.5221047688802056, 3.923141121612919 0.7803612880645122, 3.8637033051562706 1.035276180410082, 3.7877205179804205 1.2857578612126452, 3.695518130045145 1.5307337294603...
  • ST_Difference(Geometry, Geometry) → Geometry

    Description: Returns the geometry value representing the point set difference for a given geometry.

    select ST_Difference(ST_POINT(0,0),ST_POINT(2,3));-- POINT (0 0)
  • ST_EnvelopeAsPts(Geometry) -> array(Geometry)

    Description: Returns an array of two points: the lower left and upper right corners of a bounding rectangle polygon of a geometry. If the input geometry is empty, null is returned.

    select ST_EnvelopeAsPts(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));--  [POINT (0 0), POINT (1 2)]
  • ST_Intersection(Geometry, Geometry) → Geometry

    Description: Returns the geometric value representing the intersection of two geometric point sets.

     select ST_Intersection(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'), ST_LineFromText('LINESTRING (0 0, 1 1, 1 3)'));
-- LINESTRING (0 0, 1 1, 1 2)
  • ST_SymDifference(Geometry, Geometry) → Geometry

    Description: Returns a geometric value representing the symmetric difference between the point sets of two geometries.

    select ST_SymDifference(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'), ST_LineFromText('LINESTRING (0 0, 1 1, 1 3)'));--  LINESTRING (1 2, 1 3)
  • ST_Union(Geometry, Geometry) → Geometry

    Description: Returns a geometry representing the union of the points of the input geometry.

     select ST_Union(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'), ST_LineFromText('LINESTRING (0 0, 1 1, 1 3)')); --  LINESTRING (0 0, 1 1, 1 2, 1 3)