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.

  1. 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.
  2. 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));
  3. 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.