PostGIS is a spatial extension of PostgreSQL and provides spatial features including objects, indexes, functions, and operators. This topic describes how to use PostGIS in Hologres.
Limits
PostGIS is in public preview. In this case, when you use this feature in Hologres, its stability cannot be completely ensured. Spatial indexes are not supported. If you have questions when you use PostGIS, submit a ticket for feedback.
Install PostGIS
create extension if not exists postgis;  Load PostGIS.
After PostGIS is installed, you can execute the following SQL statement to view the
current version of PostGIS: select postgis_full_version();
Create and query a table that contains a spatial data type
PostGIS supports two spatial data types in Hologres: GEOMETRY and GEOGRAPHY.
The GEOMETRY data type is more frequently used. This section shows you how to create a table that contains the GEOMETRY data type and query spatial data. For more information about the parameters and instructions of the GEOGRAPHY data type, see PostGIS Geography Type.
 Create a table that contains the GEOMETRY data type.Note When you create a table that contains the GEOMETRY data type, you can specify the subtype of a geometry object to be created. Allowable subtypes include POINT, MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON, and MULTIPOLYGON.
 Create a table in which the subtype is not specified.
After the preceding statement is executed, a table that contains the GEOMETRY data type is created, but the subtype is not specified.CREATE TABLE holo_gis_1 ( id int, geom geometry, PRIMARY KEY (id)) ;
 Create a table in which the subtype and spatial reference system identifier (SRID)
are specified.
In the preceding statement, the subtype of the GEOMETRY data type is set to POINT, and the SRID is 4326. If no SRID is specified, the SRID is 0. For more information about the SRID, see PostGIS official documentation.CREATE TABLE holo_gis_2 ( id int, geom geometry(point, 4326), PRIMARY KEY (id)) ;
 Create a table in which the subtype is not specified.
 Insert data into the table.
You can insert data into the table by using one of the following methods. For more information about how to use spatial functions, see Spatial functions. For more information about the SRID, see PostGIS official documentation.
 Do not specify the SRID.
insert into holo_gis_1 values (1, ST_GeomFromText('point(116 39)'));
 Specify the SRID.
insert into holo_gis_2 values (1, ST_GeomFromText('point(116 39)', 4326));
 Do not specify the SRID.
 Query data.
After you create the table and insert the data, you can query spatial data in two typical scenarios: perform rectangular range queries and check the intersections of polygons. The following examples show you how to query spatial data. For more information about how to use spatial functions, see Spatial functions.
 Perform rectangular range queries
 Do not specify the SRID.
select st_astext(geom) from holo_gis_1 where ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);
 Specify the SRID.
select st_astext(geom) from holo_gis_2 where ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), 4326), geom);
 Do not specify the SRID.
 Check the intersections of polygons inside or on the boundary
 Do not specify the SRID.
select st_astext(geom) from holo_gis_1 where ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);
 Specify the SRID.
select st_astext(geom) from holo_gis_2 where ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), 4326), geom);
 Do not specify the SRID.
 Perform rectangular range queries
Spatial functions
 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 is in the range of 1 to 20. If no precision is specified, the default value is 15.
 index: a value of the INTEGER data type, which specifies an index.
 srid: a value of the INTEGER data type, which specifies an SRID.
Function  Syntax  Data type of return value  Description 

GeometryType  GeometryType(geom)  VARCHAR  This function returns the subtype of an input geometry as a string. For example, if the value of the geom parameter is of the POINT subtype, the returned string is POINT. 
ST_AddPoint  ST_AddPoint(geom1, geom2)
In this syntax, the value of the geom1 parameter must be of the LINESTRING subtype, and the value of the geom2 parameter must be of the POINT subtype. 
GEOMETRY  This function adds a coordinate point to a linestring. 
ST_Angle  ST_Angle(geom1, geom2, geom3)
ST_Angle(geom1, geom2, geom3, geom4) The values of all geom parameters must be of the POINT subtype. 
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:

ST_Area  ST_Area(geom)  DOUBLE  This function returns the Cartesian area of a polygonal geometry.

ST_AsBinary  ST_AsBinary(geom)  BYTEA  This function returns a hexadecimal wellknown binary (WKB) representation of an input geometry by using ASCII hexadecimal characters 0 to 9 and A to F, excluding SRID data. 
ST_AsEWKB  ST_AsEWKB(geom)  BYTEA  This function returns an extended WKB (EWKB) representation of an input geometry by using ASCII hexadecimal characters 0 to 9 and A to F, including SRID data. 
ST_AsEWKT  ST_AsEWKT(geom)  VARCHAR  This function returns an extended wellknown text (EWKT) representation of an input geometry, including SRID data. 
ST_AsGeoJSON  ST_AsGeoJSON(geom)
ST_AsGeoJSON(geom, precision) 
VARCHAR  This function returns a GeoJSON representation of an input geometry. 
ST_AsText  ST_AsText(geom)
ST_AsText(geom, precision) 
VARCHAR  This function returns a wellknown text (WKT) representation of an input geometry, excluding SRID data. 
ST_Azimuth  ST_Azimuth(point1, point2)
The values of the point1 and point2 parameters must be of the GEOMETRY data type. The SRIDs of the two input points must match each other. 
DOUBLE  This function returns the northbased Cartesian azimuth that is defined by two input points. The azimuth is referenced from north and is positive clockwise. For example, if an azimuth is measured clockwise from true north (0 degrees), due east is azimuth π/2 (90 degrees), and due south is azimuth π (180 degrees). If the two points coincide, NULL is returned. 
ST_Bilands  ST_Boundary(geom)  GEOMETRY  This function returns the boundary of an input geometry.

