mattwigway

CREATE TABLE vta.segments AS
SELECT 
  -- make a speed placeholder, update later
  s.trip_id, s.stop_id AS start_id, e.stop_id AS end_id, s.time AS start_time, e.time AS end_time,
  trips.route_id, trips.trip_headsign, -1.01 AS speed,
  -- get the time, and deal with wrapped times like 24:21 for a trip that runs past midnight
  -- if the time is negative, add 24:00:00
  CASE 
    -- segment crosses midnight: wrap it
    WHEN SUBSTR(e.time, 1, 2)::int2 >= 24 AND SUBSTR(s.time, 1, 2)::int2 < 24

mattwigway

-- (C) Matthew Conway, CC-BY-NC-SA
SELECT ROW_NUMBER() OVER () AS oid, t1.trip_id, t1.stop_id as from_id, t1.departure_time as from_time, t1.stop_sequence AS from_seq,
       t2.stop_id as to_id, t2.arrival_time as to_time, t2.stop_sequence AS to_seq,
       EXTRACT(EPOCH FROM t2.arrival_time::time - t1.departure_time::time) AS traversal,
       (SELECT ST_MakeLine(geom) AS the_geom
          FROM vta.stop_times 
          JOIN vta.stops USING (stop_id)
        WHERE trip_id = t1.trip_id AND stop_sequence BETWEEN t1.stop_sequence AND t2.stop_sequence) AS the_geom
        INTO vta.trip_segments
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY trip_id, departure_time NULLS LAST) AS num FROM vta.stop_times WHERE departure_time IS NOT NULL) t1

mattwigway

ALTER TABLE nextbus RENAME COLUMN time TO time_orig; -- save the old column
ALTER TABLE nextbus ADD COLUMN time timestamp; -- create the new column
UPDATE acrt.nextbus SET time = time_orig::timestamp; -- set the timestamps. This takes a VERY VERY LONG time; for a 395k point table, it took over 654 (!) seconds
ALTER TABLE nextbus DROP COLUMN time_orig;

mattwigway

-- All that mess of parens is b/c, if you're left joining, you have to do
-- ((a LEFT JOIN b) LEFT JOIN c) and so on; a LEFT JOIN b LEFT JOIN c does not work
SELECT a.Zip_Code, a.total, af.africanamerican, asi.asian, f.filipino, h.hispanic, me.multiethnic, fn.firstnations, o.other, p.pacificislander,
       u.unrecorded, w.white
INTO enrollment_by_zip
FROM
((((((((((SELECT Zip_Code, count(*) AS total FROM enrollment GROUP BY Zip_Code) a LEFT JOIN
(SELECT Zip_Code, count(*) AS africanamerican FROM enrollment WHERE ethnicity='African American' GROUP BY Zip_Code) af ON a.zip_code = af.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS asian FROM enrollment WHERE ethnicity='Asian' GROUP BY Zip_Code) asi ON a.zip_code = asi.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS filipino FROM enrollment WHERE ethnicity='Filipino' GROUP BY Zip_Code) f ON a.zip_code = f.zip_code) LEFT JOIN

mattwigway

SELECT loc_a.oid, loc_a.vehicle, loc_a.route, loc_a.direction, transform(ST_MakeLine(loc_a.the_geom, loc_b.the_geom), 26945) AS the_geom,
	(ST_Length(transform(ST_MakeLine(loc_a.the_geom, loc_b.the_geom), 26945))/
	(EXTRACT(EPOCH FROM loc_b.time) - EXTRACT(EPOCH FROM loc_a.time))) * 
	2.23693629 AS mph, loc_a.time AS starttime, loc_b.time AS endtime
  INTO acrt.lametrolines
  FROM 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY vehicle, time) AS num FROM acrt.nextbus) AS loc_a
    JOIN
    (SELECT *, ROW_NUMBER() OVER (ORDER BY vehicle, time) AS num FROM acrt.nextbus) AS loc_b
      ON (loc_a.vehicle = loc_b.vehicle AND

mattwigway

from twisted.web import xmlrpc, server
from twisted.internet import reactor, defer

class rpctest(xmlrpc.XMLRPC):
    def xmlrpc_test(self):
        return "Hello, world!"
    
    
if __name__ = '__main__':
    r = rpctest()