Query to access the air quality measurements

59 views
Skip to first unread message

Alberto Villa

unread,
Jun 10, 2019, 4:25:15 PM6/10/19
to Safecast Air Quality Discussion
I am no more able to get access to my air quality measurement data as I did it before using queries like the following one:

Rob Oudendijk

unread,
Jun 10, 2019, 9:02:20 PM6/10/19
to Safecast Air Quality Discussion
Alberto,

Sorry for the issues you are having. Very recently, we (thanks to all the work of Mat and Eiko) moved the Safecast data collection/display from the Cloud66 API to AWS API and we did not update the links from dev yet. So the redirection is not happening.
The good news is that we can now acquire data through Grafana (see screenshot).


Please let me know if you can access the data and if you need other data to be displayed than the current data.

Regards,
Rob Oudendijk
screenshot.jpg

Alberto Villa

unread,
Jun 12, 2019, 10:34:11 AM6/12/19
to Safecast Air Quality Discussion
Thank you very much Rob,

I am able to visualize the data using Grafana, but I have a few doubts:

a) The air sensors graph legend mentions two groups of average parameters, pms and opc. What are their units?

b) In spite of the legend, I see only three traces (instead of six). Why? Note: They seem to belong to the "opc" family, according to their colour.

c) Considering the very low values diplayed on the graph for most of the time, I suspect that something went wrong in my particulate sensor when I cleaned it several months ago. Just in case, is it possible to get a replacement of the OPC-N2?

d) Is it possible to download my recorded data in a csv format like before? I would appreciate to go on processing it also with my spreadsheet.

Thanks again and best regards,

Alberto

====================================

Rob Oudendijk

unread,
Jun 12, 2019, 3:06:39 PM6/12/19
to Safecast Air
Alberto,

You can download the data by clicking on the title of the graph and select "download data as CSV"(see attached screenshot).I will reply in more detail later a bit more. 

regards,
rob



Regards,
Rob Oudendijk Yuka Hayashi
tel +81 80-22605966  Skype: robouden  Facebook:robouden linkedin:robouden  


--
You received this message because you are subscribed to a topic in the Google Groups "Safecast Air Quality Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/safecast-air/iLJv9SyFgPc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to safecast-air...@googlegroups.com.
To post to this group, send email to safeca...@googlegroups.com.
Visit this group at https://groups.google.com/group/safecast-air.
To view this discussion on the web visit https://groups.google.com/d/msgid/safecast-air/851883e7-dd84-4790-ba36-8bf8169e0d8b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Screen Shot 2019-06-13 at 4.02.33.png

Alberto Villa

unread,
Jun 14, 2019, 3:39:27 AM6/14/19
to safeca...@googlegroups.com

Alberto Villa

unread,
Jun 25, 2019, 11:52:55 AM6/25/19
to Safecast Air Quality Discussion
Hi Rob,

I have tried several times, but I can't get the CSV data as I was used before. See for instance the attached file.

Help...:-)

Thanks!

Alberto
Thank you, Rob!

Alberto

To unsubscribe from this group and all its topics, send an email to safecast-air+unsubscribe@googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "Safecast Air Quality Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/safecast-air/iLJv9SyFgPc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to safecast-air+unsubscribe@googlegroups.com.
grafana_data_export_series as rows_NO Excel dialect.csv

Rob Oudendijk

unread,
Jun 26, 2019, 7:29:44 AM6/26/19
to Safecast Air Quality Discussion
Alberto,


The old dev server has been moved to AWS. No data there at the moment. 
But I could download the data from the last weeks from Grafana in CSV format.

Seems only OPS data is there.Not sure yet why the PMS data is not there.
See the attached excel sheet.

regards
rob
 

On Tuesday, June 11, 2019 at 5:25:15 AM UTC+9, Alberto Villa wrote:
grafana_data_export.xlsx

Alberto Villa

unread,
Jun 26, 2019, 8:30:18 AM6/26/19
to Safecast Air Quality Discussion
Thank you Rob.

I would appreciate very much if you could help me recovering the pm data like I was able to do it before...

Regards,

Alberto
==================

Mat Schaffer

unread,
Jun 26, 2019, 8:45:57 PM6/26/19
to Safecast Air Quality Discussion
Hi Alberto,

Do you have any previous exports handy?

The dev.safecast.org server was very hand-rolled, so I can't really say anything about how it _was_ working. Probably some degree of luck was involved ;)

But if we have a clear idea of what you're looking for we can put something more reliable together.