ST_Buffer  ST_Buffer(geography,float8)  GEOMETRY  This function specifies the distance from a geometry to a buffer. A geometry is returned, which covers all points whose distance from an input geography is less than or equal to a given value. 
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 nonempty intersection. ST_Contains(A, B) is equivalent to ST_Within(B, A). 
ST_ContainsFeorth  ST_ContainsProperly(geom1, geom2)
The value of the geom parameter cannot be of the GEOMETRYCOLLECTION subtype. 
BOOLEAN  This function returns TRUE if two input geometries are nonempty and all points of the second geometry lie in the interior of the first geometry. 
ST_Convexhull  ST_ConvexHull(geom)  GEOMETRY  This function returns a geometry that represents the convex shell of nonempty points in an input geometry. 
ST_CoveredBy  ST_CoveredBy(geom1, geom2)  BOOLEAN  This function returns TRUE if the first input geometry is covered by the second input
geometry. To be specific, Geometry A is covered by Geometry B if both of them are
nonempty and each point in A is a point in B.
ST_CoveredBy(A, B) is equivalent to ST_Covers(B, A). 
ST_Covers  ST_Covers(geom1, geom2)  BOOLEAN  This function returns TRUE if the first input geometry covers the second input geometry.
To be specific, Geometry A covers Geometry B if both of them are nonempty and each
point in B is a point in A.
ST_Covers(A, B) is equivalent to ST_CoveredBy(B, A). 
ST_Crosses  ST_Crosses(geom1, geom2)  BOOLEAN  This function returns TRUE if two input geometries are partially intersected. 
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. 
ST_Disjoint  ST_Disjoint(geom1, geom2)  BOOLEAN  This function returns TRUE if two input geometries have no points in common. 
ST_Distance  ST_Distance(geom1, geom2)  DOUBLE  This function returns the minimum Euclidean distance between two input geometries. 
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. 
ST_Envelope  ST_Envelope(geom)  GEOMETRY  This function returns the minimum bounding box of an input geometry.

ST_Equals  ST_Equals(geom1, geom2)  BOOLEAN  This function returns TRUE if two input geometries are geometrically equal. To be specific, geometries are geometrically equal if they have equal point sets and their interiors have a nonempty intersection. 
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 geometry. If the input geometry is not a polygon, NULL is returned. 
ST_GeometryN  ST_GeometryN(geom, index)  GEOMETRY  This function returns a geometry that is pointed to by the input index of the input
geometry.

ST_GeometryType  ST_GeometryType(geom)  VARCHAR  This function returns the subtype of an input geometry as a string. For example, if the value of the geom parameter is of the POINT subtype, ST_Point is returned. 
ST_GeomFromText  ST_GeomFromText(wkt_string)
ST_GeomFromText(wkt_string, srid) The value of the wkt_string parameter is a WKT representation of a geometry. The value is of the VARCHAR data type. 
GEOMETRY  This function constructs a geometry object from a WKT representation of an input geometry.
This function has two forms:

ST_teriorRingn  ST_InteriorRingN(geom, index)  GEOMETRY of the LINESTRING subtype  This function returns a closed linestring that corresponds to the interior ring of an input polygon at the index position. 
ST_Intersects  ST_Intersects(geom1, geom2)  BOOLEAN  This function returns TRUE if two input geometries have at least one point in common. 
ST_Intersection  ST_Intersection(geom1, geom2)  GEOMETRY  This function returns the intersection between two input geometries. 
ST_IspolyGonCW  ST_IsPolygonCW(geom)  BOOLEAN  This function tests whether an input geometry has an exterior ring in the clockwise
direction and an interior ring in the anticlockwise direction.

ST_IsClosed  ST_IsClosed(geom)  BOOLEAN  This function returns TRUE if an input geometry is closed. The following rules define
a closed geometry:

ST_IsCollection  ST_IsCollection(geom)  BOOLEAN  This function returns TRUE if an input geometry is one of the following subtypes:

ST_IsEmpty  ST_IsEmpty(geom)  BOOLEAN  This function returns TRUE if an input geometry is empty. 
ST_IsSimple  ST_IsSimple(geom)  BOOLEAN  This function returns TRUE if an input geometry has no abnormal geometric points, such as a selfintersection or selftangent line. 
ST_IsValid  ST_IsValid(geom)  BOOLEAN  This function returns TRUE if an input geometry is valid. 
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.

