Spatial Data Type

A normal database has data types such as strings, numbers and dates. The spatial database adds additional data types (spatial data types) to express geographic features. These spatial data types abstract and encapsulate spatial structures such as boundary and dimension.

MatrixDB uses PostGIS extension to support spatial data types.

Note: This feature is only available in the Enterprise Edition.

1. Install PostGIS

Install rpm package on each node (postgis depends on geos39, so install the dependencies first):

sudo yum install https://ftp.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64/geos39-3.9.1-1.rhel7.x86_64.rpm
sudo yum install mxdb-postgis-2.5-1.el7.x86_64.rpm

Connect to the database and create an extension:

CREATE EXTENSION postgis;

2. Use of PostGIS

2.1 Commonly used spatial data types of PostGIS

Common data types supported by PostGIS include:

POINT:
LINESTRING: Line segment
POLYGON: Polygon

2.1.1 Create tables

Create a table that includes spatial data types:

-- Point table
CREATE TABLE global_points (
    id INT,
    name VARCHAR(64),
    location GEOGRAPHY(POINT)
)
Distributed by(id);

-- Face table
CREATE TABLE global_areas (
    id INT,
    name VARCHAR(64),
    area GEOGRAPHY(POLYGON)
)
Distributed by(id);

2.1.2 Insert data

Insert point data:

INSERT INTO global_points VALUES(1, 'point1', 'POINT(-110 29)');
INSERT INTO global_points VALUES(2, 'point2', 'POINT(-111 30)');

Insert face data:

INSERT INTO global_areas VALUES(1, 'area1', 'POLYGON((-100 25, -100 30, -120 30, -120 25, -100 25))');
INSERT INTO global_areas VALUES(2, 'area2', 'POLYGON((-90 25, -90 30, -100 30, -100 25, -90 25))');

2.1.3 Commonly used spatial calculation functions

Calculate point coordinate distance:

SELECT name, ST_Distance('POINT(-110 30)'::geography, location) FROM global_points;
  name  |   st_distance
-------+------------------------------
 point1 | 110844.074057415
 point2 |  96485.974080553
(2 rows)

Calculate area:

SELECT id,name,ST_Area(area) FROM global_areas ;
 id | name  |      st_area
----+-----------------------------------------------------------------------------------------------------------------------------
  1 | area1 | 1098617743576.8588
  2 | area2 | 547807626660.82526
(2 rows)

Determine whether the point is in the face:

SELECT a.name, p.name FROM global_points AS p, global_areas AS a WHERE ST_Intersects (a.area, p.location);
 name  |  name
------+---------------------------------------------------------------------------------------------------------------------------
 area1 | point1
 area1 | point2
(2 rows)

The intersection of the calculation surface:

SELECT ST_AsText(ST_Intersection(a1.area, a2.area)) FROM global_areas as a1, global_areas as a2 WHERE a1.name = 'area1' AND a2.name = 'area2';
st_astext
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 LINESTRING(-100 24.9999999999992,-100 29.9999999999995)
(1 row)

2.2 Create a spatial index

Spatial data needs to be indexed to make spatial calculations more efficient. The spatial indexes supported by PostGIS include:

GIST: General Query Tree
BRIN: Block range index
SP-GiST: Spatial partitioning general query tree

Indexing the location field:

CREATE INDEX global_points_gix ON global_points USING GIST ( location );

Indexing the area field:

CREATE INDEX global_areas_gix ON global_areas USING GIST ( area );