On Oct 31, 2006, at 8:29 PM, Tom Lane wrote:
John Major <major@xxxxxxxxxxxxxx> writes:
My problem is, I often need to execute searches of tables like these
which find "All features within a range".
Ie: select FeatureID from SIMPLE_TABLE where
FeatureChromosomeName like
'chrX' and StartPosition > 1000500 and EndPosition < 2000000;
A standard btree index is just going to suck for these types of
queries;
you need something that's actually designed for spatial range queries.
You might look at the contrib/seg module --- if you can store your
ranges as "seg" datatype then the seg overlap operator expresses what
you need to do, and searches on an overlap operator can be handled
well
by a GIST index.
Also, there's the PostGIS stuff, though it might be overkill for what
you want.
Another possibility (think Tom has suggested in the past) is to
define Start and End as a box, and then use the geometric functions
built into plain PostgreSQL (though perhaps that's what he meant by
"PostGIS stuff").
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