ST_LineFromMultiPoint  ST_LineFromMultiPoint(geom)  GEOMETRY  This function returns 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. 
ST_LineInterpolatePoint  ST_LineInterpolatePoint(geom, fraction)
The value of the geom parameter must be of the LINESTRING subtype. The value of the fraction parameter is of the DOUBLE data type and in the range between 0 and 1. 
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. 
ST_MakeEnvelope  ST_MakeEnvelope(xmin, ymin, xmax, ymax)
ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid) The values of both x and y are of the DOUBLE data type. 
EMapReduce cluster POINT, LINESTRING, or POLYGON of the GEOMETRY subtype  This function creates a rectangular polygon by using the minimum and maximum values
of x and y.

ST_MakeLine  ST_MakeLine(geom1, geom2)  GEOMETRY of the LINESTRING subtype  This function creates a linestring from the input geometries. 
ST_MakePoint  ST_MakePoint(x, y)  GEOMETRY of the POINT subtype  This function returns a point geometry whose coordinate values are the input values. 
ST_Multi  ST_Multi(geom)  GEOMETRY of the MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION subtype  This function converts a geometry to the corresponding multitype.

ST_NPoints  ST_NPoints(geom)  INTEGER  This function returns the number of points in an input geometry. 
ST_NRings  ST_NRings(geom)  INTEGER  This function returns the number of rings in an input geometry. 
ST_NumGeometries  ST_NumGeometries(geom)  INTEGER  This function returns the number of geometries in an input geometry collection. 
ST_NumInteriorRings  ST_NumInteriorRings(geom)  INTEGER  This function returns the number of rings in an input polygon geometry. 
ST_NumPoints  ST_NumPoints(geom)  INTEGER  This function returns the number of points in a linestring or circular string. 
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.

ST_Point  ST_Point(x, y)  GEOMETRY of the POINT subtype  This function returns a point geometry from the input coordinate values. 
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. 
ST_Points  ST_Points(geom)  GEOMETRY of the MULTIPOINT subtype  This function returns a multipoint geometry that contains all nonempty points in an input geometry. This function does not remove duplicate points in the input, including the start and end points of ring geometries. 
ST_Polygon  ST_Polygon(linestring, srid)
The value of the linestring parameter is a value of the GEOMETRY data type or an expression whose calculation result is of the GEOMETRY data type. The subtype must be the LINESTRING subtype that represents a linestring. 
GEOMETRY of the POLYGON subtype  This function returns a polygon geometry whose exterior ring is an input linestring with the value that is specified for the SRID. 
ST_RemovePoint  ST_RemovePoint(geom, index)  GEOMETRY  This function returns a linestring geometry that has the point of the input geometry at an index position removed. The index is zerobased. The SRID of the returned linestring is the same as that of the input geometry. 
ST_Reverse  ST_Reverse(geom)  GEOMETRY  This function reverses the order of the vertices for an input geometry.

ST_setPoint  ST_SetPoint(geom1, index, geom2)  GEOMETRY  This function replaces a point in an input linestring that is specified by an index with a given point. 
ST_SetSRID  ST_SetSRID(geom, srid)  GEOMETRY  This function returns a geometry that is the same as an input geometry and whose SRID is updated. 
ST_Simplify  ST_Simplify(geom, tolerance)
The value of the tolerance parameter is of the DOUBLE data type, which specifies the tolerance level of the RamerDouglasPeucker algorithm. If the tolerance is a negative number, the value of the tolerance parameter is 0. 
GEOMETRY  This function returns a simplified copy of an input geometry by using the RamerDouglasPeucker algorithm with the given tolerance. The topology of the input geometry may not be preserved. 
ST_SRID  ST_SRID(geom)  INTEGER  This function returns the SRID of an input geometry. 
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. 
ST_Touches  ST_Touches(geom1, geom2)  BOOLEAN  This function returns TRUE if two input geometries touch. To be specific, two geometries touch if they are nonempty, intersect, and have no interior points in common. 
ST_Within  ST_Within(geom1, geom2)  BOOLEAN  This function returns TRUE if the first input geometry is within the second input geometry. For example, Geometry A is within Geometry B if each point in A is a point in B and their interiors have a nonempty intersection. ST_Within(A, B) is equivalent to ST_Contains(B, A). 
ST_X  ST_X(point)
The value of the point parameter is of the GEOMETRY data type. 
DOUBLE  This function returns the X coordinate of an input point. 
ST_XMax  ST_XMax(geom)  DOUBLE  This function returns the maximum X coordinate of an input geometry. 
ST_XMin  ST_XMin(geom)  DOUBLE  This function returns the minimum X coordinate of an input geometry. 
ST_Y  ST_Y(point)  DOUBLE  This function returns the Y coordinate of an input point. 
ST_YMax  ST_YMax(geom)  DOUBLE  This function returns the maximum Y coordinate of an input geometry. 
ST_YMin  ST_YMin(geom)  DOUBLE  This function returns the minimum Y coordinate of an input geometry. 
Best practices for using spatial functions
Alibaba Cloud provides best practices for using spatial functions. For more information, see Use spatial functions to query data.