#!/usr/bin/perl
use DBI;
use warnings;
use strict;

my $dbname = shift or die;
my ($dbh, $stm);

print STDERR "Creating Tables ...\n";
$dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "") or die $!;
$dbh->do(<<EOSQL) or die $!;
DROP TABLE IF EXISTS points;
DROP TABLE IF EXISTS boxes;
CREATE TABLE points(
  pointpoint point,
  pointbox   box
);
CREATE TABLE boxes(
  boxid int4,
  bound box
);
EOSQL

print STDERR "Creating Points ...\n";
for( my $i=0; $i<1000; $i++ ) {
  my $x = rand(1.0);
  my $y = rand(1.0);
  $dbh->do(<<EOSQL) or die $!;
INSERT INTO points VALUES ( 
  Point('$x,$y'),
  Box(Point('$x,$y'),Point('$x,$y'))
);
EOSQL
}

print STDERR "Creating Boxes ...\n";
for( my $i=0; $i<1000; $i++ ) {
  my $w = rand(0.90);
  my $h = rand(0.90);
  my $x = rand(0.05)+0.5;
  my $y = rand(0.05)+0.5;
  my $xmin = $x;
  my $xmax = $x+$w;
  my $ymin = $y;
  my $ymax = $y+$h;
  $dbh->do(<<EOSQL) or die $!;
INSERT INTO boxes VALUES ( 
  $i,
  Box(Point('$xmin,$ymin'),Point('$xmax,$ymax'))
);
EOSQL
}

print STDERR "Creating Index ...\n";
$dbh->do(<<EOSQL) or die $!;
CREATE INDEX boxes_bound ON boxes USING GiST (bound);
EOSQL

print STDERR "Analyse Boxes ...\n";
$dbh->do(<<EOSQL) or die $!;
VACUUM boxes;
EOSQL

print STDERR "Analyse Boxes ...\n";
$dbh->do(<<EOSQL) or die $!;
ANALYSE boxes;
EOSQL

print STDERR "Query 1 ...\n";
$stm = $dbh->prepare(<<EOSQL) or die $!;
set enable_seqscan = off;
SELECT
  pointpoint[0] as x,
  pointpoint[1] as y,
  boxid
FROM
  points AS p,
  boxes  AS b
WHERE 
  bound @> pointbox
EOSQL

$stm->execute() or die $!;
open FH, "| sort > a.out";
while( my $ref = $stm->fetchrow_hashref() ) {
  my $x  = $ref->{x};
  my $y  = $ref->{y};
  my $id = $ref->{boxid};
  print FH "$x,$y $id\n";
}
close FH;

print STDERR "Query 2 ...\n";
$stm = $dbh->prepare(<<EOSQL) or die $!;
set enable_seqscan = off;  
SELECT
  pointpoint[0] as x,
  pointpoint[1] as y,
  boxid
FROM
  points AS p,
  boxes  AS b
WHERE 
  bound @> pointpoint
EOSQL

$stm->execute() or die $!;
open FH, "| sort > b.out";
while( my $ref = $stm->fetchrow_hashref() ) {
  my $x = $ref->{x};
  my $y = $ref->{y};
  my $id = $ref->{boxid};
  print FH "$x,$y $id\n";
}
close FH;

system( "tkdiff a.out b.out >/dev/null 2>&1" );