ApsaraDB RDS:GanosBase Geometry model

GiST indexes

Create a GiST (Generalized Search Tree) index on a geometry column:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );

For n-dimensional data, specify the gist_geometry_ops_nd operator class:

CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);

Run VACUUM ANALYZE after creating the index:

VACUUM ANALYZE [table_name] [(column_name)];

Example:

CREATE INDEX sp_geom_index ON ROADS USING GIST(geom);
VACUUM ANALYZE ROADS (geom);

BRIN indexes

Create a BRIN (Block Range Index) index on a geometry column:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );

For 3D or 4D data, specify the corresponding operator class:

-- 3D BRIN index
CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d);

-- 4D BRIN index
CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d);

Set a custom page range with the pages_per_range parameter:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]);

Check whether a geometry is simple

ST_IsSimple() returns true if a geometry has no self-intersections or self-tangencies.

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)

Count rings and calculate area

ST_NRings() returns the number of rings in a geometry. ST_Area() returns the area. The following query finds the largest municipality that has holes (such as traffic circles) in its geometry:

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)

Calculate total length

ST_Length() returns the length of a geometry. Sum all road lengths and convert to kilometers:

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

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

Calculate area

ST_Area() returns the area of a polygon geometry. Calculate the area in hectares:

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

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

ST_Contains

Test whether one geometry contains another. The following query calculates the total road length per 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 geometry covers another. Unlike ST_Contains, ST_Covers returns true for boundary points.

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 have no points in common:

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, and ST_Intersects

Test overlap, crossing, and intersection relationships between geometries:

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 spatial relationship pattern between two geometries:

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

ST_Touches

Test whether two geometries touch at their boundaries without intersecting interiors:

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 whether one geometry is completely within another:

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)