Skip to content

Instantly share code, notes, and snippets.

@jnschaeffer

jnschaeffer/septa_postgis.sql

Created February 19, 2019 01:49
Show Gist options
  • Save jnschaeffer/931403a19d9c2e799c11b52b1abb7784 to your computer and use it in GitHub Desktop.
Save jnschaeffer/931403a19d9c2e799c11b52b1abb7784 to your computer and use it in GitHub Desktop.
Download ZIP
Some useful SEPTA GIS stuff
Raw
septa_postgis.sql
CREATE VIEW closest_vehicles AS
SELECT v.*, ST_ClosestPoint(r.wkb_geometry, v.geom) AS closest_geom
FROM vehicles v
INNER JOIN routes r
ON v.route = r.name;
CREATE INDEX vehicles_geom_idx ON vehicles USING GIST (geom);
UPDATE vehicles AS v
SET closest_geom = ST_ClosestPoint(r.wkb_geometry, v.geom)
FROM routes r
WHERE v.route = r.name;
EXPLAIN SELECT DISTINCT ON (v.id)
rs.id AS rsid, rs.geom AS route_segment_geom,
v.*
FROM vehicles v
INNER JOIN route_segments rs
ON v.route = rs.route
ORDER BY v.id, v.geom <#> rs.geom ASC;
/* segments without routes */
CREATE SEQUENCE segment_id;
CREATE TABLE segments (
id integer PRIMARY KEY DEFAULT nextval('segment_id')
);
SELECT AddGeometryColumn('segments', 'geom', 4326, 'MULTILINESTRING', 3);
WITH singlegeom AS (
SELECT ST_Union(wkb_geometry) AS geom
FROM routes
) INSERT INTO segments (geom) SELECT ST_LineSubstring(the_geom, .002*n/length,
CASE
WHEN .002*(n+1) < length THEN .002*(n+1)/length
ELSE 1
END) AS the_geom
FROM
(SELECT ST_LineMerge(singlegeom.geom) AS the_geom,
ST_Length(singlegeom.geom) AS length
FROM singlegeom
) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE .002*n/length < 1;
CREATE VIEW segment_lateness AS
SELECT sid, segment_geom, EXTRACT(hour FROM adjusted_time) AS read_hour,
COUNT(*) AS cnt, AVG(late_min) AS avg_late,
AVG(CASE WHEN ABS(late_min) < 5 THEN 1 ELSE 0 END) AS otp FROM (
SELECT DISTINCT ON (v.id)
s.id AS sid, s.geom AS segment_geom,
v.*,
((read_time::timestamp - (offset_sec * interval '1 second'))
AT TIME ZONE 'UTC') AT TIME ZONE 'EST' AS adjusted_time
FROM vehicles v
INNER JOIN segments s
ON ST_DWithin(v.geom, s.geom, 0.0001)
ORDER BY v.id, v.geom <#> s.geom ASC
) AS s
WHERE EXTRACT(dow FROM adjusted_time) NOT IN (0, 6)
GROUP BY sid, read_hour, segment_geom;
/* segments with routes */
INSERT INTO route_segments (route, geom) SELECT name, ST_LineSubstring(the_geom, .002*n/length,
CASE
WHEN .002*(n+1) < length THEN .002*(n+1)/length
ELSE 1
END) AS the_geom
FROM
(SELECT routes.name,
ST_LineMerge(routes.wkb_geometry) AS the_geom,
ST_Length(routes.wkb_geometry) AS length
FROM routes
) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE .002*n/length < 1;
CREATE VIEW route_segment_lateness AS
SELECT sid, route, COUNT(*) AS cnt, AVG(late_min) AS avg_late, STDDEV(late_min) AS std_late,
AVG(CASE WHEN ABS(late_min) < 5 THEN 1 ELSE 0 END) AS otp FROM (
SELECT DISTINCT ON (v.id)
s.id AS sid, s.geom AS segment_geom,
v.*
FROM vehicles v
INNER JOIN route_segments s
ON v.route = s.route AND ST_DWithin(v.geom, s.geom, 0.0001)
ORDER BY v.id, v.geom <#> s.geom ASC
) AS s
GROUP BY sid, route, segment_geom;
CREATE VIEW route_destination_segment_lateness AS
SELECT sid, route, destination, COUNT(*) AS cnt, AVG(late_min) AS avg_late, STDDEV(late_min) AS std_late,
AVG(CASE WHEN ABS(late_min) < 5 THEN 1 ELSE 0 END) AS otp FROM (
SELECT DISTINCT ON (v.id)
s.id AS sid, s.geom AS segment_geom,
v.*
FROM vehicles v
INNER JOIN route_segments s
ON v.route = s.route AND ST_DWithin(v.geom, s.geom, 0.0001)
ORDER BY v.id, v.geom <#> s.geom ASC
) AS s
GROUP BY sid, route, destination, segment_geom;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment