Sam Hatchett
2016-09-26 21:01:38 UTC
In trying to combine InfluxDB with Grafana's World Map plugin for
displaying time series data spatially, I've identified the need to
spatially subsample because I just have too many geographical points to
display all at once - too much lag time to return the result set. The
location's geohash provides a great way to do this (encoded as a tag value,
so I can group-by geohash to return some aggregate of potentially hundreds
of location's values), and then I can select the max, mean, min, etc., for
each spatial region, but the level of spatial aggregation has to be decided
prior to writing data to Influx.
What I'm wondering about are the relative merits of workarounds, or of
finding some way to decide on the geohash length when the query is executed.
So for instance, have multiple tags: (geo8, geo9, geo10 ...) for each
series so that I can decide on spatial subsampling when querying and choose
the right length:
select "value" from "temperature" where ... group by "geo8"
But what I really want to do is store the full 12-character geohash for
each location and group by a substring of that geohash:
select "value" from "temperature" where ... group by substr("geohash",0,8)
It's also useful to note that I can currently limit the locations that are
queried by using a regex:
select "value" from "temperature" where "geohash" =~ /^gbsuv7s/
... but can't use a substring of the hash to do the group-by, which would
take me the rest of the way there.
Any thoughts or comments would be most appreciated. I don't think that
there's a really straightforward solution right now, but does using a
substring (or other arbitrary transformation) of a tag value for grouping
make sense to anyone else? Seems like there could be many more uses of this
type of approach.
displaying time series data spatially, I've identified the need to
spatially subsample because I just have too many geographical points to
display all at once - too much lag time to return the result set. The
location's geohash provides a great way to do this (encoded as a tag value,
so I can group-by geohash to return some aggregate of potentially hundreds
of location's values), and then I can select the max, mean, min, etc., for
each spatial region, but the level of spatial aggregation has to be decided
prior to writing data to Influx.
What I'm wondering about are the relative merits of workarounds, or of
finding some way to decide on the geohash length when the query is executed.
So for instance, have multiple tags: (geo8, geo9, geo10 ...) for each
series so that I can decide on spatial subsampling when querying and choose
the right length:
select "value" from "temperature" where ... group by "geo8"
But what I really want to do is store the full 12-character geohash for
each location and group by a substring of that geohash:
select "value" from "temperature" where ... group by substr("geohash",0,8)
It's also useful to note that I can currently limit the locations that are
queried by using a regex:
select "value" from "temperature" where "geohash" =~ /^gbsuv7s/
... but can't use a substring of the hash to do the group-by, which would
take me the rest of the way there.
Any thoughts or comments would be most appreciated. I don't think that
there's a really straightforward solution right now, but does using a
substring (or other arbitrary transformation) of a tag value for grouping
make sense to anyone else? Seems like there could be many more uses of this
type of approach.
--
Remember to include the InfluxDB version number with all issue reports
---
You received this message because you are subscribed to the Google Groups "InfluxDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to influxdb+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/influxdb.
To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/2ea6b448-16a6-45e9-85c1-afff36b1a4d4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Remember to include the InfluxDB version number with all issue reports
---
You received this message because you are subscribed to the Google Groups "InfluxDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to influxdb+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/influxdb.
To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/2ea6b448-16a6-45e9-85c1-afff36b1a4d4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.