Help Center> Data Warehouse Service> Developer Guide> PostGIS Extension> Using PostGIS
View PDF

Using PostGIS

Creating PostGIS Extension

Run the CREATE EXTENSION command to create PostGIS Extension.

1
 
CREATE EXTENSION postgis;

After PostGIS Extension is created, you can run the following command to enable the grid function in PostGIS if needed:

1
 
CREATE EXTENSION postgis_raster;

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: PostGIS Extension