SQL queries for finding all AQE/AQS sites and data within a bounding box from LouisvilleAirMap.com Data Visualization Wizard

1_tristate_sites.sql

SELECT * FROM
-- combine AQE and AQS tables into one result set
(
    SELECT 'aqe' AS site_type, location_ele::VARCHAR(255) AS elevation, id::VARCHAR(255) AS id, location_lat::float AS latitude, location_lon::float AS longitude, NULL::VARCHAR(255) AS msa_name, title::VARCHAR(255) AS name, NULL::VARCHAR(255) AS state_name FROM "7c0608e3-fb4d-4fb3-928c-5351e5b9b122" aqe
    UNION ALL
    SELECT 'aqs' AS site_type, elevation::VARCHAR(255) AS elevation, aqs_id::VARCHAR(255) AS id, lat::float AS latitude, lon::float AS longitude, msa_name::VARCHAR(255) AS msa_name, site_name::VARCHAR(255) AS name, state_name::VARCHAR(255) AS state_name FROM "b1b1e239-f5e2-4bc0-9572-6056fac5257b" aqs 
)

AS sites

WHERE
    -- select only sites whose lat/lon are within range
    (sites.longitude BETWEEN -74.2591 AND -73.7002)
    AND
    (sites.latitude BETWEEN 40.4774 AND 40.9176)

2-1_tristate_data_aqe.sql

SELECT
	data_table.feed_id,data_table.datetime,data_table.parameter,data_table.value,data_table.unit,data_table.computed_aqi,
	data_table.lat, data_table.lon
FROM "7d618c44-1098-4348-9f57-8d32d4b159b6" data_table
WHERE data_table.feed_id IN (115547, 100143, 97326, 96281, 131056, 99108, 115600, 1586954335, 97400, 115381, 121470, 115603, 2067742625, 89431, 89430, 103574, 940287246, 86766, 81322, 100592, 720744708, 97562, 95713, 666529147, 1738721662, 1801354032, 96529)
ORDER BY feed_id desc, datetime desc

2-2_tristate_data_aqs.sql

SELECT aqs_id, date, time, datetime, parameter, unit, value, computed_aqi
FROM "e5225140-ec95-4e20-bc75-b3bb63c46470" 
WHERE aqs_id IN ('360610134','340131003','340390004','360050133','340130003','340030006','360810120','360610119','360610135','360850067','360050112','340390003','360610125','360470052','340035001','360050080','340030010','360610128','360810124','360470118','360610115','360850114','340030004','360050110','340171003','360850111','340171002','340170006','340318801')
ORDER BY aqs_id desc, datetime desc, date desc

Narrative

Alrighty! I have some data for you. Sorry in advance for the length of this email but I hope it’ll maybe be useful to you (and it will help us get some of these processes documented on the site). If you want to jump right into the data, simply take a look at the three .CSV files in the attached zip file. They are:
tristate_sites.csv = a listing of the 56 AQE and AQS sites in the bounding box (explained in Notes below)
tristate_data_aqe.csv = all our data from AQE devices in the area
tristate_data_aqs.csv = all our data from EPA AQS monitoring sites in the area
We started collecting data in late May. If you need more historical data, it might be available from both Xively (where AQE devices report their data) as well as from the EPA.

There are a few ways to go about getting this data but the following leverages our existing work which makes it easier (though there are some limitations -  mainly data beginning dates)

Notes:
AQE = Air Quality Egg
AQS = EPA’s Air Quality System which you can read about more at http://www.epa.gov/ttn/airs/airsaqs/
For NYC, I am using a bounding box of -74.2591,40.4774,-73.7002,40.9176 which is what I get when I go to http://boundingbox.klokantech.com/ and type in New York City. This can be tweaked later if your definition differs.
My process:
Figure out what AQE and AQS site IDs you want data for - you could do this by selecting icons on the map and/or using the compare tool but for such a large area and for the sake of reproduction, I suggest jumping into the “Data Visualization Wizard”
Go to http://www.louisvilleairmap.com/wizard
Select the AQE and AQS checkboxes and then “Next”
The default query which loads and should return ~3,399 results is for ALL AQE and AQS sites. We need to use the editor box under “SQL Query” and put in the query that can be found here (also attached as tristate_sites.sql)
You should receive 56 results (27 AQE and 29 AQS sites) in the “Results”section of the page where you can see the data as a table, on a map, or export to CSV (attached as tristate_sites.csv)
We can see that we are interested in the following site IDs grouped by type:
AQE: 115547, 100143, 97326, 96281, 131056, 99108, 115600, 1586954335, 97400, 115381, 121470, 115603, 2067742625, 89431, 89430, 103574, 940287246, 86766, 81322, 100592, 720744708, 97562, 95713, 666529147, 1738721662, 1801354032, 96529
AQS: 360610134, 340131003, 340390004, 360050133, 340130003, 340030006, 360810120, 360610119, 360610135, 360850067, 360050112, 340390003, 360610125, 360470052, 340035001, 360050080, 340030010, 360610128, 360810124, 360470118, 360610115, 360850114, 340030004, 360050110, 340171003, 360850111, 340171002, 340170006, 340318801
Now that we know the site IDs we need data from, let’s go get the data.
AQE up first: 
Go to http://www.louisvilleairmap.com/wizard
Select just the AQE checkbox and then “Next”
The default query is for a list of eggs and show the latest data point we have. We want to query for something else — all data for the specified AQE IDs above. The query for that can be found here (also attached as tristate_data_aqe.sql)
Results are attached as tristate_data_aqe.csv. There are 71k+ rows of data. If you dig in, you’ll notice a few idiosyncrasies: a) only 7 unique feed IDs are shown – this means that only 7 of those 27 AQE devices reported data in the past few months; b) we have computed the component AQI when  possible but we added this as a feature along with per-datapoint lat/lon logging [eggs could move] a few weeks into the project. 
Now we do essentially the same for AQS
Go to http://www.louisvilleairmap.com/wizard
Select just the AQS checkbox and then “Next”
Replace the query with this one (attached as tristate_data_aqs.sql)
Results are attached as tristate_data_aqs.csv . There are 68k+ rows. Idiosyncracies to mention off the bar: a) there are only 21 unique AQS IDs so it seems like 8 of the 29 AQS in the bounding box have not reported any data in the past few months, b) at the beginning, we recorded date and time in separate columns but now combine them in a datetime column.
Be aware of a few things: 
 There may be a few several day gaps where we accidentally stopped collecting data. 
All dates/times are in GMT
Congrats for making it all the way to the bottom of this email. You will likely have a number of questions about how we’re collecting data and more. Don’t hesitate to reach out. I will do my best to respond in a timely manner.