SQL queries to dive into the data from the RBINS radar.

rbins_radar_prepare_data.sql

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;

rbins_radar_select_tracks_on_length.sql

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

rbins_radar_summary_of_track_lengths.sql

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