SPATIAL空间索引

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

1. 内联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" CIRCLE 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");

2. 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" CIRCLE 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");

3. 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" CIRCLE 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");