Note, that the PM data is in those grafane-exported CSV file you sent, just mixed in with null values for time intervals with no data. 

Screen_Shot_2019-06-27_at_9_44_52_AM.png

Alberto Villa

unread,
Jun 28, 2019, 5:17:26 PM6/28/19
to Safecast Air Quality Discussion

Alberto Villa

unread,
Jun 28, 2019, 5:27:36 PM6/28/19
to Safecast Air Quality Discussion
Hi Mat,

To help you understand what I am looking for I attach here a pdf document I wrote a couple of years ago.

It is a guide to the extraction of the data from the downloaded *.csv files, to create useful graphs using pivot tables in Excel 2010.

You will find also one of those *.csv files I downloaded using the query indicated in the document.

Thank you and best regards.

Alberto
=====================

Il giorno giovedì 27 giugno 2019 02:45:57 UTC+2, Mat Schaffer ha scritto:
Safecast Air Data Graphing_Pivot Tables_Ed01.pdf
measurements_50011_2018-12-31_2019-01-08.csv

Mat Schaffer

unread,
Jun 29, 2019, 4:29:25 AM6/29/19
to safeca...@googlegroups.com
Thanks, Alberto. Is there a particular reason the document references dev.safecast.org?


--
You received this message because you are subscribed to the Google Groups "Safecast Air Quality Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to safecast-air...@googlegroups.com.

To post to this group, send email to safeca...@googlegroups.com.
Visit this group at https://groups.google.com/group/safecast-air.

Alberto Villa

unread,
Jun 29, 2019, 3:09:54 PM6/29/19
to Safecast Air Quality Discussion
You're welcome, Mat!

The reference you mention (in page 5) is part of the URL that I kindly got from Rob Oudendijk two years ago.

Go in this same forum to the old topic "Comments about the air quality database (21/04/2017)", open it, and then see Rob's answer dated 30/03/2017.

Regards,

Alberto
==================
To unsubscribe from this group and stop receiving emails from it, send an email to safeca...@googlegroups.com.

Alberto Villa

unread,
Jul 8, 2019, 3:45:13 PM7/8/19
to Safecast Air Quality Discussion
Hi Mat,

some good news?

Thanks,

Alberto

Mat Schaffer

unread,
Jul 8, 2019, 8:26:50 PM7/8/19
to safeca...@googlegroups.com
Some, yes. We've sorted out the state of the old dev server (the state is it's dead and not recoverable).

So we've pointed https://dev.safecast.org to our new AWS dev environment.

This means it'll be more stable and backed up going forward, but the data from the old dev server is gone.

Depending on what you're trying to do, the new server might work fine. But please don't consider it a safe place to store long term data. There may be future situations where we need to wipe or reset it in order to support testing for the production environment.

I haven't had the time to read & grok your guide yet but we need to work out a way to have this (and all) work happen against api.safecast.org which will ensure some reliability.

Hope that helps some and let me know if you have any questions.

-Mat



To unsubscribe from this group and stop receiving emails from it, send an email to safecast-air...@googlegroups.com.

To post to this group, send email to safeca...@googlegroups.com.
Visit this group at https://groups.google.com/group/safecast-air.

Alberto Villa

unread,
Aug 13, 2019, 6:45:35 AM8/13/19
to Safecast Air Quality Discussion
Hi Mat,

some silly questions from a non-specialist like me:

a) Can I download the data with a .csv file to build my own graphs as before? If yes, how?
b) How can I create another and safer place where to store the data coming from my 50011 unit?

Thanks & best regards.

Alberto
=======================

Mat Schaffer

unread,
Aug 16, 2019, 9:06:17 AM8/16/19
to safeca...@googlegroups.com
Hey, Alberto.

As Rob pointed out you can get a CSV from https://grafana.safecast.cc/. The only catch is that our graphs seem to inject "null" values you you'll have to filter those out (probably find/replace in excel) before you graph.

I'd like to see if we can work around that some how, maybe via a grafana setting. It's an OSS tool we use - https://grafana.com/, so often there are settings/features we're just not aware of yet.

Rob mentioned he'd reach out about that 2nd point. I think the best situation would be for the data to go into our ingest.safecast.org endpoint but with a `dev_test: true` flag. But I'm fuzzy on the device-side of the equation so hopefully he can help clear up the details there.


To unsubscribe from this group and stop receiving emails from it, send an email to safecast-air...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com.

Luis Felipe RM

