Implement Time Types

[chaitan94]

For each of the time types defined in MobilityDB, a class that extends SQLAlchemy's UserDefinedType needs to be defined, similar to how we already defined temporal types like TFloat.

These include:

• [x] period
• [x] timestampset
• [x] periodset

Check MobilityDB's documentation on Time Types: https://docs.mobilitydb.com/nightly/ch02.html

For details on how UserDefinedType is used to create new column types in SQLAlchemy, refer to: https://docs.sqlalchemy.org/en/13/core/custom_types.html#sqlalchemy.types.UserDefinedType

For an example implementation, check code for TFloat class: https://github.com/adonmo/mobilitydb-sqlalchemy/blob/master/mobilitydb_sqlalchemy/types/TFloat.py (or its base class TBaseType)

[wendellwt]

Has 'Period' been included into the baseline? Do you have any guidelines on how to define Period as a new UserDefinedType?

[chaitan94]

It has not been implemented yet. Adding that functionality should be very similar to how TBaseType has been implemented as of now - so copying that file and making the modification would be the way to go in my opinion. Would you like to take a shot at it?

[wendellwt]

Ok, I'll give it a try. Will I need to implement each comparison operator? Will the class need to implement '@>' for contains?

[chaitan94]

For operators, in the new class that you will be adding, you would need to specify the field comparator_factory. TBaseType for example, does this using the class Comparator which is defined here.

[chaitan94]

If you mean you want to do the operation on a temporal type with period as an argument, you probably don't need to add another Comparator class.

For example if you wanted to do @> on a TGeomPoint column called trip, you can probably just do something like - MyTripsTable.trip.bbox_contains(MyFavoriteLocationsTable.point)

[wendellwt]

I'm really trying to implement a "temporal polygon" (!). I have some flight paths which are all TGeogPointSeq, and they are fine. I then take radar images of weather every hour, convert the storm cells to a PostGIS Polygon, and store them along with a time Period. I then want to construct a query to determine how long (either time or distance) did this (or any) flight fly through any storm. I would welcome any advice and suggestions you have.

Thanks, Wendell

[chaitan94]

That's an interesting use case. I think this can be done through MobilityDB, only under the assumption that these cells are in the form of boxes and not arbitrary shaped polygons. If that is an okay assumption to make, I think you are already going in the right direction. Using the bbox_contains operation would definitely be the way to go in my opinion. Perhaps you would want to operate on a stbox though, to be even more specific, a GEODSTBOX T. Let me know if that helps.

[wendellwt]

Ok, I have a (minimal) Period datatype that works. At least, and insert and retrieve of single (non-dataframe) items works. Should I issue a pull request? It might be a while before I understand how to read/write the Period string in a pandas dataframe. period_updates.zip

[chaitan94]

Sure, feel free to open a pull request.

[chaitan94]

Support for period added in 27405d2b9d00bb3b98b7d982c61012bdd3ec559a, periodset in 887f26552e748fcb1f22ad1e4e6eed1cc3f42157 and timestampset in 69b8062085957cb1ddeebf74a31123bb477641ae