ApsaraDB RDS:Geometri modeli

Ganos

Ganos

Ganos

Genel Bakış

Geometri modeli, PostGIS işlemleriyle tamamen uyumludur ve mevcut uygulamalarınızı sorunsuz bir şekilde geçirmenize olanak tanır.

Başlangıç

• Eklentileri oluşturun.

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

  Önemli

  GanosBase veya PostGIS eklentilerini aynı şemada oluşturmayın. Aksi takdirde ERROR: table "spatial_ref_sys" is not a member of the extension being created hata mesajı görüntülenir.

  Bir GanosBase veya PostGIS eklentisi oluşturduğunuzda, spatial_ref_sys adlı bir tablo otomatik olarak oluşturulur. Aynı şemada hem GanosBase hem de PostGIS eklentisi oluşturursanız tablo çakışması oluşur. GanosBase ve PostGIS eklentilerini farklı şemalarda oluşturmanızı öneririz. Bir GanosBase eklentisi oluşturmadan önce PostGIS eklentisini silebilirsiniz.

• Bir geometri tablosu oluşturun.

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

• Geometri kısıtlamaları ekleyin.

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

• Geometri verilerini içeri aktarın.

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

• Geometri nesne bilgilerini sorgulayın.

  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)

• Dizinler oluşturun.

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

• Geometri nesnelerine erişin.

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

• Uzamsal verileri ölçün ve analiz edin; uzamsal ilişkileri belirleyin.

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

• Eklentileri silin.

  Drop extension ganos_geometry cascade;

SQL referansı

Daha fazla bilgi için resmi PostGIS referansını inceleyin.