unread,
Aug 18, 2019, 6:04:47 PM8/18/19
to safeca...@googlegroups.com
Hi Mat,

When building your query on Grafana to generate your graph, you have the
option of selecting if you want to inject NULL, zeros, previous values, etc.

GROUP BY = fill(none | null | 0 | previous value | etc.)

Best,
LF.

On 8/16/19 3:05 PM, Mat Schaffer wrote:
> Hey, Alberto.
>
> As Rob pointed out you can get a CSV from https://grafana.safecast.cc/.
> The only catch is that our graphs seem to inject "null" values you
> you'll have to filter those out (probably find/replace in excel) before
> you graph.
>
> I'd like to see if we can work around that some how, maybe via a grafana
> setting. It's an OSS tool we use - https://grafana.com/, so often there
> are settings/features we're just not aware of yet.
>
> Rob mentioned he'd reach out about that 2nd point. I think the best
> situation would be for the data to go into our ingest.safecast.org
> <http://ingest.safecast.org> endpoint but with a `dev_test: true` flag.
> But I'm fuzzy on the device-side of the equation so hopefully he can
> help clear up the details there.
>
> -Mat
>
> matschaffer.com <http://matschaffer.com>
>
>
> On Tue, Aug 13, 2019 at 7:45 PM Alberto Villa <alberto...@gmail.com
> <mailto:alberto...@gmail.com>> wrote:
>
> Hi Mat,
>
> some silly questions from a non-specialist like me:
>
> a) Can I download the data with a .csv file to build my own graphs
> as before? If yes, how?
> b) How can I create another and safer place where to store the data
> coming from my 50011 unit?
>
> Thanks & best regards.
>
> Alberto
> =======================
> Il giorno martedì 9 luglio 2019 02:26:50 UTC+2, Mat Schaffer ha scritto:
>
> Some, yes. We've sorted out the state of the old dev server (the
> state is it's dead and not recoverable).
>
> So we've pointed https://dev.safecast.org
> <https://dev.safecast.org/> to our new AWS dev environment.
>
> This means it'll be more stable and backed up going forward, but
> the data from the old dev server is gone.
>
> Depending on what you're trying to do, the new server might work
> fine. But please don't consider it a safe place to store long
> term data. There may be future situations where we need to wipe
> or reset it in order to support testing for the production
> environment.
>
> I haven't had the time to read & grok your guide yet but we need
> to work out a way to have this (and all) work happen against
> api.safecast.org <http://api.safecast.org> which will ensure
> some reliability.
>
> Hope that helps some and let me know if you have any questions.
>
> -Mat
>
> matschaffer.com <http://matschaffer.com>
>
>
> On Tue, Jul 9, 2019 at 4:45 AM Alberto Villa
> <alberto...@gmail.com> wrote:
>
> Hi Mat,
>
> some good news?
>
> Thanks,
>
> Alberto
>
> Il giorno sabato 29 giugno 2019 21:09:54 UTC+2, Alberto
> Villa ha scritto:
>
> You're welcome, Mat!
>
> The reference you mention (in page 5) is part of the URL
> that I kindly got from Rob Oudendijk two years ago.
>
> Go in this same forum to the old topic "Comments about
> the air quality database (21/04/2017)", open it, and
> then see Rob's answer dated 30/03/2017.
>
> Regards,
>
> Alberto
> ==================
> Il giorno sabato 29 giugno 2019 10:29:25 UTC+2, Mat
> Schaffer ha scritto:
>
> Thanks, Alberto. Is there a particular reason the
> document references dev.safecast.org
> <http://dev.safecast.org>?
>
>
>
> -Mat
>
> matschaffer.com <http://matschaffer.com>
>
>
> On Sat, Jun 29, 2019 at 6:27 AM Alberto Villa
> <alberto...@gmail.com> wrote:
>
> Hi Mat,
>
> To help you understand what I am looking for I
> attach here a pdf document I wrote a couple of
> years ago.
>
> It is a guide to the extraction of the data from
> the downloaded *.csv files, to create useful
> graphs using pivot tables in Excel 2010.
>
> You will find also one of those *.csv files I
> downloaded using the query indicated in the
> document.
>
> Thank you and best regards.
>
> Alberto
> =====================
>
> Il giorno giovedì 27 giugno 2019 02:45:57 UTC+2,
> Mat Schaffer ha scritto:
>
> Hi Alberto,
>
> Do you have any previous exports handy?
>
> The dev.safecast.org
> <http://dev.safecast.org> server was very
> hand-rolled, so I can't really say anything
> about how it _was_ working. Probably some
> degree of luck was involved ;)
>
> But if we have a clear idea of what you're
> looking for we can put something more
> reliable together.
>
> Note, that the PM data is in those
> grafane-exported CSV file you sent, just
> mixed in with null values for time intervals
> with no data. 
>
> http://dev.safecast.org/en-US/devices/50011/measurements?utf8=%E2%9C%93&latitude=&longitude=&distance=&captured_after=*01*%2F*01*%2F*2019*+12%3A18%3A04&captured_before=*10*%2F*06*%2F*2019*+12%3A18%3A04&since=&until=&commit=Filter&commit=Filter&format=csv&options=with-headings
> <http://dev.safecast.org/en-US/devices/50011/measurements?utf8=%E2%9C%93&latitude=&longitude=&distance=&captured_after=01%2F01%2F2019+12%3A18%3A04&captured_before=10%2F06%2F2019+12%3A18%3A04&since=&until=&commit=Filter&commit=Filter&format=csv&options=with-headings>
>
>
> In fact, this message appears:
> 502 Bad Gateway
>
>
> How should I modify the query to
> proceed?
>
>
> Thank you very  much and best
> regards.
>
>
> Alberto
>
>
> --
> You received this message because you are
> subscribed to the Google Groups "Safecast Air
> Quality Discussion" group.
> To unsubscribe from this group and stop
> receiving emails from it, send an email to
> safeca...@googlegroups.com.
> To post to this group, send email to
> safeca...@googlegroups.com.
> Visit this group at
> https://groups.google.com/group/safecast-air.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/safecast-air/6d041807-fad1-4f6b-a97b-a3552cb5bd6b%40googlegroups.com
> <https://groups.google.com/d/msgid/safecast-air/6d041807-fad1-4f6b-a97b-a3552cb5bd6b%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit
> https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the
> Google Groups "Safecast Air Quality Discussion" group.
> To unsubscribe from this group and stop receiving emails
> from it, send an email to safeca...@googlegroups.com.
> To post to this group, send email to safeca...@googlegroups.com.
> Visit this group at
> https://groups.google.com/group/safecast-air.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/safecast-air/57eed66c-3603-4fbd-ac94-acea2e516118%40googlegroups.com
> <https://groups.google.com/d/msgid/safecast-air/57eed66c-3603-4fbd-ac94-acea2e516118%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Safecast Air Quality Discussion" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to safecast-air...@googlegroups.com
> <mailto:safecast-air...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com
> <https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Safecast Air Quality Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to safecast-air...@googlegroups.com
> <mailto:safecast-air...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/safecast-air/CABRhK1LbXS7RW0_ROVN727TKOMNrE%2BRELaD_cQyUhgfREFE-pg%40mail.gmail.com
> <https://groups.google.com/d/msgid/safecast-air/CABRhK1LbXS7RW0_ROVN727TKOMNrE%2BRELaD_cQyUhgfREFE-pg%40mail.gmail.com?utm_medium=email&utm_source=footer>.

