Rtree Index

It's interesting to see partitioning of GiST tree. Using gevel contrib module it's easy task.

• Download cities_mbr.zip, which contains MBRs of Greece cities and villages (6,719 entries) from http://www.rtreeportal.org/spatial.html
• convert it to COPY format ([1])
• Install rtree_gist contrib module
• create test database, load rtree_gist and gevel

createdb rtree
psql rtree < rtree.sql
psql rtree < gevel.sql

• load data

create table cities (id int4, b box);
\copy cities from 'cities_mbr.copy' with delimiter as '|'

• create index

 rtree=# \d bix
Index "public.bix"
 Column | Type 
--------+------
 b      | box
gist, for table "public.cities"

• get statistics on index

rtree=# select gist_stat('bix');
 Number of levels:          2
Number of pages:           64
Number of leaf pages:      63
Number of tuples:          6782
Number of leaf tuples:     6719
Total size of tuples:      298408 bytes
Total size of leaf tuples: 295636 bytes
Total size of index:       524288 bytes

• select entries from index ( level 1) and save them to file

\pset tuples_only
\o cities-l-1.leaf
select * from gist_print('bix') as t(level int, a box) where level =1;

similarly, for level 2 (leaf).

• draw picture using libgd and perl interface GD.pm You need to find out coordinates (xmin,ymin) and (xmax,ymax) to normalize data. Script below expects space separated data from STDIN (x1 y1 x2 y2) and output to STDOUT gif image, so you need some work.

use GD;

my $DX = 600;
my $DY = 600;
$im = new GD::Image($DX,$DY);
$black = $im->colorAllocate(0,0,0);
$white = $im->colorAllocate(255,255,255);
$im->transparent($white);
$im->interlaced('true');
$xmin = 104063.96875;
$xmax = 1007864.0;
$dx = ($xmax - $xmin)/$DX;
$ymin = 3850990;
$ymax = 4623849.0;
$dy = ($ymax - $ymin)/$DY;
binmode STDOUT;
while (<>) {
   chomp;
  @a = split(/\s+/);
  $a[0] = ($a[0]-$xmin)/$dx;
  $a[2] = ($a[2]-$xmin)/$dx;
  $a[1] = ($a[1]-$ymin)/$dy;
  $a[3] = ($a[3]-$ymin)/$dy;
  $im->rectangle(@a,$white);
}
print $im->gif;

Final pictures:

• level 1
• leaf

Please, note, described script is just a sketch and better scripts are welcome !