数据仓库服务 GAUSSDB(DWS)-SPATIAL空间索引

时间：2024-12-10 11:29:03

数据仓库服务 GAUSSDB(DWS) 索引

SPATIAL空间索引

GaussDB (DWS)不支持SPATIAL空间索引。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。

内联SPATIAL空间索引。

输入示例

       
        
          
          
            CREATE TABLE `public`.`test_create_table04` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`A` POINT NOT NULL,
	`B` POLYGON NOT NULL,
	`C` GEOMETRYCOLLECTION NOT NULL,
	`D` LINESTRING NOT NULL,
	`E` MULTILINESTRING NOT NULL,
	`F` MULTIPOINT NOT NULL,
	`G` MULTIPOLYGON NOT NULL,
	SPATIAL INDEX A_INDEX(A),
	SPATIAL INDEX B_INDEX(B),
	SPATIAL INDEX C_INDEX(C),
	SPATIAL KEY D_INDEX(D),
	SPATIAL KEY E_INDEX(E),
	SPATIAL KEY F_INDEX(F),
	SPATIAL INDEX G_INDEX(G)
);

           

         

       
      

输出示例

       
        
          
          
            CREATE TABLE "public"."test_create_table04"
(
  "id" SERIAL NOT NULL PRIMARY KEY,
  "a" POINT NOT NULL,
  "b" POLYGON NOT NULL,
  "c" GEOMETRYCOLLECTION NOT NULL,
  "d" POLYGON NOT NULL,
  "e" BOX NOT NULL,
  "f" BOX NOT NULL,
  "g" POLYGON NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");
CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a");
CREATE INDEX "b_index" ON "public"."test_create_table04" USING GIST ("b");
CREATE INDEX "c_index" ON "public"."test_create_table04" USING GIST ("c");
CREATE INDEX "d_index" ON "public"."test_create_table04" USING GIST ("d");
CREATE INDEX "e_index" ON "public"."test_create_table04" USING GIST ("e");
CREATE INDEX "f_index" ON "public"."test_create_table04" USING GIST ("f");
CREATE INDEX "g_index" ON "public"."test_create_table04" USING GIST ("g");

           

         

       
      

ALTER TABLE创建SPATIAL空间索引。

输入示例

       
            CREATE TABLE `public`.`test_create_table04` (
 `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `A` POINT NOT NULL,
 `B` POLYGON NOT NULL,
 `C` GEOMETRYCOLLECTION NOT NULL,
 `D` LINESTRING NOT NULL,
 `E` MULTILINESTRING NOT NULL,
 `F` MULTIPOINT NOT NULL,
 `G` MULTIPOLYGON NOT NULL
);

ALTER TABLE `test_create_table04` ADD SPATIAL INDEX A_INDEX(A);
ALTER TABLE `test_create_table04` ADD SPATIAL INDEX E_INDEX(E) USING BTREE;

输出示例

       
            CREATE TABLE "public"."test_create_table04"
(
  "id" SERIAL NOT NULL PRIMARY KEY,
  "a" POINT NOT NULL,
  "b" POLYGON NOT NULL,
  "c" GEOMETRYCOLLECTION NOT NULL,
  "d" POLYGON NOT NULL,
  "e" BOX NOT NULL,
  "f" BOX NOT NULL,
  "g" POLYGON NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");

CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a");
CREATE INDEX "e_index" ON "public"."test_create_table04" USING GIST ("e");

CREATE INDEX创建SPATIAL空间索引。

输入示例

       
            CREATE TABLE `public`.`test_create_table04` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`A` POINT NOT NULL,
	`B` POLYGON NOT NULL,
	`C` GEOMETRYCOLLECTION NOT NULL,
	`D` LINESTRING NOT NULL,
	`E` MULTILINESTRING NOT NULL,
	`F` MULTIPOINT NOT NULL,
	`G` MULTIPOLYGON NOT NULL
);

CREATE SPATIAL INDEX A_INDEX ON `test_create_table04`(A);

输出示例

       
            CREATE TABLE "public"."test_create_table04"
(
  "id" SERIAL NOT NULL PRIMARY KEY,
  "a" POINT NOT NULL,
  "b" POLYGON NOT NULL,
  "c" GEOMETRYCOLLECTION NOT NULL,
  "d" POLYGON NOT NULL,
  "e" BOX NOT NULL,
  "f" BOX NOT NULL,
  "g" POLYGON NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");

CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a");