PostGIS is an extension of PostgreSQL and provides spatial features including objects, indexes, functions, and operators.

Note
  • PostGIS conforms to Open Geospatial Consortium (OGC) standards.
  • AnalyticDB for PostgreSQL V4.3 and V6.0 support PostGIS 2.0.3 and PostGIS 2.5.3 respectively.

Common operations

1) Connect to an AnalyticDB for PostgreSQL instance

For more information, see Connect to an AnalyticDB for PostgreSQL instance.

2) Install the PostGIS extension for the first time

Execute the following statement to install the PostGIS extension:

create extension postgis;

Execute the following statements to query the version of PostGIS that is installed:

select postgis_version();
select postgis_full_version();

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

Execute the following SQL 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 SQL 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 SQL 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 SQL statements to execute typical spatial queries

  • Execute 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.

  • Execute 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, visit ST_DWithin.

  • Check the intersections of polygons either 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, visit Introduction to PostGIS.

Note

AnalyticDB for PostgreSQL V6.0 is not compatible with certain features of PostGIS such as create extension postgis_topology. Therefore, we recommend that you do not create table columns of the geography type. If you need to create table columns of the geography type, the default SRID value is 0 or 4326.

Scenarios

1) Electronic fence

A monitoring service provider of passenger traffic gathers positioning data by using GPS terminals on passenger cars. Common services include deviation alarm, frequency reminders for popular service areas, and warnings about driving conditions in certain areas such as accidents or puddly and icy roads. Such services are typical scenarios of electronic fence. Take driving condition warnings as an example. The region table can be replicated because specific regions have small amounts of fixed data. Data can be collected once and updated on a regular basis. The corresponding SQL statement is as follows:

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

After specific region data of the Polygon or MultiPolygon type is inserted, AnalyticDB for PostgreSQL collects the data by using the ANALYZE TABLE statement and creates Generalized Search Tree (GiST) indexes. Warnings can be classified into two categories: warnings triggered when the car is fully enclosed within the region, and warnings triggered when the car touches the boundary. Each warning type uses different spatial operators. The corresponding SQL statements are as follows:

-- Fully enclosed within the region
select rid, name from ky_region
where ST_Contains(geom, ST_GeomFromText('POINT(116 39)'));

-- Contact with boundary
select rid, name from ky_region
where ST_Intersects(geom, ST_GeomFromText('POINT(116 39)'));

Description: After the latitude and longitude of a specified point are provided, the SQL statement queries the records that contain or intersect with the specified point in the geom field. If no record is returned, the car has not entered into any regions. One record indicates that the car has entered once. Multiple records indicate that the car has entered into a specific region. If multiple records are returned, some regions are overlapped, and you need to check whether the overlapped regions are valid.

2) Smart transportation

In a smart traffic scenario, a database contains linetype trace tables and other business tables. AnalyticDB for PostgreSQL queries the ID of a historical driving trace in the historical trace table. The schema of the trace table is as follows:

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 of data imported each day, and creates GiST spatial indexes for the partitions. The corresponding SQL statement is as follows:

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

Hundreds of millions of spatial queries on the trace table can be responded within less than 80 ms.

3) Shop traffic analysis

An Internet service provider analyzes their shop traffic by using AnalyticDB for PostgreSQL. A database has two business tables: an attendance table named User and a shop table named Shop. The schema of the tables is as follows:

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

A skilled design of the business tables is to use Geohash or ZOrder coding to reduce geospatial dimensions to partition keys instead of creating geospatial indexes. The SQL statement to collect statistics of customer traffic is as follows:

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;