Using PostGIS

Installing PostGIS Extension

1. Access Download page of the MogDB official website, download the PostGIS extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf postgis-2.4.2-3.1.0-01-CentOS-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd postgis/
   make install

4. Connect to the database and execute the create extension postgis; to use it.

   MogDB=# create extension postgis;
   CREATE EXTENSION

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, see PostGIS 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