mattmakesmaps

pgRouting Part II: PostGIS + Geoserver

Since compiling Orkney's pgRouting extension to PostgreSQL/PostGIS, I've decided to try my hand at creating a simple web interface to poke into the database. The current setup is as follows:

For the sake of brevity, (but really because both TOPP has created some fantastic guides) I won't go into the specifics of installing all the pieces. Just as an FYI, remember to set your 'JAVA_HOME' environment variable and make sure that you don't have things trying to use the same port!

The Bellingham data is currently stored in NAD83 State Plane WA North Feet, a typical projection for this area. This projection however, is not part of the EPSG projection set, and as such is not included in a vanilla install of PostGIS.

In order to add this to the collection of spatial reference systems used by my PostGIS install, I went with the ridiculously cool spatialreference.org site (A crschmidt, dbsgeo, hobu, and umbrella joint, hah). Navigating to the projection's page gives me the option to generate an INSERT statement, adding the projection's info into my database.

To load shapefiles into the PostGIS database, I chose to use the SPIT plugin for QGIS. Loading the data was fairly straightforward. I had an issue with a datefield that was present in the source shapefile, and had to delete the column manually using Open Office Database. I haven't found a way to delete fields from a shapefile using QGIS.

[caption id="attachment_327" align="alignnone" width="300" caption="The SPIT Interface"]spit[/caption]

After uploading the streets data into my PostGIS database, the next step was to transform the geometry into the Web Mercator 900913 Projection. This was done using standard PostGIS functions, adding a new, second, geometry column to the existing streets table. This reprojected data was then exported from my staging PostGIS database as a shapefile using the QGIS, 'Save As Shapefile' tool, and re-imported into my production database (with the routing functions).

With data stored in the web mercator projection, inside of our PostGIS/pgRouting database, the next step was to add the layers to Geoserver. Using Geoserver 2.x, the process included the following steps (all done through the web-admin).

After creating the connections between PostGIS and Geoserver, the creation of WMS services is taken care of, allowing us to roll them into OpenLayers with relative ease.

I guess this got a little off-topic from what I originally wanted to write about. I think that I'll save the actual breakdown of my OL code (taking a user's map click to and using it to calculate a route to the nearest fire-station as determined by manhattan distance, as opposed to euclidean distance) for another day.