PolarDB:GanosBase Geometry

Coordinate system

A geometry value describes a shape in 2D or 3D coordinate space. Each coordinate has an x-value and a y-value. Optionally, it may include:

• A z-value — typically represents elevation.

• An m-value — a measure value such as time or distance along a line.

When z or m values are present, every point in the geometry must define them. A geometry with only z or m values has 3D coordinates; a geometry with both has 4D coordinates.

Spatial Reference System

A Spatial Reference System (SRS) maps geometry coordinates to a real-world location on Earth's surface. GanosBase identifies each SRS by an integer called an SRID. Every geometry object carries an SRID that ties it to a specific SRS.

For more information, see Spatial reference.

Geometry types

GanosBase organizes geometry types into three groups:

Atomic types — fundamental, non-composite geometries:

Parameterized types — GanosBase extensions for curved geometries:

Collection types — groupings of geometry objects:

Supported data formats

The SFA standard defines two portable formats for exchanging geometry data:

• Well-Known Text (WKT) — a human-readable text representation.

• Well-Known Binary (WKB) — a compact, full-precision binary representation.

Both WKT and WKB include the geometry type and its coordinates, but not the SRID. Non-binary data is not supported.

GanosBase extends these with EKWT and EKWB, which add support for 3DZ, 3DM, and 4D coordinates, and embed the SRID directly in the value. Any valid WKT or WKB value is also valid EKWT or EKWB.

For direct data import, GanosBase accepts WKT, WKB, EKWT, EKWB, KML, GML, GeoHash, and GeoJSON. Use tools such as the Esri Shapefile importer to load data in other formats.

How spatial indexes work

Spatial indexes let GanosBase avoid full table scans when processing large datasets. GanosBase supports three spatial index types:

When to choose:

• Use GiST as your default. It works well across all geometry types and query patterns.

• Use BRIN when you have a very large, append-only or rarely updated dataset and storage space matters more than query speed.

• Use SP-GiST when your geometries don't overlap and you don't need KNN support.

Geometry columns view

The geometry_columns view lists every geometry column registered in the database. It follows the OGC Simple Features Specification for SQL.

Query all registered geometry columns:

SELECT * FROM geometry_columns;

Step 1: Install the extension

CREATE EXTENSION ganos_geometry CASCADE;

To avoid permission issues, install the extension in the public schema:

CREATE EXTENSION ganos_geometry WITH SCHEMA public CASCADE;

Step 2: Create a table with a geometry column

Two methods are supported. Method 1 is simpler for most use cases.

Method 1: Specify the geometry type in the column definition

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

Optionally, add a validity constraint to reject malformed geometry values:

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

Step 3: Import geometry data

Use ST_GeomFromText to convert Well-Known Text (WKT) strings into geometry values. All examples use SRID 3857 (Web Mercator).

INSERT INTO roads (id, geom, road_name)
VALUES (1, ST_GeomFromText('LINESTRING(191232 243118,191108 243242)', 3857), 'North Fifth-Ring Road');

INSERT INTO roads (id, geom, road_name)
VALUES (2, ST_GeomFromText('LINESTRING(189141 244158,189265 244817)', 3857), 'East Fifth-Ring Road');

INSERT INTO roads (id, geom, road_name)
VALUES (3, ST_GeomFromText('LINESTRING(192783 228138,192612 229814)', 3857), 'South Fifth-Ring Road');

INSERT INTO roads (id, geom, road_name)
VALUES (4, ST_GeomFromText('LINESTRING(189412 252431,189631 259122)', 3857), 'West Fifth-Ring Road');

INSERT INTO roads (id, geom, road_name)
VALUES (5, ST_GeomFromText('LINESTRING(190131 224148,190871 228134)', 3857), 'East Chang''an Avenue');

INSERT INTO roads (id, geom, road_name)
VALUES (6, ST_GeomFromText('LINESTRING(198231 263418,198213 268322)', 3857), 'West Chang''an Avenue');

