ApsaraDB RDS:Modelo de geometria

Ganos

Ganos

Ganos

Visão geral

O modelo de geometria é totalmente compatível com operações do PostGIS, o que facilita a migração de aplicações existentes.

Primeiros passos

• Crie as extensões.

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

  Importante

  Não crie extensões do GanosBase ou do PostGIS no mesmo schema. Caso contrário, o sistema exibirá a mensagem de erro ERROR: table "spatial_ref_sys" is not a member of the extension being created.

  Ao criar uma extensão do GanosBase ou do PostGIS, o sistema cria automaticamente uma tabela chamada spatial_ref_sys. Se você criar uma extensão do GanosBase e outra do PostGIS no mesmo schema, ocorrerá um conflito de tabelas. Recomendamos criar as extensões do GanosBase e do PostGIS em schemas diferentes. Exclua a extensão do PostGIS antes de criar a extensão do GanosBase.

• Crie uma tabela de geometria.

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

• Adicione restrições de geometria.

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

• Importe dados de geometria.

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

• Consulte informações de objetos de geometria.

  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)

• Crie índices.

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

• Acesse objetos de geometria.

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

• Meça e analise dados espaciais e identifique relações espaciais.

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

• Exclua as extensões.

  Drop extension ganos_geometry cascade;

Referência SQL

Para mais informações, consulte a referência oficial do PostGIS.