AnalyticDB:Geometry model

Measure geometry

Use ST_Length and ST_Area to compute distances and areas. Both functions return results in the units of the geometry's coordinate reference system (meters for SRID 3857).

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

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

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

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

Test spatial relationships

GanosBase Geometry supports the full set of PostGIS spatial relationship functions. The table below lists the most common ones.

Example — find road length per municipality using ST_Contains:

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;

Example — compare coverage between two circles using ST_Covers:

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_Covers and ST_Contains differ in how they handle boundary points. ST_Covers returns true when A covers the boundary of B; ST_Contains does not.

Example — test disjoint points and lines using ST_Disjoint:

-- Point not on line: disjoint
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
-- Result: t

-- Point on line: not disjoint
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
-- Result: f

Example — test overlap and intersection using ST_Overlaps:

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

Example — test a spatial relationship using ST_Relate:

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

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

Example — test whether a point touches a line using ST_Touches:

-- Interior point: does not touch (ST_Touches requires boundary contact only)
SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry);
-- Result: f

-- Endpoint of line: touches
SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
-- Result: t

Example — check containment for nested circles using ST_Within:

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)

Check geometry validity

Use ST_IsSimple to verify that a geometry has no self-intersections, and ST_NRings to count the rings in a polygon.

-- A valid closed ring (simple polygon)
SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
-- Result: t

-- A self-intersecting linestring (not simple)
SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
-- Result: f

-- Find the municipality with the largest area that has more than one ring
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)