Clustering spatial data on the disk
About this task
To cluster existing spatial data on the disk to reflect the ordering in an R-tree index:
Procedure
- Create a new table that is exactly the same as the original
table and insert all rows from the original table into the new table.
For example, if the original table is called circle_tab, the following SQL statements create an exact copy called circle_tab_temp and insert all rows from the circle_tab table into the circle_tab_temp table:
CREATE TABLE circle_tab_temp ( id INTEGER, circles MyCircle ); INSERT INTO circle_tab_temp SELECT * FROM circle_tab;
- Create an R-tree index on the relevant column of the new table.
- Update statistics on the new table.
- Drop the R-tree index on the original table and delete all rows.
- Insert all rows from the new table
back into the original table with a SELECT statement that returns
all rows in the new table and uses the R-tree index at the same time.
Be careful that you design this SELECT statement carefully so it satisfies both restrictions.
You might consider using the Overlap strategy function in your query, passing as the second parameter the coordinates of the entire space in which the spatial objects in the table exist. Because each spatial object obviously overlaps with the entire possible space, the query returns every row in the table. In addition, because the Overlap strategy function is specified in the WHERE clause of the query, the query must use the R-tree index.
For example, assume all the spatial objects in the table exist within a box defined by the coordinates(-1000,-1000,1000,1000)
. In this case, the query might look like the following example:INSERT INTO circle_tab SELECT * FROM circle_tab_temp WHERE Overlap(circles, 'box(-1000,-1000,1000,1000)::MyBox');
- Create a new R-tree index on the appropriate column of the original table.
- Drop the new table.
What to do next
If your original table is fragmented, be sure to use the same fragmentation scheme throughout the procedure. In other words, fragment the new table and its index the same way the original table and index are fragmented and make sure that the data is re-inserted into the correct fragment of the original table.
Subsequent updates will gradually degrade the clustering of data achieved with this procedure.