Using PostGIS

Downloading PostGIS Extension

The Download page of the MogDB official website provides the PostGIS extension for you to download and install.

Installing PostGIS Extension

For details, see gs_install_plugin or gs_install_plugin_local.

Creating PostGIS Extension

Run the CREATE EXTENSION command to create postgis, postgis_raster, and postgis_topology, respectively.

MogDB=# CREATE EXTENSION postgis;

MogDB=# CREATE EXTENSION postgis_raster;

MogDB=# set behavior_compat_options='bind_procedure_searchpath';
CREATE EXTENSION postgis_topology;

Using PostGIS Extension

Use the following function to invoke PostGIS Extension:

MogDB=# 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, seePostGIS 2.4.2 Manual.

Example 1: Create a geometry table.

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

Example 2: Insert geometry data.

MogDB=# INSERT INTO cities (id, position, city_name) VALUES (1,ST_GeomFromText('POINT(-9.5 23)',4326),'CityA');
MogDB=# INSERT INTO cities (id, position, city_name) VALUES (2,ST_GeomFromText('POINT(-10.6 40.3)',4326),'CityB');
MogDB=# 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.

MogDB=# 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 a PostGIS extension from MogDB:

MogDB=# DROP EXTENSION postgis [CASCADE];

NOTE: 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.

To completely delete PostGIS extension, run gs_om as user omm to delete PostGIS and the dynamic link libraries it depends on.

gs_om -t postgis -m rmlib