Re: Setting up spatial index

On Tue, Jun 30, 2009 at 9:57 AM, Nathaniel<naptrel(at)yahoo(dot)co(dot)uk> wrote:
> I would like to set up a table that contains a 3D position, a time and a measured value, e.g. columns: x, y, z, t, val.
>
> Does bog-standard postgres (i.e. no PostGIS extension, if possible) provide a way to index this table to allow the following 2 types of queries to be performed efficiently?
>
> 1. Select all the measurements in a given spatio-temporal box.
> 2. Select the N points nearest (in the euclidean/pythagorean sense) to a specified point.

Honestly I would suggest you repeat your question on pgsql-general.
The number of people really aware of what you can do with GIST indexes
is relatively small and if you miss the right person you might not get
an answer.

I know you do (1) for the spatial coordinates. I don't think you can
combine the two into any kind of r-tree like index except as a
two-column index where one column is matched first. You might have
success with two separate indexes if the system can do a bitmap and
between them.

Alternately you could have a GIST index of the fourtuple
x,y,z,t::abstime or some various of that to turn the timestamp into
yet another coordinate of the same data type.

You might want to look into the cube contrib module which might be a
better match than the builtin GIST indexable data types like box.

As far as (2) I believe the current status is that there's been some
talk of implementing it but nothing has come of it yet. I could be
wrong though, I know it's really important for the full text search.

--
greg
http://mit.edu/~gsstark/resume.pdf