GeoDjango: Standing Up A GeoJSON Web-Service

The models are complete. The database is loaded with some test tabular and spatial data. We’re pushing out HTML representations of attribute data using GeoDjango’s standard templating functions. Now, the focus moves to visualizing these features’ geometries in a spatial context. Just as with a Django QuerySet, GeoDjango provides a GeoQuerySet. When paired with a spatially-enabled database (e.g. PostGIS, SpatialLite, etc.), the GeoQuerySet provides functionality for querying data using a series of spatial filters, in addition to tabular filters. As a point of reference, the GeoDjango docs have great tables depicting a blow-by-blow comparison of different spatial databases, displaying each available Spatial Lookup and GeoQuerySet method. Take note, PostGIS is the clear winner in terms of functionality ;)

Why GeoJSON?

From the perspective of exporting data, GeoDjango supports a number of formats. The GeoQuerySet methods can represent your model’s geometry column in a number of different formats: GeoHash, GeoJSON, GML, KML, and SVG. Of all these serialization formats, I’ve found KML to be the most frequently used amongst GeoDjango users. Illustrative of this, three of the four functions in django.contrib.gis.shortcuts have to do with KML/KMZ. That’s awesome, but where is the love for GeoJSON?

KML can be easily consumed by OpenLayers, the king of open source web mapping viewers. But some of the new kids, e.g. leaflet, polymaps, look to favor GeoJSON over KML as an input for dynamically rendered data, not directly consuming KML out-of-the-box. That being said, if you want KML, this fork of leaflet looks like it will work for you. In my particular project, I’m interested in using leaflet, so GeoJSON was the way to go.

Later on, I’d like to do some speed comparisons, rendering the same featureset using OpenLayers, represented as both KML and GeoJSON, but that’s for the future. I’m wondering if OpenLayers will handle the JSON object faster then KML’s XML? JSON is just JavaScript after all.

The Problem

The GeoDjango GeoQuerySet API has built in methods to handle the serialization and de-serialization of a result set’s geometries into different formats. The problem is that these methods only wrap the geometries of a result set. For display in a web mapping application, like leaflet, I want to have access to both the geometry in the format of my choosing, as well as the supplementary attributes (name, type, etc.) which provide context for that geometry.

For example, asking for the GeoJSON representation of a given feature through Django’s shell, like this:

# Import Models from the Company Application
from company.Models import *
# Create a GeoQuerySet from the primary key, return GeoJSON
qs = Boundary.objects.filter(pk=1).geojson()
# Print GeoJSON representation of geom
print qs[0].geojson

Will produce a GeoJSON object like this:

{
 "type":"MultiPolygon",
  "coordinates":[
    [
      [
        [
          -122.574295,
          47.856636
        ],
        [
          -122.573924,
          47.85718
        ],
        [
          -122.573719,
          47.85757
        ] // Truncated Verticies
      ]
    ]
  ]
}

As shown in the example above, the geometries are returned, but not the tabular attributes associated with that feature. Looking at the GeoJSON spec, there are multiple ‘type’ values which an object can be constrained by. Using GeoDjango’s geoJSON() method will produce a type matching the geometry listed in the associated GeoDjango model (point, line, polygon, etc). The distinction here is that I’d like to return a GeoJSON object of type ‘Feature’ or ‘FeatureCollection’. These types require an additional ‘properties’ parameter, which can store tabular attributes. From the spec:

A feature object must have a member with the name “properties”. The value of the properties member is an object (any JSON object or a JSON null value).

So, the trick now is to dynamically create a GeoJSON object which contains both populated Geom and Properties attributes.

The fix (vectorformats)

In order to create a fully populated GeoJSON object, we need to bring in some extra assistance. Some quick searching brought me to this stack exchange response, from Chris Schmidt. Chris’ vectorformats package handles the serialization and de-serializtion of a variety of formats, including Django Querysets and GeoJSON. From the project homepage:

