Skip site navigation (1) Skip section navigation (2)

Re: Setting up spatial index

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Nathaniel <naptrel(at)yahoo(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Setting up spatial index
Date: 2009-06-30 09:58:51
Message-ID: 407d949e0906300258m166f8ec8v1bdd9ac1fe9f4cdb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: SMITH, Phillip (external)Date: 2009-07-01 07:18:42
Subject: UUID functions - installation
Previous:From: Serge FonvilleDate: 2009-06-30 09:36:00
Subject: Re: Problem while using createdb for the first time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group