workmaster2n

[2013-12-05 13:28:28] requirements_osx_brew_libs_install
requirements_osx_brew_libs_install () 
{ 
    brew unlink "$@" || true;
    brew install "$@" || { 
        typeset ret=$?;
        rvm_warn "There were package installation errors, make sure to read the log.

Try \`brew tap --repair\` and make sure \`brew doctor\` looks reasonable.

workmaster2n

SELECT DISTINCT ON (date_trunc('day', recorded_at)) COUNT(*)
FROM tracked_points WHERE worker_id = 1 AND recorded_at > '2013-11-14' 

workmaster2n

"HashAggregate  (cost=142395.01..142395.21 rows=20 width=4) (actual time=1693.497..1693.500 rows=21 loops=1)"
"  ->  Seq Scan on tracked_points  (cost=0.00..121326.67 rows=4213667 width=4) (actual time=0.006..665.009 rows=4206624 loops=1)"
"Total runtime: 1693.546 ms"

workmaster2n

SELECT tracked_point_zones.worker_id, tracked_point_zones.zone_id, COUNT(*) FROM tracked_point_zones
LEFT JOIN zones ON zones.id = tracked_point_zones.zone_id AND tracked_point_zones.recorded_at > '2013-11-01' AND tracked_point_zones.worker_id = 5
GROUP BY tracked_point_zones.worker_id, tracked_point_zones.zone_id
ORDER BY COUNT(*) DESC

workmaster2n

To install PostGIS/Postgres: 
sudo apt-get install python-software-properties  
sudo apt-add-repository ppa:ubuntugis/ppa  
sudo apt-get update 
sudo apt-get install postgresql-9.1-postgis 

workmaster2n

UPDATE tracked_points SET worker_id = worker_tags.worker_id
 FROM tags LEFT JOIN worker_tags ON tags.id = worker_tags.tag_id
 WHERE (tags.frequency = tracked_points.frequency OR worker_tags.id IS NULL)
 AND tracked_points.worker_id_checked_at IS NULL

workmaster2n

SELECT * FROM tracked_points 
INNER JOIN tags ON tags.frequency = tracked_points.frequency
INNER JOIN worker_tags ON worker_tags.tag_id = tags.id
ORDER BY recorded_at DESC LIMIT 100

workmaster2n

SELECT tracked_points.worker_id, zones.id, zones.name, COUNT(*) FROM tracked_point_zones 
INNER JOIN zones
ON zones.id = tracked_point_zones.zone_id
INNER JOIN tracked_points
ON tracked_points.id = tracked_point_zones.tracked_point_id
GROUP BY tracked_points.worker_id, zones.id
ORDER BY tracked_points.worker_id, COUNT(*) DESC

workmaster2n

export DATABASE_URL=username:password@host:port/dbname

workmaster2n

UPDATE tracked_points 
    SET worker_id = worker_tags.worker_id, worker_id_checked_at = now()
    FROM tags 
    INNER JOIN worker_tags 
      ON tags.id = worker_tags.tag_id
    WHERE 
      tags.frequency = tracked_points.frequency
    AND 
       tracked_points.worker_id_checked_at IS NULL