A query of the event recorded in Wyoming using Postgres Standard Query Language (pSQL).

DECO Query Ex #1

Motivation: find the fields we are interested in.
"The images and duration from  and between events recorded by two phones at a specific location (Casper, Wyoming)."
*42.820738, -106.265443*


gsw=> \d deco_observation_v
View "public.deco_observation_v"
Column | Type | Modifiers
---------------------------+--------------------------+-----------
id_pk | bigint | (Image identification, primary key)

sensor_id | character varying(256) | (Sensor identification of the phones)

acquisition_time | timestamp with time zone | (When the event took place)

acquisition_duration | bigint | (How long the event lasted)

acquisition_period | bigint | (Time between events)

latitude | double precision |

longitude | double precision |

altitude | double precision |

device_arch_fk | integer |

device_os_fk | integer |

device_user_agent_fk | integer |

ambient_magnetic_field_fk | bigint |

ambient_temperature_fk | bigint |

barometric_pressure_fk | bigint |

relative_humidity_fk | bigint |

image_filter1_fk | integer |

image_filter2_fk | integer |

image_data_fk | bigint | (Image data foreign key that should match image identification primary key)

created | timestamp with time zone |

NOTE: @ means absolute value


gsw=> SELECT id_pk, acquisition_duration FROM deco_observation_v WHERE @( latitude - 42.820738 ) < 0.01 AND @( longitude - -106.265443) < 0.01;
id_pk | acquisition_duration
-------+----------------------
(0 rows)

Here, we want to find event images from the database in Casper Wyoming as well as the event duration. To do so, we enter a command that selects images from the database using the SELECT command. We also need to specify the location parameters for the event. We set this to the nearest 0.01 of the coordinates. Unfortunately, zero results turned up. Perhaps the phones could not get a GPS lock?



ADDITIONAL INFORMATION GIVEN: sensor_id = "DECO-ffffffff-9eaf-9658-d0db-466e0033c587"

gsw=> SELECT id_pk, acquisition_duration FROM deco_observation_v WHERE sensor_id = 'DECO-ffffffff-9eaf-9658-d0db-466e0033c587';
id_pk | acquisition_duration
-------+----------------------
(0 rows)

Again, no results.



Motivation: Find any and all information near given coordinates

gsw=> SELECT id_pk, latitude, longitude FROM geolocation_t WHERE @( latitude - 42.820738 ) < 0.1 AND @( longitude - -106.265443) < 0.1;
id_pk | latitude | longitude
-------+----------+-----------
(0 rows)

This time we increased the radius in which the database would search for events in a given location. Again, no results.



Motivation: Look for events in the time range as given "The app was running July 6th ~1pm MST until July 9th 12pm MST"

gsw=> SELECT id_pk, acquisition_duration FROM deco_observation_v WHERE created >= '2013.07.05 24:00' AND created <= '2013.07.9 24:00'
;
id_pk | acquisition_duration
-----------+----------------------
125442840 | 887
125437761 | 828
125314232 | 882
125091491 | 2565
125076368 | 1429
125016258 | 1391
(6 rows)

Now we have six events, whether they are all real events from cosmic rays is not guaranteed. All we need is to see which phones they came from and where the phones were located.



gsw=> SELECT id_pk, acquisition_duration, latitude, longitude, sensor_id FROM deco_observation_v WHERE created >= '2013.07.05 24:00' AND created <= '2013.07.9 24:00'
;
id_pk | acquisition_duration | latitude | longitude | sensor_id
-----------+----------------------+------------------+-------------------+-------------------------------------------
125442840 | 887 | 33.7061026863163 | -117.782706837894 | DECO-ffffffff-f375-92ea-1a59-cdd00033c587
125437761 | 828 | 33.7067472623562 | -117.783105124145 | DECO-ffffffff-f375-92ea-1a59-cdd00033c587
125314232 | 882 | 33.7062419795032 | -117.782831196588 | DECO-ffffffff-f375-92ea-1a59-cdd00033c587
125091491 | 2565 | 34.0627348423004 | -118.297412395477 | DECO-ffffffff-aa25-4f4c-e29f-e85a0033c587
125076368 | 1429 | 33.70622419 | -117.78276402 | DECO-00000000-7f71-62fb-f647-baf70033c587
125016258 | 1391 | 33.70601521 | -117.78275264 | DECO-00000000-7f71-62fb-f647-baf70033c587
(6 rows)

Unfortunately, none of these events came from the phones in Wyoming. We know this because a) the sensor IDs do not match the phones' IDs in Wyoming and b) all events took place in California based off of the GPS coordinates.