Hologres:PostGIS for geographic information analysis

Before you use PostGIS in a database, you must execute the following statement as a superuser to install the PostGIS extension for the database. An extension is installed at the database level. For each database, you need to install an extension only once. If you create another database, you must execute the following statement again:

-- Create the PostGIS extension.
CREATE EXTENSION if not exists postgis;

After the PostGIS extension is installed, you can execute the following statement to view the version of PostGIS. In this example, PostGIS 3.0.0 is used in Hologres.

SELECT postgis_full_version();

To drop the PostGIS extension, execute the following statement:

DROP EXTENSION postgis;

PostGIS provides spatial functions, each of which can convert a value from one data type to another. The following tables describe the spatial functions. The function syntax contains the following parameters:

• geom: a value of the geometry data type or an expression whose calculation result is of the geometry data type.

• precision: a value of the INTEGER data type. In a coordinate system, the precision of values of the geometry data type is in the range of 1 to 20. If no precision is specified, the default value 15 is used.

• index: a value of the INTEGER data type, which specifies an index.

• srid: a value of the INTEGER data type, which specifies an SRID.

For more information about spatial functions, see PostGIS official documentation.

• Geometry constructors

  Function	Syntax	Return value type	Description	Required engine
  ST_LineFromMultiPoint	ST_LineFromMultiPoint(geom)	GEOMETRY	This function creates a linestring from an input multipoint geometry. The order of the points is preserved. The SRID of the returned geometry is the same as that of the input geometry.	HQE in Hologres V1.3
  ST_MakeEnvelope	ST_MakeEnvelope(xmin, ymin, xmax, ymax)ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid)	Geometry of the POINT, LINESTRING, or POLYGON subtype	This function creates a geometry. If the input coordinates specify a point, the returned geometry is a point. If the input coordinates specify a line, the returned geometry is a linestring. Otherwise, the returned geometry is a polygon, in which the input coordinates specify the lower-left and upper-right corners of the polygon. If an SRID is provided, the SRID of the returned geometry is set to that provided.	HQE in Hologres V1.3
  ST_MakeLine	ST_MakeLine(geom1, geom2)	Geometry of the LINESTRING subtype	This function creates a linestring from the input geometries.	HQE in Hologres V1.3
  ST_MakePoint	ST_MakePoint(x, y)	Geometry of the POINT subtype	This function creates a point whose coordinate values are the input values.	HQE in Hologres V1.3
  ST_Point	ST_Point(x, y)	GEOMETRY of the POINT subtype	This function creates a point from the input coordinate values.	HQE in Hologres V1.3
  ST_Polygon	ST_Polygon(linestring, srid)	Geometry of the POLYGON subtype	This function creates a polygon whose exterior ring is the input linestring. If an SRID is provided, the SRID of the polygon is set to that provided.	HQE in Hologres V1.3

