using postgis with node.js
15 Jan 2012
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
. 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.