Обсуждение: Setting up spatial index
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 following2 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. If so, how do I define the correct index type, preferably in a manour which is supported by both postgres versions 7.4 and8.* I've played with PostGIS a couple of years ago, but, for the sake of installation simplicity, I'd really prefer to avoid it, and I don't require any of the fancier geometry types, coordinate conversion orGIS functionality. And (back then at least) PostGIS didn't provide any inbuilt 'find nearest' capability (probably becauseit would be very tricky to implement efficiently if the geometries use ellipsoidal coordinates), although it's easyto do with a no-frills r-tree. Nathaniel Send instant messages to your online friends http://uk.messenger.yahoo.com
On Tue, Jun 30, 2009 at 9:57 AM, Nathaniel<naptrel@yahoo.co.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 following2 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