PolarDB:Geometry model

Geometry type hierarchy

Geometry is an abstract type. All concrete geometry values belong to one of its subtypes, organized into three categories:

• Atomic types — indivisible shapes: Point, LineString, Polygon

• Parameterized types — curve-defined shapes unique to GanosBase: EllipticalString, Bezier3Curve

• Collection types — grouped shapes: MultiPoint, MultiLineString, MultiPolygon, GeometryCollection, CompoundCurve, CurvePolygon

Each subtype is described in detail in the Geometry data types section.

Coordinate dimensions

Geometry models shapes in a 2D or 3D coordinate system. Every coordinate has an x-value and a y-value, and may also carry:

If z or m values are present, they must be defined for every point in the geometry.

Spatial reference system

A spatial reference system (SRS) defines how coordinates map to a specific location on Earth's surface. GanosBase associates each geometry value with an SRS through an integer identifier called the SRID. The SRID is a foreign key that references the spatial_ref_sys table.

For details, see Spatial reference.

Supported input formats

GanosBase accepts geometry data in these formats directly:

Any valid WKT or WKB value is also valid EKWT or EKWB. For other formats such as Esri Shapefiles, use an external import tool before loading into GanosBase.

Geometry data types

geometry_columns view

The geometry_columns view reads all geometry columns from the system catalog table of the database. It follows the Simple Features Specification for SQL defined by OGC.

Query all geometry columns in the current database:

SELECT * FROM geometry_columns;

Spatial indexes

Spatial indexes allow GanosBase to avoid global sequential scans of the database when processing large spatial datasets. Spatial indexes organize data in a tree structure that can be quickly traversed to find a specific record.

GanosBase provides three spatial index types:

Create the extension

-- Create the ganos_geometry extension.
CREATE EXTENSION ganos_geometry CASCADE;

Note: Create the extension in the public schema to avoid permission issues.

CREATE EXTENSION ganos_geometry WITH SCHEMA public CASCADE;

Create a geometry table

Two methods are available. Use Method 1 when you know the geometry type and SRID upfront.

Method 1 — Define the geometry column in the CREATE TABLE statement:

CREATE TABLE roads (
  id        int4,
  road_name varchar(25),
  geom      geometry(LINESTRING, 3857)
);

Method 2 — Add a geometry column to an existing table:

CREATE TABLE roads (id int4, road_name varchar(25));
SELECT AddGeometryColumn('roads', 'geom', 3857, 'LINESTRING', 2);

Add a geometry constraint

ALTER TABLE roads ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(geom));

Import geometry data

All six rows use ST_GeomFromText to parse WKT strings with SRID 3857.

INSERT INTO roads (id, geom, road_name) VALUES
  (1, ST_GeomFromText('LINESTRING(191232 243118,191108 243242)', 3857), 'North Fifth-Ring Road'),
  (2, ST_GeomFromText('LINESTRING(189141 244158,189265 244817)', 3857), 'East Fifth-Ring Road'),
  (3, ST_GeomFromText('LINESTRING(192783 228138,192612 229814)', 3857), 'South Fifth-Ring Road'),
  (4, ST_GeomFromText('LINESTRING(189412 252431,189631 259122)', 3857), 'West Fifth-Ring Road'),
  (5, ST_GeomFromText('LINESTRING(190131 224148,190871 228134)', 3857), 'East Chang''an Avenue'),
  (6, ST_GeomFromText('LINESTRING(198231 263418,198213 268322)', 3857), 'West Chang''an Avenue');

Query geometry data

SELECT id, ST_AsText(geom) AS geom, road_name FROM roads;

Expected output:

 id | geom                                    | road_name
----+-----------------------------------------+----------------------
  1 | LINESTRING(191232 243118,191108 243242) | North Fifth-Ring Road
  2 | LINESTRING(189141 244158,189265 244817) | East Fifth-Ring Road
  3 | LINESTRING(192783 228138,192612 229814) | South Fifth-Ring Road
  4 | LINESTRING(189412 252431,189631 259122) | West Fifth-Ring Road
  5 | LINESTRING(190131 224148,190871 228134) | East Chang'an Avenue
  6 | LINESTRING(198231 263418,198213 268322) | West Chang'an Avenue
(6 rows)

Create a spatial index

GiST index (recommended):

-- Standard 2D GiST index:
CREATE INDEX <index_name> ON <table_name> USING GIST (<geometry_column>);

-- N-dimensional GiST index:
CREATE INDEX <index_name> ON <table_name> USING GIST (<geometry_column> gist_geometry_ops_nd);

-- Update statistics after building the index:
VACUUM ANALYZE <table_name> (<geometry_column>);

-- Example:
CREATE INDEX sp_geom_index ON roads USING GIST(geom);
VACUUM ANALYZE roads (geom);

BRIN index (large, mostly static tables):

-- Standard BRIN index:
CREATE INDEX <index_name> ON <table_name> USING BRIN (<geometry_column>);

