Creating SQL Queries

You can create your own SQL queries to retrieve data from the Intermapper database. The datasample tables contain the 5-minute, hourly, and daily samples derived from the original data values. The recommended approach for retrieving data is to obtain it from these tables. For more information, see Intermapper Database Schemas.

You can also query individual data values, but this is much slower than querying the datasample tables. In Intermapper 5.4 and earlier, individual data values were stored in the datapoints table. They are now stored in the datastore table. Existing queries on the datapoints table must be rewritten to use the datastore table instead. This only applies if you have written queries in this or a related construct:

SELECT FROM datapoint WHERE dataset_id = 5 AND data_time BETWEEN a AND b

To retrieve data from the datastore table, use the load_data() function, described below.

Using the load_data() Function

Use this function only if you have an existing query on the datapoint table, or if you need the individual raw values. Most of the time, you should query the datasample tables as described above, since they are faster and easier to access.

The load_data() function uses the following syntax:

load_data([dataset id],[datatime start],[datatime end])

For example, to retrieve data for dataset_id = 5 between data_times a and b, use the following syntax:

SELECT data_time, data_value FROM load_data(5, a, b)

The explicit column list is not required, but it is recommended. If you use SELECT * rather than an explicit column list, the function returns a single column of the built-in composite-value type, containing both values. You can still reference the values from this composite data type, but you cannot treat it as you would a regular PostgreSQL column.

The load_data() function acts as a table source, and accepts the built-in PostgreSQL infinity and -infinity timestamps.

SELECT data_time, data_value 
FROM load_data(1, '2011-11-09 00:00:00', 'infinity') 
ORDER BY data_time

You can also use UNION to combine sources.

SELECT 5 as dataset_id, data_time, data_value 
FROM load_data(5, '2011-11-09 00:00:00', 'infinity') 
UNION SELECT 6 as dataset_id, data_time,data_value 
FROM load_data(6, '2011-11-09 00:00:00', 'infinity') 
UNION SELECT 14 as dataset_id, data_time, data_value 
FROM load_data(14,'2011-11-09 00:00:00', 'infinity') 
ORDER BY dataset_id, data_time