Route Flags

Demonstration of a query to foster community around your regular commute. It’s a step toward filtering the noise to surface complex but meaningful information to individual cyclists. See image below maps. A big thanks to everyone at EchoHack and Vissuality for their help.

All flags

All flags posted by users (bike shops, pot holes, etc).

Flags on your commute

All flags that fall near a particular commute. A commute can have many different routes, but share a common region for start and finish. This helps filter all the noise and show you information really important to you. This example shows rides that pass thru Union Square and the Brooklyn entrance to the Manhattan bridge. Query details below.

Next: Search & Rider-centric, Mapless Mapping





Query

Find all rides that pass thru the start point (the start point expanded 250 meters) AND the end point (expanded as well). Collect all those rides as a single geometry called foo. Now find all flags that fall along the paths in foo (flags expanded 250 meters).
WITH foo AS (SELECT ST_Collect(the_geom) g FROM the_rides WHERE ST_Intersects(the_geom::geography, ST_Buffer(ST_GeographyFromText(\'POINT(".$start.")\'), 250)) AND ST_Intersects(the_geom::geography, ST_Buffer(ST_GeographyFromText(\'POINT(".$finish.")\'), 250)) ) SELECT * FROM route_flags, foo WHERE ST_Intersects(ST_Buffer(the_geom::geography,250), g::geography)