Run PostGIS Spatial Queries & Geometry Analysis in Hologres - Hologres

PostGIS extends Hologres with spatial data types, functions, and operators. Use it to store, query, and analyze geographic data — from bounding box filters and proximity searches to polygon intersection checks and distance calculations.

Hologres supports PostGIS 3.0.0. In Hologres V1.3 and later, most PostGIS functions run on the Hologres Query Engine (HQE) developed by Alibaba Cloud, which delivers better query performance. Earlier versions fall back to the PostgreSQL Query Engine (PQE).

Limits

Fields of the geometry or geography data type cannot be used as primary keys. Use an integer surrogate key instead.
Spatial indexes are not supported. For large datasets, apply bounding box pre-filters (such as ST_MakeBox2D + ST_Covers) before more expensive spatial predicates to reduce the scan scope.
In Hologres instances earlier than V1.3, all PostGIS functions run on PQE. Performance may be lower than HQE.

Install the PostGIS extension

Run the following statement as a superuser to install PostGIS in a database. Installation is per-database — repeat this step for each database that needs spatial support.

CREATE EXTENSION IF NOT EXISTS postgis;

PostGIS cannot be installed in the pg_catalog schema.

To verify the installation, run:

SELECT postgis_full_version();

A successful install returns a version string such as POSTGIS="3.0.0 ...".

To remove the extension:

DROP EXTENSION postgis;

Important

Do not use DROP EXTENSION postgis CASCADE. The CASCADE option drops all dependent objects — including PostGIS data, roaring bitmap data, Proxima data, binary log data, BSI data, and metadata such as tables, views, and server data — and cannot be undone.

Create and query a geometry table

PostGIS supports two spatial data types in Hologres: geometry (planar/Cartesian coordinates) and geography (spherical longitude/latitude coordinates). For details on the geography type, see the PostGIS geography documentation.

The geometry type is more commonly used. The following steps show how to create a geometry table and run spatial queries.

1. Create a geometry table

When creating a table, you can specify a geometry subtype. Supported subtypes: Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon.

Without a subtype:

CREATE TABLE holo_gis_1 (
  id   INT,
  geom geometry,
  PRIMARY KEY (id)
);

With a subtype and spatial reference system identifier (SRID):

CREATE TABLE holo_gis_2 (
  id   INT,
  geom geometry(point, 4326),
  PRIMARY KEY (id)
);

In this example, the subtype is Point and the SRID is 4326 (WGS 84). If no SRID is specified, the default is 0. For more information about SRIDs, see the PostGIS documentation.

2. Insert spatial data

-- Without SRID
INSERT INTO holo_gis_1 VALUES (1, ST_GeomFromText('point(116 39)'));

-- With SRID 4326
INSERT INTO holo_gis_2 VALUES (1, ST_GeomFromText('point(116 39)', 4326));

For more information about spatial functions, see Spatial functions.

3. Query spatial data

After inserting data, you can run the following types of queries.

Rectangular range query

Returns all points within a bounding box.

Without SRID:

SELECT st_astext(geom)
FROM holo_gis_1
WHERE ST_Covers(
  ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40)),
  geom
);

With SRID:

SELECT st_astext(geom)
FROM holo_gis_2
WHERE ST_Covers(
  ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40)), 4326),
  geom
);

Both queries return:

st_astext
-------------
POINT(116 39)

Polygon intersection check

To find points inside or on the boundary of a polygon, use ST_Covers. The syntax is the same as the rectangular range query above — ST_MakeBox2D produces a rectangular polygon that includes its boundary in the coverage test.

Without SRID:

SELECT st_astext(geom)
FROM holo_gis_1
WHERE ST_Covers(
  ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40)),
  geom
);

With SRID:

SELECT st_astext(geom)
FROM holo_gis_2
WHERE ST_Covers(
  ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40)), 4326),
  geom
);

Both queries return:

st_astext
-------------
POINT(116 39)

Spatial functions

PostGIS provides spatial functions to convert and analyze geometry values. The function syntax uses the following parameters:

geom: a geometry value or an expression that evaluates to geometry
precision: INTEGER; coordinate precision in the range 1–20; default 15
index: INTEGER; a 1-based index unless otherwise noted
srid: INTEGER; a spatial reference system identifier

The Required engine column indicates which query engine executes each function:

Engine	When it applies	Performance
HQE (Hologres Query Engine)	Hologres V1.3 and later	Higher — optimized for analytical workloads
PQE (PostgreSQL Query Engine)	All versions; required for certain functions	Lower — compatibility mode

For the complete PostGIS function specification, see the PostGIS reference documentation.