Spatial Data Type

This document mainly introduces the spatial data types supported by YMatrix.

Usually, a database supports common data types such as strings, values, and dates, but because spatial databases need to express data geographical features (Geographic Features), they need to add additional data types: spatial data types. These spatial data types encapsulate spatial structures such as boundaries and dimensions.

YMatrix uses PostGIS extension to support spatial data types.

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

1 Install PostGIS

PostGIS depends on geos39, you need to first install the RPM package on each node:

$ 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 Using PostGIS

2.1 Commonly used spatial data types of PostGIS

Common data types supported by PostGIS include:

POINT: Point
LINESTRING: Line segment
POLYGON: Polygon

2.1.1 Create a table

Create a table that includes spatial data types:

# Point table
=# CREATE TABLE global_points (
   id INT,
   name VARCHAR(64),
   location GEOGRAPHY(POINT)
   )
   USING MARS3
   DISTRIBUTED BY (id)
   ORDER BY (id);

# Face table
=# CREATE TABLE global_areas (
   id INT,
   name VARCHAR(64),
   area GEOGRAPHY(POLYGON)
   )
   USING MARS3
   DISTRIBUTED BY (id)
   ORDER 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. PostGIS supports spatial indexes:

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 );