Skip to content

Operations Tips

This document contains tips and cookbooks on how to do things related to the Tracking database.

Mapping One Platform to Another

Oftentimes, we want to map location data from one source to another. For example, we use Spot trackers on M1 so we like to map those locations as if they are coming from M1. In order to do this, you need to:

  1. ssh into pismo as ‘driftertrack’
  2. cd to ~/dev/MBARItracking/scripts
  3. vi drifter.py
  4. Around line 693, there is an entry that maps the Stella SPOT data to M1. Edit that to send SPOT tracker data masking as M1.

Useful SQL Statements

Below are some useful SQL statements that we have accumulated over the years that are related to maintenance and upkeep of the TrackingDB(s).

Removing Duplicates Positions

To remove duplicates positions in the Tracking DB, run:

DELETE FROM position WHERE id IN (SELECT id FROM (SELECT id, row_number() over (partition BY platform_id, datetime ORDER BY id) AS rnum FROM position) t WHERE t.rnum > 1);

Removing Out-of-Bounds Location

To query for the rows that are out of normal bounds for lat lon locations (order by reverse time to see most recent first):

select platform_id, name, datetime, st_y(geom), st_x(geom) from position, platform where platform.id = position.platform_id and (st_x(geom) < -180 or st_x(geom) > 180 or st_y(geom) > 90 or st_y(geom) < -90) order by datetime desc

To count them up, use:

select count(*) from position where ST_X(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) > 90 or ST_Y(geom) < -90

To remove rows that have lat and lon outside of the -90->90, -180->180:

delete from position where ST_X(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) > 90 or ST_Y(geom) < -90