Timescaledb: More documentation about Timescale DB + PostGIS?

[jbkoh]

Hi Timescale DB!

I am new here and looking for a good spatio+temporal database. I've been using GeoMesa with HBase, but the latency was not bearable. I found that Timescale DB can be working with PostGIS as in the example at timescale.com. However, I can't find a proper documentation of how to do it.

Could you elaborate a bit more about their relationship? Is it enough just to put "geometry" type when creating the hypertable?

[mfreed]

Hi @jbkoh it's really straight forward:

1. Install both extensions (e.g., CREATE EXTENSION ...) in the same database
2. Create a table with a GEOMETRY type
3. Make the table a hypertable.

You can now perform PostGIS queries on Timescale.

You can also take an existing hypertable and add a geometry column, index, etc. Just treat the hypertable like a standard Postgres table.

Here are some more step-by-step directions: http://docs.timescale.com/v0.8/tutorials/tutorial-hello-nyc#tutorial-postgis

[jbkoh]

Thanks for the clarification! It works for me.

Not sure if my following question makes sense, but how many rows can it handle? I believe that timescale db partitions the hypertable based on the timestamp. Are spatial queries scalable (i.e., similar latency as time-based query) together with time-based queries?

Thanks!

[mfreed]

Timescale requires that you partition based on some time/integer-oriented attribute, but you can also partition on additional dimensions: http://docs.timescale.com/v0.8/api#add_dimension

But your question doesn't have any easy answer. We've had people store many billion row hypertables (time + geometry columns), but the latency is very much a function of the type of query. For example, if you include a time predicate in your geospatial query, that we can significantly exclude the number of partitions we need to touch in order to execute the query (including the spatial query). And the effectiveness/performance of spatial constraints depend a lot on the type of query being performed.

But in general, Timescale should give you superior performance to vanilla Postgres if you have some type of time attribute. Happy to discuss your use case more on Slack.

[jbkoh]

For now, the queries would not associate any analytics but naive data retrieval with time ranges and bounding boxes. I will start with the basic functions and continue the discussion at the Slack channel once the idea is matured. Thanks so much for the information. You can close this issue if needed.

[jugdemon]

I tried to find the tutorials but all links are dead. Is there a way to get to them or can you send me the zips?

[deltodon]

Had a same issue as @jugdemon but eventually found the tutorial here:
https://docs.timescale.com/clustering/tutorials/tutorial-hello-nyc

with PostGIS at point 5.
https://docs.timescale.com/clustering/tutorials/tutorial-hello-nyc#tutorial-postgis

I hope that helps.