Skip to content

Instantly share code, notes, and snippets.

@peterdesmet
Last active August 29, 2015 14:07
Show Gist options
  • Save peterdesmet/404002d0e4c4694c3890 to your computer and use it in GitHub Desktop.
Save peterdesmet/404002d0e4c4694c3890 to your computer and use it in GitHub Desktop.
SQL queries to dive into the data from the RBINS radar.
ALTER TABLE rbins_radar_occurrences
RENAME COLUMN expr1000 to date;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN area SET data type integer USING area::integer;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN av_reflectivity SET data type integer USING av_reflectivity::integer;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN date SET data type timestamp with time zone USING date::timestamp with time zone;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN heading SET data type integer USING heading::integer;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN latitude SET data type double precision USING latitude::double precision;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN longitude SET data type double precision USING longitude::double precision;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN speed SET data type integer USING speed::integer;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN std_dev_reflectivity SET data type integer USING std_dev_reflectivity::integer;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN target_height SET data type double precision USING target_height::double precision;
ALTER TABLE rbins_radar_occurrences
ALTER COLUMN target_width SET data type double precision USING target_width::double precision;
WITH processed_occ AS (
SELECT
*
FROM rbins_radar_occurrences AS occ
JOIN (
SELECT
track_id as track_id_for_length,
count(*) as track_length
FROM rbins_radar_occurrences
GROUP BY track_id
) AS track_length
ON occ.track_id = track_length.track_id_for_length
)
SELECT
*
FROM processed_occ
WHERE
track_length >= 3
SELECT
track_length,
count(*)
FROM (
SELECT
track_id,
count(*) AS track_length
FROM rbins_radar_occurrences
GROUP BY track_id
) AS track_length_table
GROUP BY track_length
ORDER BY track_length
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment