All Products
Search
Document Center

AnalyticDB for PostgreSQL:Use PostGIS

Last Updated:Dec 15, 2023

PostGIS is an extension of PostgreSQL and provides features such as spatial objects, spatial indexes, spatial functions, and spatial operators. This topic describes how to use PostGIS to perform spatio-temporal analysis.

Note

PostGIS conforms to Open Geospatial Consortium (OGC) standards.

Common operations

  1. Connect to an AnalyticDB for PostgreSQL instance.

    For more information, see Client connection.

  2. Install the PostGIS extension for the first time.

    1. Execute the following statement to install the PostGIS extension:

      CREATE extension postgis;
    2. Execute the following statements to query the version of PostGIS that is installed:

      SELECT postgis_version();
      SELECT postgis_full_version();
    Important

    To install or upgrade extensions on instances that run V6.3.8.9 or later, Submit a ticket.

    For more information about how to view the minor version of an instance, see View the minor engine version.

  3. Create a table and insert spatial data into the table.

    Execute the following statement to create a table with a geometry field:

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

    The preceding statement indicates that the inserted spatial data is insensitive to geometry types such as Point, MultiPoint, Linestring, MultiLineString, Polygon, and MultiPolygon. Execute the following statement to create a table with the required geometry type and spatial reference identifier (SRID):

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

    The following statements show how to insert data into a table with or without an SRID:

    -- without srid
    INSERT INTO testg values (1, ST_GeomFromText('point(116 39)'));
    
    -- with srid
    INSERT INTO test values (1, ST_GeomFromText('point(116 39)', 4326));

    The following code shows how to use the Java Database Connectivity (JDBC) API to insert data:

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    
    public class <PGJDBC> {
        public static void main(String args[]) {
            Connection conn = null;
            Statement stmt = null;
            try{
                Class.forName("org.postgresql.Driver");
                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("insert successfully");
        }
    }
  4. Manage spatial indexes.

    • Create a spatial index.

      CREATE index idx_test_geom on test using gist(geom);

      idx_test_geom is a custom index name, in which test is the table name and geom is the geometry column name.

    • Query indexes in a table.

      SELECT * FROM pg_stat_user_indexes 
      WHERE relname='test';
    • Query the size of an index.

      SELECT pg_indexes_size('idx_test_geom');
    • Rebuild an index.

      reindex index idx_test_geom;
    • Delete an index.

      DROP index idx_test_geom;
  5. Use statements to execute typical spatial queries.

    • Perform rectangular range queries.

      -- 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);

      The ST_MakeBox2D function creates an envelope that is also known as a rectangular polygon.

    • Perform range queries based on geometry buffers.

      -- 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);

      For more information about ST_DWithin, see ST_DWithin.

    • Check the intersections of polygons inside or on the boundary.

      -- 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);

      The ST_* operator is case-insensitive. For more information, see Introduction to PostGIS.

    Note

    AnalyticDB for PostgreSQL V6.0 is not compatible with specific features of PostGIS such as CREATE extension postgis_topology. We recommend that you do not create tables that contain geography columns. If you must create a table of this type, set the SRID to 0 or 4326.

Use cases

Case 1: Electronic fence

A monitoring service provider of passenger traffic uses GPS terminals on passenger cars to collect location data. Common services include deviation alarm, frequency reminders for popular service areas, and driving condition warnings for specific areas such as accident-prone areas and icy roads. Electronic fences can be used for these services. Take driving condition warnings for specific areas as an example. Such areas are infrequently changed and contain small volumes of data. The area data can be collected once and updated on a regular basis. We recommend that you use replicated tables to record the area data. Sample statement:

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

After specific area data of the Polygon or MultiPolygon type is inserted, AnalyticDB for PostgreSQL uses the ANALYZE TABLE statement to collect statistics and creates Generalized Search Tree (GiST) indexes. Warnings can be classified into two categories: warnings triggered when the car is fully enclosed within the area, and warnings triggered when the car touches the boundary. Each warning type uses different spatial operators. Sample statements:

-- Fully enclosed within the area 
SELECT rid, name FROM ky_region
WHERE ST_Contains(geom, ST_GeomFromText('POINT(116 39)'));

-- The boundary may be touched 
SELECT rid, name from ky_region
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(116 39)'));

After the latitude and longitude of a specific point are provided, the statement queries the records that contain or intersect with the specified point in the geom field. If no record is returned, the car did not enter an area. If one record is returned, the car entered an area. If multiple records are returned, the car entered multiple areas. If areas in the table are overlapped, check whether the overlapped areas are valid.

Case 2: Smart transportation

A smart transportation database contains a driving trace table and other business tables. AnalyticDB for PostgreSQL queries the IDs of driving traces that have entered an area from the driving trace table. Table schema:

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'),
 ......
);

AnalyticDB for PostgreSQL creates partitions for the trace table by day, collects statistics on the data that is imported each day, and creates GiST spatial indexes for the partitions. Sample statement:

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);

Spatial queries on the trace table that contains hundreds of millions of rows can be responded within less than 80 milliseconds.

Case 3: Customer traffic analysis

An Internet service provider uses AnalyticDB for PostgreSQL to analyze their customer traffic. A database contains two business tables: a user table and a shop table. Table schemas:

-- user
CREATE TABLE user_label (
  ghash7          int, 
  uid             int, 
  workday_geo     geometry, 
  weekend_geo     geometry) 
distributed by (ghash7);

-- shop
CREATE TABLE user_shop (
  ghash7          int, 
  sid             int, 
  shop_poly       geometry) 
distributed by (ghash7);

The business tables use Geohash or ZOrder to reduce the number of geospatial dimensions for data distribution instead of creating spatial indexes. Sample statement:

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;