Using an R-tree index
To demonstrate the use of an R-tree index in RDM we will use a simplistic example table containing data about the location of zip codes in United States Cities. The table will be defined as follows:
CREATE TABLE pointsofinterest
(
zip INT32 KEY NOT NULL,
city CHAR(29) KEY NOT NULL,
state CHAR(2) NOT NULL,
location DOUBLE ARRAY[4] KEY USING R_TREE NOT NULL
);
The location column will be a longitude and latitude point for the zip code. Since it is a point the maximum and minimum values for each dimension will be the same. We will populate the table with the following rows:
|
Zip Code |
City |
State |
Latitude |
Longitude |
|---|---|---|---|---|
|
83686 |
Nampa |
ID |
43.529812 |
-116.577610 |
|
83687 |
Nampa |
ID |
43.590212 |
-116.537810 |
|
83701 |
Boise |
ID |
43.603768 |
-116.272921 |
|
83702 |
Boise |
ID |
43.627734 |
-116.207560 |
|
83703 |
Boise |
ID |
43.668396 |
-116.257070 |
|
83704 |
Boise |
ID |
43.631230 |
-116.287160 |
|
83705 |
Boise |
ID |
43.583139 |
-116.225200 |
|
83706 |
Boise |
ID |
43.593523 |
-116.199030 |
|
83707 |
Boise |
ID |
43.384690 |
-115.997118 |
|
83708 |
Boise |
ID |
43.459855 |
-116.243984 |
|
83709 |
Boise |
ID |
43.572671 |
-116.295270 |
|
83711 |
Boise |
ID |
43.459855 |
-116.243984 |
|
83712 |
Boise |
ID |
43.595763 |
-116.163670 |
|
83713 |
Boise |
ID |
43.638314 |
-116.330590 |
|
83714 |
Boise |
ID |
43.641774 |
-116.265070 |
|
83715 |
Boise |
ID |
43.459855 |
-116.243984 |
|
83716 |
Boise |
ID |
43.601772 |
-116.027210 |
|
83717 |
Boise |
ID |
43.459855 |
-116.243984 |
|
83719 |
Boise |
ID |
43.459855 |
-116.243984 |
|
83720 |
Boise |
ID |
43.459855 |
-116.243984 |
|
98060 |
Seattle |
WA |
47.432251 |
-121.803388 |
|
98061 |
Rollingbay |
WA |
47.680763 |
-122.576199 |
|
98062 |
Seahurst |
WA |
47.432251 |
-121.803388 |
|
98063 |
Federal Way |
WA |
47.432251 |
-121.803388 |
|
98064 |
Kent |
WA |
47.432251 |
-121.803388 |
|
98065 |
Snoqualmie |
WA |
47.530639 |
-121.830530 |
|
98067 |
Bellevue |
WA |
47.617634 |
-122.140193 |
|
98068 |
Snoqualmie Pass |
WA |
47.397811 |
-121.396030 |
|
98070 |
Vashon |
WA |
47.416198 |
-122.468210 |
|
98071 |
Auburn |
WA |
47.432251 |
-121.803388 |
|
98072 |
Woodinville |
WA |
47.758786 |
-122.102050 |
|
98073 |
Redmond |
WA |
47.432251 |
-121.803388 |
|
98074 |
Sammamish |
WA |
47.625364 |
-122.045347 |
|
98075 |
Sammamish |
WA |
47.584879 |
-122.033685 |
|
98082 |
Bothell |
WA |
47.432251 |
-121.803388 |
|
98083 |
Kirkland |
WA |
47.432251 |
-121.803388 |
|
98092 |
Auburn |
WA |
47.297285 |
-122.163280 |
|
98093 |
Federal Way |
WA |
47.311041 |
-122.113791 |
|
98101 |
Seattle |
WA |
47.610670 |
-122.334380 |
|
98102 |
Seattle |
WA |
47.632870 |
-122.322530 |