Mat Schaffer

unread,
Aug 22, 2019, 9:28:10 AM8/22/19
to safeca...@googlegroups.com
Thanks Luis!

I played around a bit with this tonight, but seems like despite graphing option choice grafana always exports "null" into the CSV for missing values.

The best I've been able to manage so far is limiting the time interval to something we know we'll have a least one measurement for. So like here are all the Max opc_pm02_5 measurements for safecast-air:50011 per hour.


If you export CSV from that you get a clean file.

Kalin mentioned grafana 6 is out so might try upgrading and seeing if that offers any cleaner CSV export option.

We could also look at building a new CSV export feature into api.safecast.org but with an all-volunteer staff I can't really give any idea of when that might be achievable. If anyone's up to try I'm happy to help steer the work.


To unsubscribe from this group and stop receiving emails from it, send an email to safecast-air...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/safecast-air/6e4d94fb-edde-760d-a54a-369ad9ffca62%40gmail.com.

Alberto Villa

unread,
Dec 15, 2019, 5:42:23 PM12/15/19
to Safecast Air Quality Discussion
Hi to the specialists,

I am still not able to download in a simple and complete way the particulate measurements of my Safecast Air 50011 device from Grafana (see my previous posts), and now other problems have emerged.

The PM1 and PM10 plots disappeared from the Grafana display. Furthermore, the time samples of the PM2.5 are on the average very sparse, sometimes with several hours in between. See attached screenshot.