-- 3D or 4D BRIN index:
CREATE INDEX <index_name> ON <table_name> USING BRIN (<geometry_column> brin_geometry_inclusion_ops_3d);
CREATE INDEX <index_name> ON <table_name> USING BRIN (<geometry_column> brin_geometry_inclusion_ops_4d);

-- BRIN index with custom block size:
CREATE INDEX <index_name> ON <table_name> USING BRIN (<geometry_column>) WITH (pages_per_range = <number>);

Measure and analyze spatial data

The following examples use two sample tables:

bc_roads        — columns: gid (integer), name (varchar), the_geom (geometry/LineString)
bc_municipality — columns: gid (integer), code (integer), name (varchar), the_geom (geometry/Polygon)

Calculate total road length:

SELECT sum(ST_Length(the_geom)) / 1000 AS km_roads FROM bc_roads;

      km_roads
------------------
 70842.1243039643
(1 row)

Calculate the area of a municipality:

SELECT ST_Area(the_geom) / 10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

      hectares
------------------
 32657.9103824927
(1 row)

Run spatial relationship queries

GanosBase supports the full OGC set of spatial relationship functions. The examples below demonstrate common patterns.

ST_Contains — find roads within each municipality:

SELECT m.name, sum(ST_Length(r.the_geom)) / 1000 AS roads_km
FROM bc_roads AS r, bc_municipality AS m
WHERE ST_Contains(m.the_geom, r.the_geom)
GROUP BY m.name
ORDER BY roads_km;

       name          |      roads_km
---------------------+------------------
 SURREY              | 1539.47553551242
 VANCOUVER           | 1450.33093486576
 LANGLEY DISTRICT    |  833.793392535662
 BURNABY             |  773.769091404338
 PRINCE GEORGE       |  694.37554369147
 ...

ST_Covers — test whether one circle covers another:

SELECT
  ST_Covers(smallc, smallc) AS smallinsmall,
  ST_Covers(smallc, bigc)   AS smallcoversbig,
  ST_Covers(bigc, ST_ExteriorRing(bigc)) AS bigcoversexterior,
  ST_Contains(bigc, ST_ExteriorRing(bigc)) AS bigcontainsexterior
FROM (
  SELECT
    ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) AS smallc,
    ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) AS bigc
) AS foo;

 smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
--------------+----------------+-------------------+---------------------
 t            | f              | t                 | f
(1 row)

ST_Disjoint — test whether two geometries share no points:

SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);

 st_disjoint
-------------
 t
(1 row)

SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);

 st_disjoint
-------------
 f
(1 row)

ST_Overlaps, ST_Crosses, ST_Intersects, ST_Contains — compare a point and a line:

SELECT
  ST_Overlaps(a, b)    AS a_overlap_b,
  ST_Crosses(a, b)     AS a_crosses_b,
  ST_Intersects(a, b)  AS a_intersects_b,
  ST_Contains(b, a)    AS b_contains_a
FROM (
  SELECT
    ST_GeomFromText('POINT(1 0.5)')             AS a,
    ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') AS b
) AS foo;

 a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
-------------+-------------+----------------+--------------
 f           | f           | t              | t

ST_Relate — test a specific DE-9IM pattern:

SELECT ST_Relate(
  ST_GeometryFromText('POINT(1 2)'),
  ST_Buffer(ST_GeometryFromText('POINT(1 2)'), 2),
  '0FFFFF212'
);

 st_relate
-----------
 t

ST_Touches — test whether geometries share only a boundary point:

SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry);

 st_touches
------------
 f
(1 row)

SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);

 st_touches
------------
 t
(1 row)

ST_Within — test containment between buffered circles:

SELECT
  ST_Within(smallc, smallc)              AS smallinsmall,
  ST_Within(smallc, bigc)                AS smallinbig,
  ST_Within(bigc, smallc)                AS biginsmall,
  ST_Within(ST_Union(smallc, bigc), bigc) AS unioninbig,
  ST_Within(bigc, ST_Union(smallc, bigc)) AS biginunion,
  ST_Equals(bigc, ST_Union(smallc, bigc)) AS bigisunion
FROM (
  SELECT
    ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) AS smallc,
    ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) AS bigc
) AS foo;

 smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
--------------+------------+------------+------------+------------+------------
 t            | t          | f          | t          | t          | t
(1 row)

Access geometry properties

Check whether a geometry is simple:

SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));

 st_issimple
-------------
 t
(1 row)

SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));

 st_issimple
-------------
 f
(1 row)

Find the largest city with interior rings (traffic circles):

SELECT gid, name, ST_Area(the_geom) AS area
FROM bc_municipality
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC
LIMIT 1;

 gid | name   | area
-----+--------+------------------
  12 | Anning | 257374619.430216
(1 row)

Drop the extension

-- Remove the ganos_geometry extension and all dependent objects.
DROP EXTENSION ganos_geometry CASCADE;