[postgis-devel] temporal indexing

[Chris Hodgson]

In order to build the cube index you will need to add an additional 
column of type 'cube' which is essentially an aggregate of the geometry 
and the date/time columns. If your space and time data is stored in 
separate tables, then it will be difficult if not impossible to build an 
optimal spatio-temporal index. However, depending on your queries it may 
be more optimal to filter one table on the spatial index, and the other 
on a B-tree index on the date, and then do a slow non-indexed join... it 
just depends on the relative selectivities of your time and space 
filters and the overall number of results returned from the query.

Chris

Elena Camossi wrote:
> In fact my first intention was to evaluate the performances of the two 
> different cases: querying time and space with separate index, and then 
> with a unique spatio-temporal index.
> Do spatio-temporal queries, in this second case, can be performed even 
> if the spatial and temporal query data are stored as separate column? Or 
> is this cube type a special data type with a related index type?
> In my spatio-temporal schema are even defined in different tables, that 
> have to be joined when executing the access query.
> 
> Best regards,
> -Elena
> 
> On 8/7/07, * Chris Hodgson* <chodgson at refractions.net 
> <mailto:chodgson at refractions.net>> wrote:
> 
>     You didn't mention if you would be doing spatio-temporal queries, ie.
>     querying for objects which existed within a certain region of time and
>     space. For these types of queries, the cube type and index which is
>     provided in the postgres-contrib package is quite effective. You can
>     store your x,y coordinates in the first two dimensions of the cube,and
>     the date/time in the third dimension. The gist-based cube indexes are
>     are very fast, but do consume a lot of disk space.
> 
>     If you're only querying based on date then a standard b-tree index on
>     the date itself should provide reasonable performance.
> 
>     Chris
> 
>     Elena Camossi wrote:
>      > Hi list,
>      >
>      > I'm designing a spatio-temporal schema with support for coverage and
>      > temporal data in postgresql by using postgis spatial extension. The
>      > temporal support so far is quite limited,  just timestamps which
>     state
>      > the time at which data have been collected. Anyway, the temporal
>      > information is extensively used to access data, so the corresponding
>      > fields should require an index.
>      >
>      > The scientific literature states that R-Tree perform quite well with
>      > this kind of data, but there exist other indexes that should
>     perform better.
>      > Does anyone in the list has some previous experience on using GiST
>      > postgres implementation for indexing postgresql temporal columns?
>     Does
>      > anyone extend the GiST operator classes with specific support for
>      > temporal data?
>      >
>      > Thank you for helping.
>      >
>      > Best regards,
>      > -Elena
>      >
>      >
>      >
>     ------------------------------------------------------------------------
>      >
>      > _______________________________________________
>      > postgis-devel mailing list
>      > postgis-devel at postgis.refractions.net
>     <mailto:postgis-devel at postgis.refractions.net>
>      > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> 
>     _______________________________________________
>     postgis-devel mailing list
>     postgis-devel at postgis.refractions.net
>     <mailto:postgis-devel at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-devel
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel