ApsaraDB RDS:Model Geometry

Ikhtisar

Model geometry sepenuhnya kompatibel dengan operasi PostGIS dan memungkinkan migrasi aplikasi yang sudah ada secara lancar.

Memulai

• Buat ekstensi.

  -- Buat ekstensi geometry.
  Create extension ganos_geometry with schema public cascade;

  Penting

  Jangan membuat ekstensi GanosBase atau ekstensi PostGIS dalam skema yang sama. Jika tidak, pesan error ERROR: table "spatial_ref_sys" is not a member of the extension being created akan ditampilkan.

  Saat Anda membuat ekstensi GanosBase atau PostGIS, tabel bernama spatial_ref_sys akan dibuat secara otomatis. Jika Anda membuat ekstensi GanosBase dan ekstensi PostGIS dalam skema yang sama, terjadi konflik tabel. Kami menyarankan agar Anda membuat ekstensi GanosBase dan PostGIS di skema yang berbeda. Anda dapat menghapus ekstensi PostGIS sebelum membuat ekstensi GanosBase.

• Buat tabel geometry.

  -- Metode 1: Buat tabel yang berisi bidang geometry.
  CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25), geom geometry(LINESTRING,3857) );
  
  -- Metode 2: Buat tabel biasa lalu tambahkan bidang geometry ke tabel tersebut.
  CREATE TABLE ROADS (ID int4, ROAD_NAME varchar(25));
  SELECT AddGeometryColumn( 'roads', 'geom', 3857, 'LINESTRING', 2);

• Tambahkan constraint geometry.

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

• Impor data geometry.

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

• Kueri informasi objek geometry.

  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)

• Buat indeks.

  -- Buat indeks GiST.
  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)];
  
  -- Contoh:
  Create INDEX sp_geom_index ON ROADS USING GIST(geom);
  VACUUM ANALYZE ROADS (geom);
  
  -- Buat 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);
  -- Buat indeks BRIN dengan rentang tertentu.
  CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]);

• Akses objek geometry.

  -- Tentukan apakah objek geometri spasial hanya terdiri dari elemen-elemen sederhana.
   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)
  
  
  -- Kueri kota terbesar yang memiliki bundaran lalu lintas di wilayah tersebut.
  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)

• Ukur dan analisis data spasial, serta identifikasi hubungan spasial.

  -- Buat tabel bc_roads. 
  Create table bc_roads (gid serial, name varchar, the_geom geometry);
  
  -- Buat tabel bc_municipality.
  Create table bc_municipality(gid serial, code integer, name varchar, the_geom geometry);
  
  -- Hitung panjang.
  SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;
  
  km_roads
  ------------------
  70842.1243039643
  (1 row)
  
  -- Hitung luas.
  SELECT ST_Area(the_geom)/10000 AS hectares FROM bc_municipality WHERE name = 'PRINCE GEORGE';
  
  hectares
  ------------------
  32657.9103824927
  (1 row)
  -- Gunakan fungsi 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;
  
  name                        | roads_km
  ----------------------------+------------------
  SURREY                      | 1539.47553551242
  VANCOUVER                   | 1450.33093486576
  LANGLEY DISTRICT            | 833.793392535662
  BURNABY                     | 773.769091404338
  PRINCE GEORGE               | 694.37554369147
  
  
  -- Gunakan fungsi 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;
      --Result
   smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
  --------------+----------------+-------------------+---------------------
   t            | f              | t                 | f
  (1 row)    
  
  -- Gunakan fungsi ST_Disjoint.
  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)
  
  -- Gunakan fungsi 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
  
  -- Gunakan fungsi ST_Relate.
  SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
  st_relate
  -----------
  t
  
  -- Gunakan fungsi ST_Touches.
  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)
  
  -- Gunakan fungsi 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;
  --Result
   smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
  --------------+------------+------------+------------+------------+------------
   t            | t          | f          | t          | t          | t
  (1 row)

• Hapus ekstensi.

  Drop extension ganos_geometry cascade;

Referensi SQL

Untuk informasi lebih lanjut, lihat referensi resmi PostGIS.