[postgis-users] How efficient are spatial indexes?

[Jim Mlodgenski]

On Wed, Sep 19, 2012 at 6:27 PM, Adam Wells <adam at alandstreet.com> wrote:
> Hi All,
>
> I am in the process of writing an application that involves keeping track of a large number of calendar appointments - things that have start and end dates.  It is important to make sure that appointments for a given person do not overlap, which will involve lots of date comparisons in db queries.

If you can use 9.2, the new Range Type feature does what you are looking for.
http://www.postgresql.org/docs/9.2/interactive/rangetypes.html

>
> Now I might be smoking crack, but I had the following idea, and would value feedback:
>
> I could represent an appointment as a line in a geometry column - the X axis would be time (as integer values), and the Y axis would be the ID of the user.
>
> user id      222
> start time 1348095600000 (09/20/2012 09:00:00)
> end time 1348099200000 (09/20/2012 10:00:00)
>
> The WKT for the geometry would be LINESTRING(1348095600000 222, 1348099200000 222)
>
> If all appointments had such geometries stored, then I could use spatial queries to find all appointments within a given range by looking for ST_Overlaps etc.
>
> Is the spatial index efficient at this sort of thing?  More so than doing date range checking in a traditional index?
>
> Is this a fundamentally lame approach, or it it worth going to the trouble of benchmarking?
>
> Cheers,
>
> Adam
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users