• Geometry accessors

  Function	Syntax	Return value type	Description	Required engine
  GeometryType	GeometryType(geom)	VARCHAR	This function returns the subtype of an input geometry as a string.	HQE in Hologres V1.3
  ST_Boundary	ST_Boundary(geom)	GEOMETRY	This function returns the boundary of an input geometry. If the input geometry is empty, which contains no points, the input geometry is returned as is. If the input geometry is a point or a non-empty multipoint, an empty geometry collection is returned. If the input geometry is a linestring or a multilinestring, a multipoint that contains all points on the boundary is returned. The multipoint may be empty. If the input geometry is a polygon that has no interior ring, a closed linestring that represents its boundary is returned. If the input geometry is a polygon that has interior rings or is a multipolygon, a multilinestring is returned. The multilinestring contains all the boundaries of all rings in the areal geometry as closed linestrings.	HQE in Hologres V1.3
  ST_Dimension	ST_Dimension(geom)	INTEGER	This function returns the intrinsic dimension of an input geometry. The intrinsic dimension is the dimension value of the subtype that is defined in the geometry.	HQE in Hologres V1.3
  ST_Envelope	ST_Envelope(geom)	GEOMETRY	This function returns the minimum bounding box of the input geometry. If the input geometry is empty, the returned geometry is a copy of the input geometry. If the minimum bounding box of the input geometry degenerates to a point, the returned geometry is a point. If the minimum bounding box of the input geometry is one-dimensional, a two-point linestring is returned. If none of the preceding conditions is true, this function returns a clockwise-oriented polygon whose vertices are the corners of the minimum bounding box. The SRID of the returned geometry is the same as that of the input geometry.	HQE in Hologres V1.3
  ST_ExteriorRing	ST_ExteriorRing(geom)	Geometry of the LINESTRING subtype	This function returns a closed linestring that represents the exterior ring of an input polygon.	HQE in Hologres V1.3
  ST_GeometryN	ST_GeometryN(geom, index)	GEOMETRY	This function returns a geometry to which the input index of an input geometry points. If the input is a point, line string, or polygon, and the index is 1, the input geometry is returned. If the index is not 1, null is returned. If the input is a multipoint, multilinestring, multipolygon, or geometry collection, the point, linestring, polygon, or geometry to which the input index points is returned. The input index starts from 1. The SRID of the returned geometry is the same as that of the input geometry.	HQE in Hologres V1.3
  ST_GeometryType	ST_GeometryType(geom)	VARCHAR	This function returns the subtype of an input geometry as a string.	HQE in Hologres V1.3
  ST_InteriorRingN	ST_InteriorRingN(geom, index)	Geometry of the LINESTRING subtype	This function returns a closed linestring that represents the interior ring of an input polygon at the index position.	HQE in Hologres V1.3
  ST_IsClosed	ST_IsClosed(geom)	BOOLEAN	This function returns true if an input geometry is closed. The following rules define a closed geometry: The input geometry is a point or a multipoint. The input geometry is a linestring, and the start and end points of the linestring coincide. The input geometry is a non-empty multilinestring, and all its linestrings are closed. The input geometry is a non-empty polygon, all rings of the polygon are non-empty, and the start and end points of all its rings coincide. The input geometry is a non-empty multipolygon, and all its polygons are closed. The input geometry is a non-empty geometry collection, and all its elements are closed.	HQE in Hologres V1.3
  ST_IsCollection	ST_IsCollection(geom)	BOOLEAN	This function returns true if an input geometry is one of the following subtypes: GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, or MULTIPOLYGON.	HQE in Hologres V1.3
  ST_IsEmpty	ST_IsEmpty(geom)	BOOLEAN	This function returns true if an input geometry is empty. If a geometry contains no points, the geometry is empty.	HQE in Hologres V1.3
  ST_IspolyGonCW	ST_IsPolygonCCW(geom)	BOOLEAN	This function returns true if an input polygon or multipolygon is anticlockwise. This function returns true if an input geometry is a point, linestring, multipoint, or multilinestring. This function returns true if all the geometries in an input geometry collection are anticlockwise.	HQE in Hologres V1.3
  ST_IsSimple	ST_IsSimple(geom)	BOOLEAN	This function returns true if an input geometry is simple.	HQE in Hologres V1.3
  ST_NPoints	ST_NPoints(geom)	INTEGER	This function returns the number of points in an input geometry.	HQE in Hologres V1.3
  ST_NRings	ST_NRings(geom)	INTEGER	This function returns the number of rings in an input geometry.	HQE in Hologres V1.3
  ST_NumGeometries	ST_NumGeometries(geom)	INTEGER	This function returns the number of geometries in an input geometry collection.	HQE in Hologres V1.3
  ST_NumInteriorRings	ST_NumInteriorRings(geom)	INTEGER	This function returns the number of rings in an input polygon.	HQE in Hologres V1.3
  ST_NumPoints	ST_NumPoints(geom)	INTEGER	This function returns the number of points in an input geometry.	HQE in Hologres V1.3
  ST_PointN	ST_PointN(geom, index)	Geometry of the POINT subtype	This function returns a point in a linestring as specified by an index value. Negative index values are counted backward from the end of the linestring so that -1 indicates the last point.	HQE in Hologres V1.3
  ST_Points	ST_Points(geom)	Geometry of the MULTIPOINT subtype	This function returns a multipoint geometry that contains all non-empty points in an input geometry. This function does not remove duplicate points in the input geometry, including the start and end points of a ring geometry.	HQE in Hologres V1.3
  ST_StartPoint	ST_StartPoint(geom)	GEOMETRY	This function returns the first point of an input linestring. The SRID of the returned geometry is the same as that of the input geometry.	HQE in Hologres V1.3
  ST_X	ST_X(point)	DOUBLE	This function returns the X coordinate of an input point.	HQE in Hologres V1.3
  ST_Y	ST_Y(point)	DOUBLE	This function returns the Y coordinate of an input point.	HQE in Hologres V1.3

• Geometry editors

  Function	Syntax	Return value type	Description	Required engine
  ST_AddPoint	ST_AddPoint(geom1, geom2)	GEOMETRY	This function returns a linestring that is the same as an input geometry with a point added.	HQE in Hologres V1.3
  ST_Multi	ST_Multi(geom)	Geometry of the MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION subtype	This function converts a geometry to the corresponding multitype. If the input geometry is a multitype or a geometry collection, a copy of the input geometry is returned. If the input geometry is a point, linestring, or polygon, a multipoint, multilinestring, or multipolygon that contains the input geometry is returned.	HQE in Hologres V1.3
  ST_RemovePoint	ST_RemovePoint(geom, index)	GEOMETRY	This function returns a linestring that has a point of an input geometry at an index position removed. The index is zero-based. The SRID of the returned geometry is the same as that of the input geometry.	HQE in Hologres V1.3
  ST_Reverse	ST_Reverse(geom)	GEOMETRY	This function reverses the order of the vertices for a linear or areal geometry. For a point or multipoint, a copy of the input geometry is returned. For a geometry collection, this function reverses the order of the vertices for each of the geometries in the collection.	HQE in Hologres V1.3
  ST_setPoint	ST_SetPoint(geom1, index, geom2)	GEOMETRY	This function returns a linestring that is the same as an input linestring with a point specified by an index value replaced with new coordinates. The new coordinates are the coordinates of an input point.	HQE in Hologres V1.3

• Geometry validation function

  Function	Syntax	Return value type	Description	Required engine
  ST_IsValid	ST_IsValid(geom)	BOOLEAN	This function returns true if an input geometry is valid. Note This function is supported by PQE.	PQE

• Spatial reference system functions

  Function	Syntax	Return value type	Description	Required engine
  ST_SetSRID	ST_SetSRID(geom, srid)	GEOMETRY	This function returns a geometry that is the same as an input geometry, except that the returned geometry is updated by using the SRID of the input geometry.	HQE in Hologres V1.3
  ST_SRID	ST_SRID(geom)	INTEGER	This function returns the SRID of an input geometry.	HQE in Hologres V1.3

• Geometry input function

  Function	Syntax	Return value type	Description	Required engine
  ST_GeomFromText	ST_GeomFromText(wkt_string) ST_GeomFromText(wkt_string, srid)	Geometry	This function constructs a geometry object from a well-known text (WKT) representation of an input geometry. Note PQE	PQE

• Geometry output functions

  Function	Syntax	Return value type	Description	Required engine
  ST_AsBinary	ST_AsBinary(geom)	BYTEA	This function returns the hexadecimal well-known binary (WKB) representation of an input geometry by using ASCII characters 0 to 9 and A to F.	HQE in Hologres V1.3
  ST_AsEWKB	ST_AsEWKB(geom)	BYTEA	This function returns the extended well-known binary (EWKB) representation of an input geometry by using ASCII characters 0 to 9 and A to F.	HQE in Hologres V1.3
  ST_AsEWKT	ST_AsEWKT(geom)	VARCHAR	This function returns the extended well-know text (EWKT) representation of an input geometry.	HQE in Hologres V1.3
  ST_AsGeoJSON	ST_AsGeoJSON(geom)ST_AsGeoJSON(geom, precision)	VARCHAR	This function returns the GeoJSON representation of an input geometry. For more information about GeoJSON, visit the GeoJSON page on Wikipedia.	HQE in Hologres V1.3
  ST_AsText	ST_AsText(geom)ST_AsText(geom, precision)	VARCHAR	This function returns the WKT representation of an input geometry.	HQE in Hologres V1.3