Is it possible to find out why? Is it a problem at the origin (my 50011), or an issue with Grafana?

I have planned the substitution of the particulate detector because of excessively low-level, unrealistic readings recorded this year, making me think it could be defective. I want to be sure about the accuracy of my 50011, but before proceeding with the expense I need to know if I can trust Grafana, and if I will be able to record the data as smoothly as before the server problems caused the switch to Grafana.

Last but not least: do you think that a software-ignorant like me could find a solution to collect the particulate data also directly? For instance, like I can do it with my bGeigie radiation measurements?

Please help me going on using the Safecast Air as effectively as possible...

Thank you very much and best regards.

Alberto



>     send an email to safeca...@googlegroups.com
>     <mailto:safecast-air+unsub...@googlegroups.com>.

>     To view this discussion on the web visit
>     https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com
>     <https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Safecast Air Quality Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send
To unsubscribe from this group and stop receiving emails from it, send an email to safeca...@googlegroups.com.
Safecast air 50011_2019-12-15_PM2.5 only.PNG

Rob Oudendijk

unread,
Dec 16, 2019, 3:34:22 AM12/16/19
to Safecast Air
Alberto,

Sorry for the confusion/mistakes etc. 
We are in the process for redesign the Grafana dashboards please give us time.


regards
rob



Regards,
Rob Oudendijk Yuka Hayashi
tel +81 80-22605966  Skype: robouden  Facebook:robouden linkedin:robouden  


>     To view this discussion on the web visit
>     https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com
>     <https://groups.google.com/d/msgid/safecast-air/43844daf-9395-4e69-80bb-6095acab23b8%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Safecast Air Quality Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to safeca...@googlegroups.com

> To view this discussion on the web visit
> https://groups.google.com/d/msgid/safecast-air/CABRhK1LbXS7RW0_ROVN727TKOMNrE%2BRELaD_cQyUhgfREFE-pg%40mail.gmail.com
> <https://groups.google.com/d/msgid/safecast-air/CABRhK1LbXS7RW0_ROVN727TKOMNrE%2BRELaD_cQyUhgfREFE-pg%40mail.gmail.com?utm_medium=email&utm_source=footer>.

--
You received this message because you are subscribed to the Google Groups "Safecast Air Quality Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to safeca...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/safecast-air/6e4d94fb-edde-760d-a54a-369ad9ffca62%40gmail.com.

--
You received this message because you are subscribed to a topic in the Google Groups "Safecast Air Quality Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/safecast-air/iLJv9SyFgPc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to safecast-air...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/safecast-air/ce101223-db5f-4b91-8c48-e7bc4c543c5f%40googlegroups.com.

Alberto Villa

unread,
Jan 14, 2020, 7:18:10 AM1/14/20
to Safecast Air Quality Discussion
Thank you very much, Rob!

Up to now I can see only the PM2.5. I suppose that the PM1 and PM10 will follow soon...

Best regards,

Alberto

Rob Oudendijk

unread,
Jan 14, 2020, 4:30:46 PM1/14/20
to Safecast Air
Alberto,

Yes. We will work on that later.
regards
rob


Regards,
Rob Oudendijk Yuka Hayashi
tel +81 80-22605966  Skype: robouden  Facebook:robouden linkedin:robouden  

--
You received this message because you are subscribed to a topic in the Google Groups "Safecast Air Quality Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/safecast-air/iLJv9SyFgPc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to safecast-air...@googlegroups.com.

Alberto Villa

unread,
Mar 13, 2020, 7:05:50 AM3/13/20
to Safecast Air Quality Discussion
To Rob and Mat:

I have collected several technical papers about low-cost air quality sensors. You can find them at  the folowing Dropbox address (maybe you arleady know them, but just in case...):


By the way: my Graphana page is still showing only the pm2.5 data.

Thank you very much and best regards.

Alberto

Il giorno lunedì 10 giugno 2019 22:25:15 UTC+2, Alberto Villa ha scritto:

Rob Oudendijk

unread,
Mar 15, 2020, 7:13:17 PM3/15/20
to Safecast Air Quality Discussion
Alberto,

Thanks for the documents. Very useful.
We are still working on the Grafana pages. Please be patient.


Regards,
rob


On Tuesday, June 11, 2019 at 5:25:15 AM UTC+9, Alberto Villa wrote:
Reply all
Reply to author
Forward
0 new messages