On this page

Constructor

Show all

Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ HetuEngine SQL Functions and Operators/ Geospatial Function

Geospatial Function

Updated on 2024-12-13 GMT+08:00

View PDF

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)

Parent topic: HetuEngine SQL Functions and Operators

Previous topic: URL Function

Next topic: HyperLogLog Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot