Index: Makefile.PL
===================================================================
--- Makefile.PL (revision 13694)
+++ Makefile.PL (working copy)
@@ -219,7 +219,7 @@
# This change MAY POSSIBLY BREAK OLD APPLICATIONS THAT ALREADY
# USED FTS3 ... but sooner or later that change had to be done !
'-DSQLITE_ENABLE_FTS3_PARENTHESIS', # for sqlite >= 3.6.10
-
+ '-DSQLITE_ENABLE_RTREE', # for sqlite >= 3.6.10
'-DSQLITE_ENABLE_COLUMN_METADATA',
'-DNDEBUG=1',
);
Index: t/44_rtree.t
===================================================================
--- t/44_rtree.t (revision 0)
+++ t/44_rtree.t (revision 0)
@@ -0,0 +1,101 @@
+#!/usr/bin/perl
+
+use strict;
+BEGIN {
+ $| = 1;
+ $^W = 1;
+}
+
+use t::lib::Test;
+use Test::More;
+use DBD::SQLite;
+use Data::Dumper;
+
+my @coords = (
+ # id, minX, maxX, minY, maxY
+ [1, 1, 200, 1, 200], # outside bounding box
+ [2, 25, 100, 25, 50],
+ [3, 50, 125, 40, 150],
+ [4, 25, 200, 125, 125], # hor. line
+ [5, 100, 100, 75, 175], # vert. line
+ [6, 100, 100, 75, 75], # point
+ [7, 150, 175, 150, 175]
+);
+
+my @test_regions = (
+ # minX, maxX, minY, maxY
+ [75, 75, 45, 45], # query point
+ [10, 140, 10, 175], # ... box
+ [30, 100, 75, 75] # ... hor. line
+);
+
+my @test_results = (
+ # results for contains tests (what does this region contain?)
+ [],
+ [2, 3, 5, 6],
+ [6],
+
+ # results for overlaps tests (what does this region overlap with?)
+ [1..3],
+ [1..6],
+ [1, 3, 5, 6]
+);
+
+BEGIN {
+ if (!grep /ENABLE_RTREE/, DBD::SQLite::compile_options()) {
+ plan skip_all => 'RTREE is disabled for this DBD::SQLite';
+ }
+}
+use Test::NoWarnings;
+
+plan tests => @coords + (2 * @test_regions) + 4;
+
+# connect
+my $dbh = connect_ok( RaiseError => 1 );
+
+# TODO: test rtree and rtree_i32 tables
+
+# create R* Tree table
+$dbh->do(<<"") or die DBI::errstr;
+ CREATE VIRTUAL TABLE try_rtree
+ USING rtree(id, minX, maxX, minY, maxY);
+
+# populate it
+my $insert_sth = $dbh->prepare(<<"") or die DBI::errstr;
+INSERT INTO try_rtree VALUES (?,?,?,?,?)
+
+for my $coord (@coords) {
+ ok $insert_sth->execute(@$coord);
+}
+
+# find by primary key
+my $sql = "SELECT * FROM try_rtree WHERE id = ?";
+
+my $idx = 0;
+for my $id (1..2) {
+ my $results = $dbh->selectrow_arrayref($sql, undef, $id);
+ is_deeply($results, $coords[$idx], "Coords for $id match");
+ $idx++;
+}
+
+# find contained regions
+my $contained_sql = <<"";
+SELECT id FROM try_rtree
+ WHERE minX >= ? AND maxX <= ?
+ AND minY >= ? AND maxY <= ?
+
+for my $region (@test_regions) {
+ my $results = $dbh->selectcol_arrayref($contained_sql, undef, @$region);
+ is_deeply($results, shift @test_results);
+}
+
+# find overlapping regions
+my $overlap_sql = <<"";
+SELECT id FROM try_rtree
+ WHERE maxX >= ? AND minX <= ?
+ AND maxY >= ? AND minY <= ?
+
+for my $region (@test_regions) {
+ my $results = $dbh->selectcol_arrayref($overlap_sql, undef, @$region);
+ is_deeply($results, shift @test_results);
+}
Index: lib/DBD/SQLite.pm
===================================================================
--- lib/DBD/SQLite.pm (revision 13694)
+++ lib/DBD/SQLite.pm (working copy)
@@ -1890,7 +1890,45 @@
that space can sometimes be spared --- see the tip in the
L<Cookbook|DBD::SQLite::Cookbook/"Sparing database disk space">.
+=head1 R* TREE SUPPORT
+The RTREE extension module within SQLite adds support for creating
+a R-Tree, a special index for range and multidimensional queries. This
+allows users to create tables that can be loaded with (as an example)
+geospatial data such as latitude/longitude coordinates for buildings within
+a city :
+
+ CREATE VIRTUAL TABLE city_buildings USING rtree(
+ id, -- Integer primary key
+ minLong, maxLong, -- Minimum and maximum longitude
+ minLat, maxLat -- Minimum and maximum latitude
+ );
+
+then query which buildings overlap or are contained within a specified region:
+
+ # IDs that are contained within query coordinates
+ my $contained_sql = <<"";
+ SELECT id FROM try_rtree
+ WHERE minLong >= ? AND maxLong <= ?
+ AND minLat >= ? AND maxLat <= ?
+
+ # ... and those that overlap query coordinates
+ my $overlap_sql = <<"";
+ SELECT id FROM try_rtree
+ WHERE maxLong >= ? AND minLong <= ?
+ AND maxLat >= ? AND minLat <= ?
+
+ my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
+ $minLong, $maxLong, $minLat, $maxLat);
+
+ my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
+ $minLong, $maxLong, $minLat, $maxLat);
+
+For more detail, please see the SQLite R-Tree page
+(L<
http://www.sqlite.org/rtree.html>). Note that custom R-Tree
+queries using callbacks, as mentioned in the prior link, have not been
+implemented yet.
+
=head1 FOR DBD::SQLITE EXTENSION AUTHORS
Since 1.30_01, you can retrieve the bundled sqlite C source and/or
@@ -1947,6 +1985,13 @@
Support the full API of sqlite3_open_v2 (flags for opening the file).
+=head2 Support for custom callbacks for R-Tree queries
+
+Custom queries of a R-Tree index using a callback are possible with
+the SQLite C API (L<
http://www.sqlite.org/rtree.html>), so one could
+potentially use a callback that narrowed the result set down based
+on a specific need, such as querying for overlapping circles.
+
=head1 SUPPORT
Bugs should be reported via the CPAN bug tracker at