Verify the import by reading back the WKT representation:

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)

Step 4: Create a spatial index

For most workloads, create a GiST index:

CREATE INDEX sp_geom_index ON roads USING GIST (geom);

-- Update statistics so the query planner uses the index
VACUUM ANALYZE roads (geom);

For N-dimensional geometries, use the gist_geometry_ops_nd operator class:

CREATE INDEX <index_name> ON <table_name> USING GIST (<geometry_column> gist_geometry_ops_nd);

To create a BRIN index instead:

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

-- BRIN index for 3D geometries
CREATE INDEX <index_name> ON <table_name> USING BRIN (<geometry_column> brin_geometry_inclusion_ops_3d);

-- BRIN index for 4D geometries
CREATE INDEX <index_name> ON <table_name> USING BRIN (<geometry_column> brin_geometry_inclusion_ops_4d);

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

Step 5: Run spatial queries

The following examples use two sample tables:

bc_roads
  gid       integer           — Unique ID
  name      character varying — Road name
  the_geom  geometry          — LineString

bc_municipality
  gid       integer           — Unique ID
  code      integer           — Unique code
  name      character varying — City or town name
  the_geom  geometry          — Polygon

Measurement: calculate total road length and city area

-- Total road length in kilometers
SELECT sum(ST_Length(the_geom)) / 1000 AS km_roads FROM bc_roads;

-- Result
--   km_roads
-- ------------------
--   70842.1243039643

-- Area of a specific city in hectares
SELECT ST_Area(the_geom) / 10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

-- Result
--     hectares
-- ------------------
--   32657.9103824927

Spatial relationship queries

Spatial relationship functions answer questions like "which municipalities contain these roads?" or "do these two geometries overlap?" Choose the function that matches your question:

Example — find total road length inside 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;

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

Example — ST_Covers vs. ST_Contains on circles:

SELECT
    ST_Covers(smallc, smallc)            AS small_in_small,
    ST_Covers(smallc, bigc)              AS small_covers_big,
    ST_Covers(bigc, ST_ExteriorRing(bigc)) AS big_covers_exterior,
    ST_Contains(bigc, ST_ExteriorRing(bigc)) AS big_contains_exterior
FROM (
    SELECT
        ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) AS smallc,
        ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) AS bigc
) AS foo;

-- Result
--  small_in_small | small_covers_big | big_covers_exterior | big_contains_exterior
-- ----------------+------------------+---------------------+-----------------------
--  t              | f                | t                   | f

Example — ST_Disjoint:

SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
--  t   (point and line do not share any space)

SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
--  f   (point lies on the line)

Example — ST_Within:

SELECT
    ST_Within(smallc, smallc) AS small_in_small,
    ST_Within(smallc, bigc)   AS small_in_big,
    ST_Within(bigc, smallc)   AS big_in_small,
    ST_Within(ST_Union(smallc, bigc), bigc) AS union_in_big,
    ST_Within(bigc, ST_Union(smallc, bigc)) AS big_in_union,
    ST_Equals(bigc, ST_Union(smallc, bigc)) AS big_is_union
FROM (
    SELECT
        ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) AS smallc,
        ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) AS bigc
) AS foo;

-- Result
--  small_in_small | small_in_big | big_in_small | union_in_big | big_in_union | big_is_union
-- ----------------+--------------+--------------+--------------+--------------+--------------
--  t              | t            | f            | t            | t            | t

Access geometry properties

-- Check whether a geometry is simple (no self-intersections)
SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
--  t

SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
--  f   (LineString self-intersects)

-- Find the largest city with interior rings (traffic circles, lakes, etc.)
SELECT gid, name, ST_Area(the_geom) AS area
FROM bc_municipality
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC
LIMIT 1;

-- Result
--  gid |  name  |        area
-- -----+--------+--------------------
--  12  | Anning | 257374619.430216

Step 6: Remove the extension (optional)

DROP EXTENSION ganos_geometry CASCADE;