Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL Syntax Migration Reference/ MySQL Syntax Migrating/ Schema Objects and Data Definition Language (DDL)/ Indexes/ Spatial Indexes
Updated on 2024-07-19 GMT+08:00
View PDF
Share

Spatial Indexes

GaussDB(DWS) does not support spatial indexes. DSC will perform adaptation based on GaussDB(DWS) features during migration.

  1. Inline spatial index

    Input

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    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)
);

    Output

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
    		 
    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");
  2. Spatial index created by ALTER TABLE

    Input

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    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;

    Output

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    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");
  3. Spatial index created by CREATE INDEX

    Input

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    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);

    Output

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    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");
Parent topic: Indexes