• Spatial relationship functions

  Function	Syntax	Return value type	Description	Required engine
  ST_Contains	ST_Contains(geom1, geom2)	BOOLEAN	This function returns true if the first input geometry contains the second input geometry. Geometry A contains Geometry B if each point in B is a point in A and their interiors have a non-empty intersection. ST_Contains(A, B) is equivalent to ST_Within(B, A). Note PQE	PQE
  ST_ContainsProperly	ST_ContainsProperly(geom1, geom2)	BOOLEAN	This function returns true if two input geometries are non-empty and all points of the second geometry lie in the interior of the first geometry. Note PQE	PQE
  ST_CoveredBy	ST_CoveredBy(geom1, geom2)	BOOLEAN	This function returns true if the first input geometry is covered by the second input geometry. Geometry A is covered by Geometry B if both of them are non-empty and each point in A is a point in B. ST_CoveredBy(A, B) is equivalent to ST_Covers(B, A). Note PQE	PQE
  ST_Covers	ST_Covers(geom1, geom2)	BOOLEAN	This function returns true if the first input geometry covers the second input geometry. Geometry A covers Geometry B if both of them are non-empty and each point in B is a point in A. ST_Covers(A, B) is equivalent to ST_CoveredBy(B, A). Note PQE	PQE
  ST_Crosses	ST_Crosses(geom1, geom2)	BOOLEAN	This function returns true if two input geometries are intersected.	N/A
  ST_Disjoint	ST_Disjoint(geom1, geom2)	BOOLEAN	This function returns true if two input geometries have no points in common.	N/A
  ST_DWithin	ST_DWithin(geom1, geom2, threshold)	BOOLEAN	This function returns true if the Euclidean distance between two input geometries does not exceed a threshold. Note PQE	PQE
  ST_DWithin_S2	ST_DWithin_S2(x1, y1, x2, y2, threshold)	BOOLEAN	This function returns true if the spherical distance between two geographic locations is less than or equal to the value specified by threshold (unit: meters). The input parameters are the longitude and latitude of Location 1, the longitude and latitude of Location 2, and the spherical distance threshold in sequence. Note This function is supported in Hologres V2.0.8. The input values cannot be constants.	HQE
  ST_Equals	ST_Equals(geom1, geom2)	BOOLEAN	This function returns true if two input geometries are geometrically equal. Geometries are geometrically equal if they have equal point sets and their interiors have a non-empty intersection.	PQE
  ST_Intersects	ST_Intersects(geom1, geom2)	BOOLEAN	This function returns true if two input geometries have at least one point in common. Note PQE	PQE
  ST_Touches	ST_Touches(geom1, geom2)	BOOLEAN	This function returns true if two input geometries touch. Two geometries touch if they are non-empty, intersect, and have no interior points in common.	PQE
  ST_Within	ST_Within(geom1, geom2)	BOOLEAN	This function returns true if the first input geometry is within the second input geometry. Geometry A is within Geometry B if each point in A is a point in B and their interiors have a non-empty intersection. ST_Within(A, B) is equivalent to ST_Contains(B, A). Note PQE	PQE

