All Products
Search
Document Center

ApsaraDB RDS:Geometry model

Last Updated:Oct 17, 2024

GanosBase Geometry is a spatial geometry extension of PostgreSQL. GanosBase Geometry complies with OpenGIS specifications and allows PostgreSQL to store and manage 2D (X, Y), 3D (X, Y, Z), and 4D (X, Y, Z, M) spatial geometry data. GanosBase Geometry also provides various features such as spatial geometry objects, indexes, functions, and operators.

Overview

The geometry model is fully compatible with PostGIS operations and allows you to smoothly migrate existing applications.

Getting started

  • Create extensions.

    -- Create a geometry extension.
    Create extension ganos_geometry with schema public cascade;
    Important

    Do not create GanosBase extensions or PostGIS extensions in the same schema. Otherwise, the error message ERROR: table "spatial_ref_sys" is not a member of the extension being created is displayed.

    When you create a GanosBase or PostGIS extension, a table named spatial_ref_sys is automatically created. If you create a GanosBase extension and a PostGIS extension in the same schema, a table conflict occurs. We recommend that you create GanosBase and PostGIS extensions in different schemas. You can delete the PostGIS extension before you create a GanosBase extension.

  • Create a geometry table.

    -- Method 1: Create a table that contains a geometry field.
    CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25), geom geometry(LINESTRING,3857) );
    
    -- Method 2: Create a regular table and then add a geometry field to the table.
    CREATE TABLE ROADS (ID int4, ROAD_NAME varchar(25));
    SELECT AddGeometryColumn( 'roads', 'geom', 3857, 'LINESTRING', 2);
  • Add geometry constraints.

    ALTER TABLE ROADS ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(geom));
  • Import geometry data.

    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');
  • Query geometry object information.

    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)
  • Create indexes.

    -- Create a GiST index.
    CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); 
    CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);
    VACUUM ANALYZE [table_name] [(column_name)];
    
    -- Example:
    Create INDEX sp_geom_index ON ROADS USING GIST(geom);
    VACUUM ANALYZE ROADS (geom);
    
    -- Create a block range index (BRIN).
    CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ); 
    CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d);
    CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d);
    -- Create a BRIN index that has a specified range.
    CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]);
  • Access geometry objects.

    -- Determine whether a spatial geometry object consists of only simple elements.
     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)
    
    
    -- Query the largest city that has traffic circles in the terrain.
    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)
  • Measure and analyze spatial data, and identify spatial relationships.

    -- Create the bc_roads table. 
    Create table bc_roads (gid serial, name varchar, the_geom geometry);
    
    -- Create the bc_municipality table.
    Create table bc_municipality(gid serial, code integer, name varchar, the_geom geometry);
    
    -- Calculate the length.
    SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;
    
    km_roads
    ------------------
    70842.1243039643
    (1 row)
    
    -- Calculate the area.
    SELECT ST_Area(the_geom)/10000 AS hectares FROM bc_municipality WHERE name = 'PRINCE GEORGE';
    
    hectares
    ------------------
    32657.9103824927
    (1 row)
    -- Use the ST_Contains function.
    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
    
    
    -- Use the ST_Covers function.
    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;
        --Result
     smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
    --------------+----------------+-------------------+---------------------
     t            | f              | t                 | f
    (1 row)    
    
    -- Use the ST_Disjoint function.
    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)
    
    -- Use the ST_Overlaps function.
    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
    
    -- Use the ST_Relate function.
    SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
    st_relate
    -----------
    t
    
    -- Use the ST_Touches function.
    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)
    
    -- Use the ST_Within function.
    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;
    --Result
     smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
    --------------+------------+------------+------------+------------+------------
     t            | t          | f          | t          | t          | t
    (1 row)
  • Delete extensions.

    Drop extension ganos_geometry cascade;

SQL reference

For more information, see the official PostGIS reference.