The vectorformats library is designed to make it easy to serialize content from any source to any source within Python. Think of it as a “poor man’s OGR” – a pure Python implementation of transforming features to and from various formats (largely XML based).

Installing vectorformats is as easy as:

$sudo easy_install vectorformats

From there, as outlined in the above referenced post, it’s only a matter of adding a few lines into your GeoDjango app’s view function.

# Using vectorfeatures module return a GeoJSON FeatureCollection
# for a given boundary ID.
def boundary_detail(request, boundary_id):
    boundary_detail = Boundary.objects.filter(pk=boundary_id)
    djf = Django.Django(geodjango='geom', properties=['name'])
    geoj = GeoJSON.GeoJSON()
    s = geoj.encode(djf.decode(boundary_detail))
    return HttpResponse(s)

The resulting GeoJSON object, represented as a ‘type’ of ‘FeatureCollection’:

{
  "crs":null,
  "type":"FeatureCollection",
  "features":[
    {
      "geometry":{
        "type":"MultiPolygon",
        "coordinates":[
          [
            [
              [
                -122.574295,
                47.856636
              ],
              [
                -122.573924,
                47.85718
              ],
              [
                -122.573719,
                47.85757
              ] // Truncated Verticies
            ]
          ]
        ]
      },
      "type":"Feature",
      "id":1,
      "properties":{
        "name":"Port Gamble"
      }
    }
  ]
}

And there you have it, GeoJSON containing both the geometry and attributes. This output can now be mapped to URL, creating an endpoint such as ‘http://my-site.com/geojson/boundary/{boundary_id}/’. Pass this to your web mapping client, and you’re ready to rock.

Moving to GeoDjango

I’ve been creating a simple GeoDjango application for managing environmental sampling metadata, and it’s been a lot of fun so far. I’ve had experience working with many different forms of metadata tracking, from spreadsheets, to wikis, to online project management tools. All of them have their ups and downs, and it seems like there is always a dealbreaker with each organizational method.

Spreadsheets are easy to edit, but lack any form of relational structure (two sets of data for the same report? i guess i’ll just put two entries into the same cell).

sometimes spreadsheets just don't work...

Wikis are cool, allow easy access to information, but are (in certain cases) a pain for folks to edit. Take the experience of table creation. Dokuwiki, a generic wiki software, requires a series of  carefully placed carrots and pipes to delineate headers and columns. A common pain comes when adding a new value to a new row, in which that value exceeds the length of any previous cell. This requires the author to expand the column header, and all previously entered rows, re-aligning the pipes and carrots. Granted as a slightly OCD GIS Analyst, the sight of a well crafted text table fills me with no less wonder then when I saw Albert Bierstadt’s “Puget Sound on the Pacific Coast”, but it’s just darn tedious at times. Additionally, as the log of sampling events grows larger, it gets harder to manage. Dokuwiki, AFAIK provides no ways to automatically resort entire sections of pages or records in tables based on alphabetical order, which would make searching for information on a particular page much faster as content becomes larger and larger.

^ Column One                ^ Column Two                 ^
| Zee 'z' string            | This is a longer string    |
| 2nd longer string of text | 2nd short string           |
| SuperShort                | A string starting with 'A' |

Online project management tools are interesting as well. They allow rapid collaboration between project members, and provide template functionality, allowing for status reports on recurring workflows to be easily generated (e.g., create a template for a report, spawn off an instance of a template for each new project). The downside to these services are that: they cost money, they also may not provide a normalized way to store data, and (of most interest to myself) they probably don’t allow for the storage/visualization of spatial data.

10 megs free for one user? cool!

In comes GeoDjango. Over the next few posts, I think I’ll record my experiences developing an application that allows the storage of metadata, within the context of environmental sampling efforts. The goal is to provide a web application which stores both tabular and spatial data in a normalized fashion, and easily visualize both in an informative way.

Hey look Ma', it's GeoJSON!

