From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | Adam Jensen <hanzer(at)riseup(dot)net> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: interval origami |
Date: | 2018-12-02 01:23:17 |
Message-ID: | CAJexoSK1kkyp_mkW0i+7jCPZvXCTO8mfjap_8LmaN8wndOLpVQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sat, Dec 1, 2018, 12:38 PM Adam Jensen <hanzer(at)riseup(dot)net wrote:
> On 12/1/18 8:24 AM, Joe Conway wrote:
> > Perhaps overkill, but if you represent your timeline as actual line
> > segments, perhaps PostGIS would be useful. E.g.:
> >
> > https://postgis.net/docs/manual-2.5/ST_Difference.html
>
> That's an interesting notion. Thanks, Joe!
>
> I think I learned enough about plpgsql programming last night to write
> the three basic functions that each operate on two time intervals:
>
> 1. determine if two intervals overlap
> 2. merge two overlapping intervals into one interval
> 3. given two overlapping intervals, produce the difference interval or
> interval set
>
> My thinking here is that since these functions seem to be
> computationally simple, it might be more convenient for exploration,
> development, distribution and maintenance if there is some uniformity in
> their style and control over their implementation and behavior.
>
> Currently, I am thinking about how these functions might be used to
> solve the problem. Since I am not familiar with the capabilities of SQL,
> my tendency is to think in terms of a function that iterates over the
> data set multiple times and eventually converges to produce the solution
> set. I am concerned that this might be a very goofy way to solve the
> problem in a relational database.
>
If you use postgis then you get all the fundamental primitives and
(critically) indexing that is virtually guaranteed to function well. I used
postgis awhile back to solve a data problem that was based in abstract
dimensions and it worked incredibly well. I'm not an expert in the kind of
extension programming you're attempting but I'd be worried that it's easy
to make a mistake.
Postgis is going to give you maybe more guardrails to keep your work within
safe design boundaries..
Just two cents from the gallery,
Steve
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kirti Pathak | 2018-12-06 23:44:34 | After dump of 4.2 GB SQL file Equal to hard disk space??? |
Previous Message | Adam Jensen | 2018-12-01 20:37:46 | Re: interval origami |