Postgres may not be portable than SQLite. Postgres has geometric data type and functions, and if you take begin and end for a box of no height on the x axis, Postgres's box operator will work. For exmaple, Begin=100 and End=200 record is coordinate of ((100,0),(200,0)).

http://www.postgresql.org/docs/8.2/static/functions-geometry.html

Your sample data of 5301598 records took me an hour to load into default installation of Postgres, but it gives me very quick response.

###SQL### 
drop table chromosomes;
create table chromosomes(
        score   integer
        ,div            numeric(6,1)
        ,del            numeric(6,1)
        ,ins            numeric(6,1)
        ,q_sequence     text
        --,begin1               integer
        --,end1         integer
        ,pos    box
        ,left_paren     text
        ,repeat1                text
        ,repeat2                text
        ,class_family   text
        ,begin2         text
        ,end2           text
        ,left_paren2   text
        ,ID             integer
);

create index idx_chromosomes_1 on chromosomes(q_sequence);
create index idx_chromosomes_2 on chromosomes using gist(pos);

[download]
###PERL### 
use strict;
use warnings;
use DBI;

my($dbh);

system('date');
$dbh = DBI->connect('dbi:Pg:dbname=test1','','',{AutoCommit=>0})
                        or die $dbh->errstr;

#populate($dbh);
select_test($dbh);

$dbh->disconnect;

system('date');

exit 0;

sub populate{

        my($dbh)=@_;
        my($sth,$sql,$cnt);
        $cnt=0;

        $sql="insert into chromosomes values (?,?,?,?,?,?,?,?,?,?,?,?,
+?,?)";
        $sth=$dbh->prepare($sql) or die $dbh->errstr;
        eval{
                open my $fh , "<", "hg19.fa.out" or die $!;
                while(my $line=<$fh>){
                        $cnt++;
                        next if $cnt <= 3;

                        chomp $line;
                        $line =~ s/^\s+//;
                        my @vals=split(/\s+/, $line);
                        #begin and end to box type
                        $vals[5]="(($vals[5],0),($vals[6],0))";
                        splice(@vals,6,1);

                        $sth->execute(@vals);
                }
                close $fh;
                $sth->finish;
        };
        if($@){
                $dbh->rollback;
        }else {
                $dbh->commit;
        }
}
sub select_test{
        my($dbh)=@_;
        my($sth,$sql);

        #SQL for overlaps
        #$sql="select * from chromosomes where pos && box '((10000,0),
+(20000,0))'";
        #SQL for include
        $sql="select * from chromosomes where pos @ box '((10000,0),(2
+0000,0))'";

        $sth=$dbh->prepare($sql) or die $dbh->errstr;
        $sth->execute  or die $dbh->errstr;
        while(my $href= $sth->fetchrow_hashref){
                print $href->{pos} . "\n";
        }
        $sth->finish;
}

[download]

In reply to Re^2: Reducing memory footprint when doing a lookup of millions of coordinates by remiah
in thread Reducing memory footprint when doing a lookup of millions of coordinates by richardwfrancis

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.