Hand-Rolled Vector Tiles – TileStache

A few weeks ago I found myself surfing the intertubes for instructions on how to serve up some vector tile goodness. That search came up pretty much empty, except for one glimmering thread of hope. The answer, TileStache { <– Imagine that’s a mustache on it’s side.

TileStache is a Python-based server application that can serve up map tiles based on rendered geographic data.

By design, TileStache can be used to serve up stylish TMS tiles using mapnik map files, and can also be used to locally cache remote-services via proxy. What I’m most interested in though, is it’s ability to deploy vector tiles. So what are vector tiles? Think TMS tiles… but replace representations of the geometries through images, with GeoJSON. Pretty wild right? Specifically, the TileStache PostGeoJSON Provider can be used to connect TileStache to a PostGIS data source, and return a tile comprised entirely of GeoJSON data.

For example, data from a PostGIS data source can be rendered as an image tile (…/10/16/357.png), like this:

But can also be represtented as a vector tile (…/10/16/357.json), like this:

// Subset of a single 256x256 pixel vector tile.
{
  "type": "FeatureCollection",
  "features": [
    {
      "geometry": {
        "type": "MultiPolygon",
        "coordinates": [
          [
            [
              [
                -122.973093,
                47.969842
              ],...
              [
                -122.973093,
                47.969842
              ]
            ]
          ]
        ]
      },
      "type": "Feature",
      "properties": {
        "property_s": "USFS",
        "juris_name": "Olympic National Forest"
      },
      "id": 1280
    }
  ]
}

So what are the advantages of using vector tiles? You can already use OpenLayers’ GeoJSON format reader to populate a vector layer in OL. It’s an issue of size. Highly complex geometries can be large in size, and requesting all that data at once can be time consuming. Vector tiles approach this problem using the same answer as TMS… only request those sections of data which you need at that time. By only requesting those tiles within the user’s current extent + a small buffer, the need to download large geometries at once can be negated. Furthermore, just as TMS’s can be pre-cached to disk (seeded), so can vector tiles.

One example of this is serving up a combined NFS boundary dataset compiled by my good pal, Greg (http://www.chopshopgeo.com/blog/). These boundaries are dense and displaying them at their full extent & raw level of detail is expensive. But by breaking the vector representations of these geometries up into a standard tile scheme, only those tiles which we need are requested, and only when we need them. As a side note, in addition to tiling, I also simplified the boundaries, to promote faster load time at small-scales. The least granular vector representations display at the smallest zoom-scales, while the highest (raw, unsimplified) level of granularity displays only at the largest zoom-scales.

NFS Boundaries Provided By ChopShopGeo

Additionally, using vector representations of geometry rather then cached images allows styling of those geometries on the fly. Polymaps, the only display client I’ve found so far that can consume vector tiles out-of-the-box, renders these tiles as SVG elements. Because of this, unique styling can be applied via CSS; controlling the color, stroke, fill, etc. of each geometry in response to both attributes associated with the geometry (see image below) or user input… ala the Polymaps example page.

USGS real-time gauge stations. Darker dots represent stronger streamflow, lighter dots represent slower flow. You'll have to ignore the fact that I'm symbolizing streamflow without the streams.

The above example converts data from the USGS Instantaneous Values Web Service (part of the USGS Water Date for the Nation program) as a JSON response to GeoJSON. These data points are then symbolized dynamically using Polymaps. More on that later.

{
“type”: “FeatureCollection”,
“features”: [{
“geometry”: {
“type”: “MultiPolygon”,
“coordinates”: [
[
[
[-122.973093, 47.969842],
[-122.973093, 47.969842]
]
]
]
},
http: //jsbeautifier.org/
“type”: “Feature”,
“properties”: {
“property_s”: “USFS”,
“juris_name”: “Olympic National Forest”
},
“id”: 1280
}]
}

cURL’ing to FeatureServer from PostGIS: Easier then I Thought

So I’ve finished cutting a draft tileset using mapnik, depicting bus routes in Bellingham, WA. Now that the cartography is well in progress, I’d like to add some interactivity to the map. My first attempt at this will be to utilize MetaCarta (Chris Schmidt)’s FeatureServer. FeatureServer allows one to use standard HTTP verbs to GET representations of data, POST new data, or DELETE existing data. While querying data you can also pass additional URL parameters like a bounding box or attribute to select out a smaller subset of returned representations. I’ll be POST’ing a bus stop dataset to FeatureServer as GeoJSON. Once the data are stored in FeatureServer, I’ll be able to add popups based on a user’s click of a bus stop.

Getting data stored on my local PostGIS install to my remote FeatureServer instance turned out to be a three step process.

Step One: Convert local PostGIS bus stops layer to GeoJSON via OGR

I had originally planned on writing a pg/plsql function to try and output a bash script. The script would cURL each feature individually to my FeatureServer instance. This proved to be way more work then I had expected. What was the solution? OGR, of course. OGR has read/write drivers for both GeoJSON and PostGIS. This allows one to convert an entire dataset to GeoJSON with a single command (see below).

ogr2ogr -f "GeoJSON" ogrstops.json PG:"host=localhost dbname=routing user=postgres password=*** port=5432" "wtastops(the_geom)"

Step 2: Wrap “coordinate” elements in double brackets

When initially trying to cURL the GeoJSON output to FeatureServer, I was receiving an error stating that a bounding box could not be determined for the first geometry in my dataset. After some trial-and-error, I soon realized that the OGR output FeatureCollection was wrapping each point feature’s geometry in a single set of brackets. This type of behavior follows the GeoJSON specification for a FeatureCollection, as far as I can tell. However, in order for FeatureServer to consume this dataset, each point feature is required to be wrapped in a second set of brackets. I used gedit to run the find/replace. Below is an example of a GeoJSON feature which FeatureServer can consume. This individual feature is part of a larger FeatureCollection.


{ "type": "Feature",
          "properties": {
             "POINT_ID": "1000",
             "POINT_NAME": "Fielding at 32nd",
             "SHELTER": "Yes", "BENCH": "No" },
          "geometry": {
             "type": "Point",
             "coordinates": [[-122.474490,48.730021]]}
}

Step 3: cURL GeoJSON to FeatureServer

The last step is to actually POST the data to FeatureServer. For that, I used cURL.


curl -d @ogrstops.json http://mkgeomatics.com/cgi-bin/featureserver/featureserver.cgi/scribble/create.json

Now that the features have been uploaded, we can view them via FeatureServer as GeoRSS, KML, JSON, GML. Neat!

PL/pgSQL function to Iterate pgRouting

I’ve been working on a side project using pgRouting to determine the least-cost path across a street network from a given building to the nearest bus stop within Bellingham, WA. It’s one thing to execute pgRouting’s built-in functions for a single vertex (building) to another vertex (bus stop)… but another to have the function iterate through all buildings and their closest bus stop.

So that began my first experience with using PL/pgSQL. The benefit for using the procedural language for PostgreSQL lies in its ability to loop through collections of records easily. I’ve posted my function below. It’s not pretty, but it’s filled with enough notices to let me know where an error occurs, which helped me understand how things were acting each step of the way. Here is the basic idea:

  • Loop through a table in which each row has a source and destination vertex
  • Execute the pgRouting function using these two vertices, determining the length of the least-cost path.
  • Populate a field, ‘dist_calc’ with the distance.
CREATE OR REPLACE FUNCTION bulk_route_generate() RETURNS VOID AS $$
DECLARE
 bld_row bld_wtastops_staging5%ROWTYPE;
 dist_calc RECORD;
BEGIN
 RAISE NOTICE 'Beginning Function';
 FOR bld_row IN SELECT * FROM bld_wtastops_staging5 WHERE bld_wtastops_staging5.bld_vert_node IS NOT NULL
 AND bld_wtastops_staging5.wtastops_vert_node IS NOT NULL
 -- BEGIN ADDING BUM NODES TO SKIP OVER
 AND bld_wtastops_staging5.bld_vert_node &lt;&gt; 2915
 AND bld_wtastops_staging5.wtastops_vert_node &lt;&gt; 293
 -- ADD START GID
 -- USED ONLY IF BUM NODES EXIST
 --AND bld_wtastops_staging5.bld_gid &gt;= 29200
 ORDER BY bld_wtastops_staging5.bld_gid LOOP
 RAISE NOTICE 'Value of wtastops_vert_node is %. The value of bld_vert_node is %',bld_row.wtastops_vert_node, bld_row.bld_vert_node;
 RAISE NOTICE 'Value of wtastops_gid is %. The value of bld_gid is %',bld_row.wtastops_gid, bld_row.bld_gid;
 -- BEGIN STANDARD pgRouting A*Star FUNCTION
 SELECT SUM(cost) INTO dist_calc FROM shortest_path_astar('
 SELECT gid as id,
 source::integer,
 target::integer,
 length::double precision as cost,
 x1, y1, x2, y2
 FROM streets_9102748',
 bld_row.bld_vert_node, bld_row.wtastops_vert_node, false, false);
 RAISE NOTICE 'Value of dist_calc is %.',dist_calc;
 EXECUTE 'UPDATE bld_wtastops_staging5
 SET route_dist = ' ||dist_calc|| '
 WHERE ' ||bld_row.bld_gid|| ' = bld_wtastops_staging5.bld_gid';
 END LOOP;
 -- BAIL OUT ON ERRORS
 EXCEPTION
 WHEN CONTAINING_SQL_NOT_PERMITTED THEN
 RAISE NOTICE ' EXECPTION Value of wtastops_vert_node is %. The value of bld_vert_node is %',bld_row.wtastops_vert_node, bld_row.bld_vert_node;
END;
$$ LANGUAGE 'plpgsql';
-- EXECUTE FUNCTION
SELECT bulk_route_generate();

I’m excited at the possibilities that using PL/pgSQL offers in terms of manipulating data. I’m sure that the above function can be cleaned up quite a bit, too. If I ever have the need to re-visit this or similar problems, I’ll be sure to do some serious head-scratching to think about a better approach!

Here is an image of the resulting data generated using mapnik. Areas from dark green to light-green are within a 1/4 mile distance, while areas from yellow-to-red represent distances increasingly greater then a 1/4 mile. The large checkered areas are where the dataset failed to route. More on that at another time.

bld_sym_diverging_web

the result as seen in mapnik

pgRouting III: PHP + OpenLayers Interface

With the routing database configured and populated, and with geoserver rendering the WMS, now the focus can shift on designing the actual display and functionality.

The conceptual plan is as follows:

  • Extract the geometry of a user’s click on the map.
  • Pass the extracted geometry to a PHP script, via an HTTP GET request.
  • Use the PHP script to pass the geometry as part of an SQL query against the PostGIS/pgRouting database.
  • Return the geometry from the database as GeoJSON, and deserialize it into an OpenLayers vector layer feature.

The code to extract a user’s clicked coordinates was taken from this OpenLayers example. It was then modified to pass the xy coordinates to a second function, designed to create a URL which will execute a PHP script.

trigger: function(e) {
 var xy = map.getLonLatFromViewPortPx(e.xy);
 executeSQL(xy);
 }

Passing the XY variable to the executeSQL() function, we are able to now seperate out the individual X and Y coordinates, and apply them to their respective parameters in our URL string.

// Build the URL
 var json_url = "http://localhost/near_vertex_astar.php?";
 json_url += "x=" + escape(xy.lon);
 json_url += "&y=" + escape(xy.lat);

Having constructed the URL, we are now ready to use it to populate an OpenLayers vector layer with data.

// Make a fresh vector layer, pulling features from our script URL
 json_layer = new OpenLayers.Layer.Vector("GeoJSON", {
 styleMap: myStyles,
 strategies: [new OpenLayers.Strategy.Fixed()],
 protocol: new OpenLayers.Protocol.HTTP({
 url: json_url,
 format: new OpenLayers.Format.GeoJSON()
 })
 });

Alright! So where are we at right now? A user has clicked the map, and that click’s geometry has been extracted and sent to a PHP script on the server for further work. The PHP script will execute SQL in the PostGIS/pgRouting data base to do the following:

  • Find the closest vertex in our routing network to the user’s map click. This will be used as a source vertex.
  • Find all firestations within 5km of the vertex (which have been pre-attributed with the closest vertex on the routing network to their location).
  • Calculate the cost (as defined by total length of the route) from the source vertex to each fire station (really the routing network vertex).
  • Return back as GeoJSON only the geometry for the route with the lowest cost.

Why all the hassle with determining the cost? Can’t you just use PostGIS’ ST_DWithin() function to find the closet firestation to our user’s click and create the route? Well you could, but it might not always be the shortest route.

Euclidean distance versus Manhattan. Which one is shorter?

Euclidean distance versus Manhattan. Which one is shorter?

This behavior can be respresented in the routing network with the example below. Two different routes are generated from the same source vertex based on the combination of routing algorithm and account for route cost. On the left, the dijkstra algorithm is used to return the route to the closest fire station as the result of an ST_DWithin() query. On the right, the A-Star algorithm is used, and the route costs of all fire stations within a buffer are taken into account. As we can see, a different route and a different station are returned.

Comparing the two search algorithms and cost relationships.

A link to the JS and PHP scripts can be found at the end of this post. This definitely is not the most elegant solution to working with routing, but in terms of an experiment it was a great learning exercise. I’m really excited to dive deeper into PostGIS and pgRouting. The next step in the process will be incorporating OSM data, and adding in addition attributes which affect cost (speed limits, one-way streets, etc).

View the PHP.

View the OL JS.

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:

  • Display: OpenLayers
  • Renderer: Geoserver (via non-cached WMS)
  • Spatial Backend: PostGIS/pgRouting enabled PostgreSQL
  • Data: Public GIS data from the city of Bellingham, Washington’s GIS department.

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.

spit

The SPIT Interface

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).

  • Add the new data store pointing the PostGIS database.
  • Add new layers (resources) which point to the tables of interest in our PostGIS database.

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.

pgRouting On Ubuntu Netbook Remix 9.10

While working through Regina Obe and Leo Hsu’s PostGIS In Action I thought that I’d jump into the world of routing. My plan was to develop a sample application that could be used to plan bicycle routes throughout the city of Seattle. A quick google search proved that someone has already done it, and done it very well! http://www.ridethecity.com/ provides cycling routes using OSM data for many major cities, Seattle included.

Undeterred and inspired, i decided to compile the pgRouting set of tools for PostGIS and give them a whirl.

My primary tutorial for moving through the install and execution of functions came from the 2009 FOSS4G Tokyo & Osaka workshop entitled, “FOSS4G routing with pgRouting tools and OpenStreetMap road data.” Although my installation on Ubuntu Netbook Remix (UNR) 9.10 required a little different setup, this guide definitely got me 99% of the way there.

The majority of my installation woes were caused by the different pathways used on my UNR install of PostgreSQL vs. what are apparently the standard paths.

After attempting to execute cmake to compile pgRouting, I’d be presented with an error stating that the ‘POSTGRESQL_INCLUDE_DIR’ was not found. A locate command pointed me to the correct path for my PostgreSQL installation. By modifying the FindPostgreSQL.cmake file to search for the correct path, I was back in business.

Following the workshop instructions, I then attempted to create the database directly from the terminal, which yielded the following result.

matt@matt-netbook:~$ createdb -U postgres routing
createdb: could not connect to database postgres: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

After reading the documentation associated with “createdb”, i tried adding the “-h” flag pointing to “localhost”, which solved the problem.

The final error which I ran into had to do with the “$libdir” environment variable. While trying to register the pgRouting functions in my new database, I’d be presented with the following:

psql:/usr/share/postlbs/routing_core.sql:32: ERROR:  could not access file "$libdir/librouting": No such file or directory
psql:/usr/share/postlbs/routing_core.sql:43: ERROR:  could not access file "$libdir/librouting": No such file or directory
psql:/usr/share/postlbs/routing_core.sql:53: ERROR:  could not access file "$libdir/librouting": No such file or directory

Getting impatient at this point (i wanted to route!) I modified the SQL files to reference the explicit path of my PostgreSQL lib directory. Once that was done, I had a working routing database!

Loading the sample data, creating the indexes, and executing the queries was amazingly straightforward. To test visualizing the data, I exported one of the tutorial queries directly into a new table.

SELECT * INTO export
 FROM dijkstra_sp('ways', 10, 20);
qgis_routing

The route depicted in red as seen in QGIS.

Just for kicks, I tried exporting the data as GeoJSON and visualzing it via OpenLayers.

The following SQL query aggregates the exported line segments into a single GeoJSON object:

SELECT ST_AsGeoJSON(ST_UNION(the_geom)) AS geom_union
FROM export;

Using the vector-formats OL example, which displays GeoJSON in either EPSG 4326 or 102113, I was able to visualize the line segment with no problem.

GeoJSON representation of line segment generated using pgRouting, displayed in OpenLayers

Well that’s all for one day. So it looks like the bike riding app is out, but I’m sure that there will be many more interesting ideas for pgRouting that will come to mind as I continue to explore PostGIS.

ESRI UC Student Assistant, Sweet!

Just found out that I’ll be participating in the 2009 ESRI UC as a student assistant. Big thanks go out to Dr. Robert Balling and James Fee, who both wrote letters of recommendation for me.

Now to get back to more pressing matters, like refactoring this giant wad o’ javascript.

See you in San Deigo!

Visualizing An Existing MySQL Database

So I’ve been working for about a month with a fairly-normalized (53-table) database in which I draw out all kinds of tabular information, and display it in a spatial context. This has required the numerous multiple table joins, with all kinds of weird relationships… you know, the kind that usually don’t work out very well?

In any event, my SOP for handling these queries was to submit sample data through the codeigniter site that our project’s web developer has been courageously firing away at. In this sense, I’d sort-of trace the flow of new information through the various tables of the database, monitoring the information stream as best as I could. I thought to myself, that there has to be a better way to handle this stuff! In comes the MySQL Workbench. This handy tool from the MySQL Dev Zone apparently comes in two flavors: FOSS and commercial.

The free version served my visualization needs perfectly. The layout of the program is very solid. I was easily able to take an SQL export of the existing database and import it into the Workbench, through a tool they call ‘Reverse Engineer MySQL Create Script’. Once the schema has been injected into the program, a model can be automatically created containing all of the tables as well as relationships. The auto-layout feature however, leaves a lot to be desired.

Above: Automatic Layout Results, Snazzy!

Above: Automatic Layout Results, Snazzy!

After about twenty-minutes of fooling with the table graphics, a usable layout can be produced. One feature that I think is really convenient, but will never use, is the automatic setting of the diagram width and height based on numbers of pages. This is useful for those who need a quick-print out of their database for whatever reason.

Above: Workbench w/ Completed Diagram

Above: Workbench w/ Completed Diagram

The real benefit for me however, is the automatic highlighting of key values linking tables together. I’m now able to quickly work my way from the table I need to get to, drilling backwards until I see the table I need to start with.

Above: Automatically Highlight The Key Fields Between Two Tables.

Above: Automatically Highlight The Key Fields Between Two Tables.