[postgis-users] Linestring and tsrange

[Regina Obe]

Nathan,

 

I think thinking about geometries as just representing physical space is a bit short-sighted.   

There are so many things in this world that lend themselves to a spatial representation that aren't physical or at least not physical in the way most people think about physical.

 

We use linestrings for representing date and  time ranges where we have discontinuity and need to represent as a single unit without having to resort to storing as arrays.

The built in functions for ranges in PostgreSQL are much less powerful than what PostGIS offers for lines and by extension time lines.

 

For example if you have a time line where you have   2017-04-01 10:00 AM – 2017-04-01 11:00 AM   and then a break that then continues 2017-04-05 10 PM – 2017-04-05 11 PM

It's computationally easier to store as a multilinestring than resort to using tsrange arrays.  

 

We've found it much more useful to use PostGIS to represent these than the built in PostgreSQL temporal ranges. We usually use with dateranges, but should work just as well with tsrange.

 

PostGIS offers a ton of functions you can use on lines such as the aggregation and intersection functions you can use for aggregating and breaking timelines.  There is no equivalent of such things in PostgreSQL for range types, though it would be nice if there was.

 

This is particularly handy in healthcare when computing things like length of stay of a patient in a hospital or in finance stock market fluctuations in a compact way.

 

We have slides and a video that describes the approach:

 

http://postgis.us/presentations/PGConfUS2017_PostGISTop10.html#/9

 

Video:

https://youtu.be/6yTFkLMOY_s?t=1115

 

 

Thanks,

Regina

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Darafei "Kom?pa" Praliaskouski
Sent: Tuesday, May 23, 2017 4:27 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Linestring and tsrange

 

Hi, 
Please try not to break abstractions when designing something that would live more than an hour. 
Linestring is for geometry (with at least two dimensions), tsrange is for 1D temporal intervals. 

If you lack any functionality you gain by breaking abstraction better implement it directly. 

 

On аў, 23 мая 2017, 06.12 Nathan Lai <nathanwh71 at gmail.com <mailto:nathanwh71 at gmail.com> > wrote:

Hi all,

    Both linestring (using just X axis) and tsrange can represent time ranges. When is linestring more preferrable than tsrange?

    Thanks!

Nathan 

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170524/eb2136dc/attachment.html>