AnalyticDB:Use PostGIS

Create a table with a geometry column

Use the geometry type without constraints to accept any geometry type — Point, MultiPoint, LineString, MultiLineString, Polygon, or MultiPolygon:

CREATE TABLE testg ( id int, geom geometry )
DISTRIBUTED BY (id);

To enforce a specific geometry type and SRID, declare both in the column definition:

CREATE TABLE test ( id int, geom geometry(point, 4326) )
DISTRIBUTED BY (id);

Insert spatial data

Use ST_GeomFromText to convert Well-Known Text (WKT) to a geometry value.

Without an SRID:

INSERT INTO testg VALUES (1, ST_GeomFromText('POINT(116 39)'));

With an SRID:

INSERT INTO test VALUES (1, ST_GeomFromText('POINT(116 39)', 4326));

Using the JDBC API (Java):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class SpatialInsert {
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            // Replace placeholders with your actual connection details
            conn = DriverManager.getConnection(
                "jdbc:postgresql://<host>:3432/<database>",
                "<user>",
                "<password>"
            );
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            String sql = "INSERT INTO test VALUES (1001, ST_GeomFromText('POINT(116 39)', 4326))";
            stmt.executeUpdate(sql);

            stmt.close();
            conn.commit();
            conn.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Inserted successfully.");
    }
}

Replace the following placeholders with your actual values:

Manage spatial indexes

PostGIS uses Generalized Search Tree (GiST) indexes for spatial data. Unlike B-tree indexes, which work on one-dimensional ordered data, GiST indexes are designed for multidimensional data — points, lines, and polygons — and significantly improve performance for spatial range queries and joins. For most spatial workloads, create a GiST index on every geometry column you query.

Create a spatial index:

CREATE INDEX idx_test_geom ON test USING GIST(geom);

idx_test_geom is the index name, test is the table name, and geom is the geometry column.

List indexes on a table:

SELECT * FROM pg_stat_user_indexes
WHERE relname = 'test';

Check index size:

SELECT pg_indexes_size('idx_test_geom');

Rebuild an index:

REINDEX INDEX idx_test_geom;

Drop an index:

DROP INDEX idx_test_geom;

Run spatial queries

All ST_* function names are case-insensitive. For the full function reference, see Introduction to PostGIS.

Rectangular range query

ST_MakeBox2D creates a bounding box (rectangular envelope) from two corner points. ST_Contains returns rows whose geometry falls inside the box.

-- Without SRID
SELECT ST_AsText(geom) FROM testg
WHERE ST_Contains(ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40)), geom);

-- With SRID
SELECT ST_AsText(geom) FROM test
WHERE ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39), ST_Point(117, 40)), 4326), geom);

Expected output (example):

   st_astext
---------------
 POINT(116 39)
(1 row)

Buffer range query

ST_DWithin returns rows within a specified distance from a reference geometry. For details, see ST_DWithin.

-- Without SRID
SELECT ST_AsText(geom) FROM testg
WHERE ST_DWithin(ST_GeomFromText('POINT(116 39)'), geom, 0.01);

-- With SRID
SELECT ST_AsText(geom) FROM test
WHERE ST_DWithin(ST_GeomFromText('POINT(116 39)', 4326), geom, 0.01);

Expected output (example):

   st_astext
---------------
 POINT(116 39)
(1 row)

Intersection query

ST_Intersects returns rows whose geometry shares any point with the reference geometry, including boundary intersections.

-- Without SRID
SELECT ST_AsText(geom) FROM testg
WHERE ST_Intersects(
    ST_GeomFromText('POLYGON((116 39, 116.1 39, 116.1 39.1, 116 39.1, 116 39))'),
    geom
);

-- With SRID
SELECT ST_AsText(geom) FROM test
WHERE ST_Intersects(
    ST_GeomFromText('POLYGON((116 39, 116.1 39, 116.1 39.1, 116 39.1, 116 39))', 4326),
    geom
);

Expected output (example):

   st_astext
---------------
 POINT(116 39)
(1 row)

Geofencing

A passenger transportation operator uses GPS terminals on vehicles to detect when a vehicle enters a monitored area — for driving condition warnings on accident-prone roads, icy roads, or restricted zones. Because these monitored areas change infrequently and contain a small volume of data, store them in a replicated table:

CREATE TABLE ky_region (
  rid     serial,
  name    varchar(256),
  geom    geometry)
DISTRIBUTED REPLICATED;

After inserting Polygon or MultiPolygon area data and running ANALYZE TABLE to collect statistics, create a GiST index on the geom column. Depending on the alert type, use a different spatial operator:

-- Alert when the vehicle is fully inside an area
SELECT rid, name FROM ky_region
WHERE ST_Contains(geom, ST_GeomFromText('POINT(116 39)'));

-- Alert when the vehicle touches an area boundary
SELECT rid, name FROM ky_region
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(116 39)'));

Both queries take a GPS coordinate and return any areas that contain or intersect it. No result means the vehicle is outside all monitored areas; multiple results mean it has entered overlapping areas (verify that overlapping areas are valid polygons).

Smart transportation

A smart transportation system stores GPS driving traces in a day-partitioned table and queries which traces entered a specific geographic area:

CREATE TABLE vhc_trace_d (
 stat_date        text,
 trace_id         text,
 vhc_id           text,
 rid_wkt          geometry)
DISTRIBUTED BY (vhc_id) PARTITION BY LIST(stat_date)
(
 PARTITION p20191008 VALUES('20191008'),
 PARTITION p20191009 VALUES('20191009'),
 ......
);

Each day's partition gets a GiST spatial index after data import. A typical area-intersection query across hundreds of millions of rows returns in under 80 milliseconds:

SELECT trace_id FROM vhc_trace_d
WHERE ST_Intersects(
  ST_GeomFromText('POLYGON((
    118.732461 29.207363, 118.732366 29.207198, 118.732511 29.205951,
    118.732296 29.205644, 118.73226  29.205469, 118.732350 29.20470,
    118.731708 29.203399, 118.731701 29.202401, 118.754689 29.213488,
    118.750827 29.21316,  118.750272 29.213337, 118.749677 29.213257,
    118.748699 29.213388, 118.747715 29.213206, 118.746580 29.213831,
    118.74639  29.213872, 118.744989 29.213858, 118.743442 29.213795,
    118.74174  29.213002, 118.735633 29.208167, 118.734422 29.207699,
    118.733045 29.207450, 118.732803 29.207342, 118.732461 29.207363))'),
  rid_wkt
);

Customer traffic analysis

An internet service provider analyzes which users visit specific shops by joining a user location table with a shop boundary table. Both tables use Geohash or Z-order encoding to pre-cluster data by location, so spatial joins can run without a dedicated spatial index:

-- User location table (workday and weekend geometry per user)
CREATE TABLE user_label (
  ghash7          int,
  uid             int,
  workday_geo     geometry,
  weekend_geo     geometry)
DISTRIBUTED BY (ghash7);

-- Shop boundary table
CREATE TABLE user_shop (
  ghash7          int,
  sid             int,
  shop_poly       geometry)
DISTRIBUTED BY (ghash7);

Count distinct users who visited specific shops on either workdays or weekends:

SELECT COUNT(1)
FROM (
  SELECT DISTINCT T0.uid
  FROM user_label T0
  JOIN user_shop T1 ON T1.ghash7 = T0.ghash7
  WHERE T1.sid IN (1, 2, 3)
    AND (ST_Intersects(T0.workday_geo, T1.shop_poly)
      OR ST_Intersects(T0.weekend_geo, T1.shop_poly))
) c;