PostGIS (Beta) - Developer Guide| Alibaba Cloud Documentation Center

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

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

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.
```
CREATE TABLE holo_gis_1 ( id int, geom geometry, PRIMARY KEY (id)) ;
```
  After the preceding statement is executed, a table that contains the GEOMETRY data type is created, but the subtype is not specified.
- Create a table in which the subtype and spatial reference system identifier (SRID) are specified.
```
CREATE TABLE holo_gis_2 ( id int, geom geometry(point, 4326), PRIMARY KEY (id)) ;
```
  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.
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));
```
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);
```
- 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);
```

Spatial functions

PostGIS provides some spatial functions, each of which can convert the data type of a value. The following table describes the spatial functions. The following content describes only some of the parameters in the function syntax:

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.

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


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: 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.
ST_Area	ST_Area(geom)	DOUBLE	This function returns the Cartesian area of a polygonal geometry. 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.
ST_AsBinary	ST_AsBinary(geom)	BYTEA	This function returns a hexadecimal well-known 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 well-known 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 well-known 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 north-based 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. 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.
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 non-empty 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 non-empty 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 non-empty 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 non-empty 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 non-empty 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. If the input geometry is empty, the returned geometry is also empty. 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.
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 non-empty 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. The input is a point, linestring, or polygon: If the index is 1, the input geometry is returned as is. If the index is not 1, NULL is returned. The input is a multipoint, multilinestring, multipolygon, or geometry collection: A point, linestring, polygon, or geometry collection is returned as pointed to by an input index that starts from 1. The SRID of the returned geometry is the same as that 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: No SRID is specified. A geometry whose SRID is set to 0 is returned. An SRID is used as the second parameter. A geometry that takes the SRID as part of the metadata is returned.
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. If the input polygon is anticlockwise, TRUE is returned. If the input geometry is a point, linestring, multipoint, or multilinestring, TRUE is returned. For geometry collections, TRUE is returned if all the geometries in a collection are anticlockwise.
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 components are closed.
ST_IsCollection	ST_IsCollection(geom)	BOOLEAN	This function returns TRUE if an input geometry is one of the following subtypes: GEOMETRYCOLLECTION MULTIPOINT MULTILINESTRING MULTIPOLYGON
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 self-intersection or self-tangent 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. This function returns 0 for points, multipoints, and areal geometries. If the input is a geometry collection, this function returns the sum of the lengths of the geometries in the collection.
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.	E-MapReduce 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. 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. If the input coordinates specify the lower-left and upper-right corners of a box, the returned geometry is a polygon. The input values must be in the spatial reference system specified by an SRID. If no SRID is specified, the SRID is 0.
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. If the input geometry is a multitype or a geometry collection, the input geometry is returned as is. If the input geometry is a point, linestring, or polygon, a multipoint, multilinestring, or multipolygon that contains the input geometry is returned.
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. 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.
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 non-empty 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 zero-based. 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. For points or multipoints, the input geometry is returned as is. For geometry collections, this function reverses the order of the vertices for each of the geometries in the collection.
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 Ramer-Douglas-Peucker 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 Ramer-Douglas-Peucker 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 non-empty, 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 non-empty 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.