[postgis-users] How efficient are spatial indexes?

[Adam Wells]

Thanks Jim - that is exactly what I need, I was not aware of that as a datatype.

On 20/09/2012, at 9:08 AM, Jim Mlodgenski <jimmy76 at gmail.com> wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users