[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Setting up spatial index
From:       Nathaniel <naptrel () yahoo ! co ! uk>
Date:       2009-06-30 11:24:07
Message-ID: 107724.69156.qm () web25006 ! mail ! ukl ! yahoo ! com
[Download RAW message or body]


Dear postgres users,

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 query 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 and 8.*? The 7.4 manual suggests postgres can \
do an R-tree index, but 8.4's suggests that this kind of index has been subsumed \
within the GiST framework. Either way, I don't really know where to start.

I've played with PostGIS a couple of years ago, but, for the sake of installation \
simplicity, I'd really prefer to avoid it--I don't require any of the fancier
geometry types, coordinate conversion or GIS functionality. And (back
then, at least) PostGIS didn't provide any inbuilt 'find nearest'
capability (probably because it would be very tricky to implement
efficiently if the geometries use ellipsoidal coordinates), although queries like \
(2), above, are easy to do quickly when you have an R-tree index of points in \
cartesian coordinates.

Nathaniel





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]