I've written my spatial query and it returns a an array of the 10 route lines that are closest to a point. My query retrieves the route number, description and distance in decimal degrees. I've projected the trimet data to the WGS84 projection, which is what we usually get from a GPS.

a note about projections and web mapping

I'm sure that the GIS people are nervous about my cavalier treatment of projections. I didn't mention anything about spheres vs spheroids or anything like that. If we were doing serious analysis it would be unacceptable to pay so little attention to the underlying data. However we are building a web app where speed and ease of use trumps accuracy. There are compromises that have to be made for this sort of thing. It is good to be aware of the tradeoffs, but I'm not too concerned.

a trimet node module and git branches

I've added a some node module to the repo to hold the server side application code for interacting with postgis and the trimet data. I haven't worried too much about organizing my code, yet, but it seems like a good time to impose some structure. I've also added a test directory and an integration test that actually runs the query against our database and verifies that we get the expected results. This will come in handy as things get more complicated.

git flow and development branches

You may also notice that the git repo contains a develop branch in addition to our master. The master branch holds our current stable deployed code, and new code gets added to develop. When I am ready to release I merge the develop branch into master to be deployed.  Git makes it easy to branch and merge.  I'm also using the flow extension to git to make it easy to manage the branches.  I highly recommend this approach.

trimet node.js module

In the development branch, I've added a new javascript file called lib/trimet.js.  It exports one function to our server that takes a latitude and longitude and returns a list of routes from the postgis database.  It is using the node-postgres module by Brian Carlson and used in production at places like yammer. This integration test demonstrates the use of trimet.getRoutesByPoint, our first module function. It uses mocha, an excellent node test framework that I wrote about in article on asynchronous tests with node and redis.

preventing sql injection attacks

Note that I am creating the query string by concatenating the coordinates. This opens us up to sql injection attacks and should raise a red flag for security. The node postgres module supports parameter replacement, but I was running into trouble with that. In order to prevent random sql from being executed on our database, I'm testing to make sure our latitude and longitude are actually numbers before passing them to the module. Really this method should fail if we pass it anything but numbers. We'll address that later.

Now that I've got the comment view more or less working, it's time to link it up with actual trimet routes.  I think we need an initial screen to show to users when they first bring up the app.  I imagine that people will be either riding the bus/max, or they will be waiting at the stop.  Seems like we need to start by showing routes that are closest the user's current location.

performing spatial queries in postgis

Our backend needs to support a method that takes a latitude and longitude in the commonly used WGS84 projection and return the routes that are closest to that point.  Just getting the nearest stops isn't useful because the rider may be actually travelling the route and not anywhere near a stop.  Luckily postgis makes it easy to select lines based on proximity to a point.  Please see the note from Regina in the comments for updated postgis functions. This query returns the following results when applied against the postgis table I created from the trimet shapefile projected to WGS84. Note that postgres requires that we double quote the column names because they are capitalized.

grouping results in sql

The routes returned look great for my current location. The data is accurate enough to differentiate between routes that run on the same street in different directions. If I were waiting at a bus stop, it would be a safe bet to assume that the bus I want is on the same side of the street as me. I still want to return other routes, but at this point I don't really care what direction they are going. I want to group the results and just return a single row for each route. We can achieve that goal by tweaking the sql. We just need to apply an aggregate to the distance and group the results by route. We could filter the results by distance, but there isn't really a need at this point.

putting it all together

I'm happy with these results. The next step is to wrap it in a method and expose it on our backend. Then I'll write a new Sencha touch view to request the data based on the coordinate returned from the geolocation api in the browser. Should be a snap.

After spending a day watching Sencha Touch screencast videos, I've got my comment view cleaned working and deployed to nodejitsu at http://seatmate.nodejitsu.com.  Basically the same code should make the chat and instant messaging views function as well.  Feel free to file bug reports on the seatMate github issue tracker. I've got a Sencha list view with an xtemplate bound to a json store.  There is a form panel that dispatches to a controller for posting messages to a single comment channel on the backend.  When the page loads, the datastore is prepopulated with the last 10 or so  comments on the channel.  The list view scrolls independently of the rest of the page. New comments entered through the form are added to the view immediately, but comments posted on other devices are not displayed automatically.  That would be what makes the feature that differentiates the comment view from the chat view.  I also need to tweak the css to make the comments smaller and have more of them on the screen at once. The app works on Android and iOS browsers, as well as desktop webkit browsers like Chrome, but has some trouble with Firefox.  Unfortunately that is a problem for all Sencha Touch apps. Development was good considering I also had to learn Sencha/ExtJS.  Unfortunately I don't have any javascript unit tests.  I have a hard time doing TDD when I'm using a new framework, but I'd be able to do it next time. I'm going to finish the app in Sencha, but I feel like it isn't the best approach for mobile web app development.  I'm writing a lot of code for what I'm getting and I prefer to build my interfaces in html rather than javascript.  The framework does provide a slick product and handles a lot of the mobile pain, though. I'd also prefer a solution that allowed the same app to run on mobile, tablets and desktops with responsive design.  Unfortunately sencha touch apps are relegated to the mobile ghetto.  But I feel like I'm making good progress overall.