• Measurement functions

  Function	Syntax	Return value type	Description	Required engine
  ST_Angle	ST_Angle(geom1, geom2, geom3) ST_Angle(geom1, geom2, geom3, geom4)	DOUBLE	This function returns the angle in radians between points that are measured clockwise. The return value is in radians and in the range of [0, 2π). Examples: If three points are specified, the returned angle is measured by rotating from P1 to P3 around P2 clockwise. If four points are specified, the returned angle is formed by the directed lines P1-P2 and P3-P4 clockwise. If two parallel lines in which P1 equals P2 or P3 equals P4 are specified, null is returned.	PQE
  ST_Area	ST_Area(geom)	DOUBLE	This function returns the Cartesian area of an input geometry. The area units are the same as the units in which the coordinates of the input geometry are expressed. For points, linestrings, multipoints, and multilinestrings, 0 is returned. For geometry collections, the sum of the areas of all geometries in a collection is returned.	HQE in Hologres V1.3
  ST_Azimuth	ST_Azimuth(point1, point2)	DOUBLE	This function returns the north-based Cartesian azimuth that is defined by two input points.	HQE in Hologres V1.3
  ST_Distance	ST_Distance(geom1, geom2)	DOUBLE	This function returns the spherical central angle between two values of input geometries.	HQE in Hologres V1.3
  ST_Distance_Sphere_S2	ST_Distance_Sphere_S2(x1, y1, x2, y2)	DOUBLE	This function returns the spherical distance between two geographic locations. Unit: meters. The input parameters are the longitude and latitude of Location 1 and the longitude and latitude of Location 2 in sequence. Note This function is supported in Hologres V2.0.8. The input values cannot be constants.	HQE
  ST_Length	ST_Length(geom)	DOUBLE	This function returns the Cartesian length of an input linear geometry. The length units are the same as the units in which the coordinates of the input geometry are expressed. This function returns 0 for points, multipoints, and areal geometries. If the input is a geometry collection, this function returns the total length of the geometries in the collection.	HQE in Hologres V1.3
  ST_Perimeter	ST_Perimeter(geom)	DOUBLE	This function returns the Cartesian perimeter of an input areal geometry, which indicates the length of its boundary. The perimeter units are the same as the units in which the coordinates of the input geometry are expressed. This function returns 0 for points, multipoints, and linear geometries. If the input is a geometry collection, this function returns the sum of the perimeters of the geometries in the collection.	HQE in Hologres V1.3

• Overlay functions

  Function	Syntax	Return value type	Description	Required engine
  ST_Intersection	ST_Intersection(geom1, geom2)	GEOMETRY	This function returns the intersection between two input geometries.	HQE in Hologres V1.3

• Geometry processing functions

  Function	Syntax	Return value type	Description	Required engine
  ST_Buffer	ST_Buffer(geography,float8)	GEOMETRY	This function returns the distance from a geometry to a buffer. Note PQE	PQE
  ST_Convexhull	ST_ConvexHull(geom)	GEOMETRY	This function returns a geometry that represents the convex shell of non-empty points in an input geometry.	HQE in Hologres V1.3
  ST_Simplify	ST_Simplify(geom, tolerance)	GEOMETRY	This function returns a simplified copy of an input geometry by using the Ramer-Douglas-Peucker algorithm with a given tolerance. The topology of the input geometry may not be preserved. For more information about the Ramer-Douglas-Peucker algorithm, visit the Ramer-Douglas-Peucker page on Wikipedia.	HQE in Hologres V1.3

• Bounding box functions

  Function	Syntax	Return value type	Description	Required engine
  ST_XMax	ST_XMax(geom)	DOUBLE	This function returns the maximum X coordinate of an input geometry. Note PQE	PQE
  ST_XMin	ST_XMin(geom)	DOUBLE	This function returns the minimum X coordinate of an input geometry. Note PQE	PQE
  ST_YMax	ST_YMax(geom)	DOUBLE	This function returns the maximum Y coordinate of an input geometry. Note PQE	PQE
  ST_YMin	ST_YMin(geom)	DOUBLE	This function returns the minimum Y coordinate of an input geometry. Note PQE	PQE

• Linear referencing function

  Function	Syntax	Return value type	Description	Required engine
  ST_LineInterpolatePoint	ST_LineInterpolatePoint(geom, fraction)	Geometry of the POINT subtype	This function returns an interpolation point, which is a point along a line at a fractional distance from the start of the line.	HQE in Hologres V1.3