On Wed, Sep 19, 2012 at 6:27 PM, Adam Wells <a...@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@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Reply via email to