ApsaraDB RDS：Model geometri

Ikhtisar

Model geometri sepenuhnya kompatibel dengan operasi PostGIS dan mendukung migrasi mulus dari aplikasi yang ada.

Memulai

• Buat ekstensi.

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

  Penting

  Jangan buat ekstensi GanosBase atau PostGIS di skema yang sama. Jika tidak, pesan kesalahan ERROR: table "spatial_ref_sys" is not a member of the extension being created akan muncul.

  Saat membuat ekstensi GanosBase atau PostGIS, tabel bernama spatial_ref_sys akan otomatis dibuat. Jika Anda membuat ekstensi GanosBase dan PostGIS di skema yang sama, konflik tabel dapat terjadi. Untuk menghindari hal ini, disarankan untuk membuat ekstensi GanosBase dan PostGIS di skema yang berbeda. Sebagai alternatif, Anda dapat menghapus ekstensi PostGIS sebelum membuat ekstensi GanosBase.

• Buat tabel geometri.

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

• Tambahkan batasan geometri.

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

• Impor data geometri.

  INSERT INTO roads (id, geom, road_name)
    VALUES (1,ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',3857),'Jalan Lingkar Utara');
  INSERT INTO roads (id, geom, road_name)
    VALUES (2,ST_GeomFromText('LINESTRING(189141 244158,189265 244817)',3857),'Jalan Lingkar Timur');
  INSERT INTO roads (id, geom, road_name)
    VALUES (3,ST_GeomFromText('LINESTRING(192783 228138,192612 229814)',3857),'Jalan Lingkar Selatan');
  INSERT INTO roads (id, geom, road_name)
    VALUES (4,ST_GeomFromText('LINESTRING(189412 252431,189631 259122)',3857),'Jalan Lingkar Barat');
  INSERT INTO roads (id, geom, road_name)
    VALUES (5,ST_GeomFromText('LINESTRING(190131 224148,190871 228134)',3857),'Jalan Chang'an Timur');
  INSERT INTO roads (id, geom, road_name)
    VALUES (6,ST_GeomFromText('LINESTRING(198231 263418,198213 268322)',3857),'Jalan Chang'an Barat');

• Kueri informasi objek geometri.

  SELECT id, ST_AsText(geom) AS geom, road_name FROM roads;
  
  --------------------------------
       id | geom                                    | road_name
  --------+-----------------------------------------+-----------
        1 | LINESTRING(191232 243118,191108 243242) | Jalan Lingkar Utara
        2 | LINESTRING(189141 244158,189265 244817) | Jalan Lingkar Timur
        3 | LINESTRING(192783 228138,192612 229814) | Jalan Lingkar Selatan
        4 | LINESTRING(189412 252431,189631 259122) | Jalan Lingkar Barat
        5 | LINESTRING(190131 224148,190871 228134) | Jalan Chang'an Timur
        6 | LINESTRING(198231 263418,198213 268322) | Jalan Chang'an Barat
  (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 indeks rentang blok (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 geometri.

  -- Tentukan apakah objek geometri spasial hanya terdiri dari 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 di medan.
  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;
      --Hasil
   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;
  --Hasil
   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.