PolarDB:Modelo geométrico

Tipos atômicos

Tipos parametrizados

Estes tipos são específicos do GanosBase e utilizam parâmetros matemáticos em vez de sequências de pontos.

Tipos de coleção

SELECT * FROM geometry_columns;

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

CREATE EXTENSION ganos_geometry WITH SCHEMA public CASCADE;

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

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

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

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

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

 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)

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

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

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

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

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

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

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

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
 ...

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)

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)

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

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

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

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)

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)

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)

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)

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