Help Center/ GaussDB(DWS)/ Developer Guide/ Standard data warehouse (8.1.3.x)/ Using PostGIS Extension/ Using PostGIS
Updated on 2024-11-05 GMT+08:00
View PDF
Share

Using PostGIS

  • The third-party software that the PostGIS Extension depends on needs to be installed separately. If you need to use PostGIS, submit a service ticket or contact technical support to submit an application.
  • If the error message "ERROR: EXTENSION is not yet supported." is displayed, the PostGIS software package is not installed. Contact technical support.
  • The uuid-ossp extension has been preloaded in GaussDB(DWS). You can directly use the uuid function supported by GaussDB(DWS) without running the CREATE EXTENSION uuid-ossp command.

Creating PostGIS Extension

Run the CREATE EXTENSION command to create PostGIS Extension.

1
 
CREATE EXTENSION postgis;

Using PostGIS Extension

Use the following function to invoke a PostGIS Extension:

1
 
SELECT GisFunction (Param1, Param2,......);

GisFunction is the function, and Param1 and Param2 are function parameters. The following SQL statements are a simple illustration for PostGIS use. For details about related functions, see PostGIS 2.4.2 Manual.

Example 1: Create a geometry table.

1
2
 
CREATE TABLE cities ( id integer, city_name varchar(50) );
SELECT AddGeometryColumn('cities', 'position', 4326, 'POINT', 2);

Example 2: Insert geometry data.

1
2
3
 
INSERT INTO cities (id, position, city_name) VALUES (1,ST_GeomFromText('POINT(-9.5 23)',4326),'CityA');
INSERT INTO cities (id, position, city_name) VALUES (2,ST_GeomFromText('POINT(-10.6 40.3)',4326),'CityB');
INSERT INTO cities (id, position, city_name) VALUES (3,ST_GeomFromText('POINT(20.8 30.3)',4326), 'CityC');

Example 3: Calculate the distance between any two cities among three cities.

1
 
SELECT p1.city_name,p2.city_name,ST_Distance(p1.position,p2.position) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

Deleting PostGIS Extension

Run the following command to delete PostGIS Extension from GaussDB(DWS):

1
 
DROP EXTENSION postgis [CASCADE];

If PostGIS Extension is the dependee of other objects (for example, geometry tables), you need to add the CASCADE keyword to delete all these objects.

Parent topic: Using PostGIS Extension