Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Spatial Data Types

This document describes the spatial data types supported by YMatrix.

Typically, a database supports common data types such as string, number, and date. However, spatial databases require additional data types—spatial data types—to represent geographic features. These spatial data types encapsulate spatial structures such as boundaries and dimensions.

YMatrix uses the PostGIS extension to support spatial data types.

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

1 Installing PostGIS

PostGIS depends on geos39. You must 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 the extension:

=# CREATE EXTENSION postgis;

2 Using PostGIS

2.1 Common Spatial Data Types in PostGIS

PostGIS supports the following common spatial data types:

  • POINT: Point
  • LINESTRING: Line segment
  • POLYGON: Polygon

2.1.1 Creating Tables

Create tables containing 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);

-- Polygon table
=# CREATE TABLE global_areas (
   id INT,
   name VARCHAR(64),
   area GEOGRAPHY(POLYGON)
   )
   USING MARS3
   DISTRIBUTED BY (id)
   ORDER BY (id);

2.1.2 Inserting 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 polygon 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 Common Spatial Functions

Calculate distance between point coordinates:

=# 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)

Check if a point lies within a polygon:

=# 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)

Compute intersection of two polygons:

=# 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 Creating Spatial Indexes

Spatial indexes are required for efficient spatial queries. PostGIS supports the following spatial index types:

  • GIST: Generalized Search Tree
  • BRIN: Block Range Index
  • SP-GiST: Space-Partitioned Generalized Search Tree

Create an index on the location column:

=# CREATE INDEX global_points_gix ON global_points USING GIST (location);

Create an index on the area column:

=# CREATE INDEX global_areas_gix ON global_areas USING GIST (area);
← Previous
JSON/JSONB、MXKV/MXKV2
Next →
Overview