PostGIS spatial functions

Uninstall PostGIS

To remove the extension:

DROP EXTENSION postgis;

If the database still contains objects that depend on the geometry or geography type — for example, a table created as described in Create and query a geometry table — the statement fails with cannot drop extension postgis because other objects depend on it. Drop the dependent objects first:

1. Find the objects and columns that still use a spatial type:

   SELECT n.nspname AS schema_name, c.relname AS object_name, a.attname AS column_name, t.typname
   FROM pg_attribute a
     JOIN pg_class c ON c.oid = a.attrelid
     JOIN pg_type t ON t.oid = a.atttypid
     JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE t.typname IN ('geometry', 'geography')
     AND c.relkind IN ('r', 'p', 'm', 'f')
     AND a.attnum > 0
     AND NOT a.attisdropped
     AND n.nspname NOT LIKE 'pg_%';

2. Drop each table returned by the query, or drop only its spatial columns:

   DROP TABLE <TABLE_NAME>;

   Note

   If the table recycle bin is enabled — which is the default in Hologres V3.1 and later — DROP TABLE moves the table to the recycle bin, so you must also complete the next step. To bypass the recycle bin and skip the next step, run DROP TABLE <TABLE_NAME> FORCE; instead. For more information, see Table recycle bin.

3. Empty the recycle bin. Tables in the recycle bin still depend on the spatial types, so the types remain in use until the recycle bin is emptied:

   CALL hologres.hg_purge_all_tables();

   Important

   hg_purge_all_tables() permanently deletes every table in the recycle bin of the current database. This action cannot be undone. Before you run it, confirm that the recycle bin holds no table that you still need. This command must be run by a superuser of the current instance.

4. Uninstall the extension:

   DROP EXTENSION postgis;

Resolve the spatial_ref_sys conflict

If CREATE EXTENSION postgis fails with the error relation spatial_ref_sys already exists, the database contains a leftover spatial_ref_sys system table from a previous installation. A normal DROP EXTENSION postgis leaves nothing behind, so this error typically appears after a migration from another database or when a table of the same name was created manually. Resolve the conflict as follows:

1. Remove the partially installed extension:

   DROP EXTENSION IF EXISTS postgis;

2. Remove the leftover system table. Before you run this statement, confirm that the table is a leftover from a previous installation rather than a business table:

   DROP TABLE IF EXISTS spatial_ref_sys CASCADE;

3. Reinstall the PostGIS extension:

   CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public;

4. Verify the installation:

   SELECT public.postgis_full_version();

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. The SRID of the geometries that you insert or use in query predicates must match the SRID of the column. Otherwise, the statement fails with an error such as Geometry SRID (0) does not match column SRID (4326) or Operation on mixed SRID geometries. 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)

SELECT st_astext(geom)
FROM holo_gis_1
WHERE geom && ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40))
  AND ST_Intersects(ST_GeomFromText('POLYGON((116 39,117 39,117 40,116 40,116 39))'), geom);

Polygon intersection check

To find points that fall inside an arbitrary polygon or lie on its boundary, use ST_Intersects with the polygon. Unlike the bounding box built by ST_MakeBox2D, the polygon can have any shape, and ST_Intersects counts boundary contact as a match.

Without SRID:

SELECT st_astext(geom)
FROM holo_gis_1
WHERE ST_Intersects(
  ST_GeomFromText('POLYGON((116 39,117 39,117 40,116 40,116 39))'),
  geom
);

With SRID:

SELECT st_astext(geom)
FROM holo_gis_2
WHERE ST_Intersects(
  ST_SetSRID(ST_GeomFromText('POLYGON((116 39,117 39,117 40,116 40,116 39))'), 4326),
  geom
);

Both queries return:

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

Geometry constructors

All functions in this group run on HQE.

Geometry accessors

All functions in this group run on HQE, except ST_IsPolygonCCW, which runs on PQE. They are not complements: when the exterior ring and the interior rings have the same orientation, both return false. Do not negate ST_IsPolygonCW to test for counterclockwise orientation. When you filter a large table by ring orientation, also note that these two functions run on different engines.

Geometry editors

All functions in this group run on HQE.

Geometry validation

Spatial reference system functions

All functions in this group run on HQE.

Geometry input

Geometry output

All functions in this group run on HQE.

Spatial relationship predicates

The engine varies by function in this group. See the Required engine column.

Measurement functions

Overlay function

Geometry processing functions

Bounding box functions

All functions in this group run on PQE.

Linear referencing function