popufare/server/scripts/popufare-avls

#!/bin/bash
#
# Copyright (c) 2020 Clementine Computing LLC.
#
# This file is part of PopuFare.
#
# PopuFare is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# PopuFare is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with PopuFare.  If not, see <https://www.gnu.org/licenses/>.
#


# A simple script ot query the AVLS data and format it out nicely.
# This is a rough draft and should be considered experimental
#
# NOTE: this assumes the existence of a 'db-config' file that holds database
# credential information. For example:
#
# $ cat db-config
# [client]
# database=popufare
# user=u
# password=p
#
# usage:
#
#  ./popufave-avls '2020-11-05 00:00:00'
#
#

querytd="$1"

if [[ "$1" == "" ]] ; then
  querytd=`date --date '-10 min' +'%Y-%m-%d %H:%M:%S'`
fi

cat <<EOF | mysql --defaults-file=db-config | tr ',' '_' | tr '\t' ','
set @ts = "$querytd";

select
    a.equip_num as vehicle_id,
    floor(a.latitude/100.0) +  ((a.latitude -  (100.0*floor(a.latitude/100.0)))/60.0)  as vehicle_lat,
    floor(a.longitude/100.0) + ((a.longitude - (100.0*floor(a.longitude/100.0)))/60.0) as vehicle_lon,
-- a.latitude as vehicle_lat,
-- a.longitude as vehicle_lon,

    a.heading as vehicle_bearing,
    a.chirp_time as vehicle_timestamp,
    a.velocity as vehicle_velocity,
    a.route as route_id,
    concat(convert(a.route, char), '_', convert(a.trip, char)) trip_id,


    floor(s.latitude/100.0)  + ((s.latitude -  (100.0*floor(s.latitude/100.0)))/60.0)  as stop_lat,
    floor(s.longitude/100.0) + ((s.longitude - (100.0*floor(s.longitude/100.0)))/60.0) as stop_lon,
-- s.latitude as stop_lat,
-- s.longitude as stop_lon,

    s.name as stop_name

from 
  avls_data a,
  stops s, 
  paddles p
where 
      a.paddle = p.id
  and a.route = p.route
  and a.trip = p.trip
  and ( (a.stop = 0 and p.stop is null) or
        (a.stop = p.stop) )
  and p.stopid = s.id
  and a.chirp_time >= @ts
order by chirp_time desc ;
EOF