What the Agile Alliance Australia should be

Posted in agile, General on January 29th, 2010 by mark – 2 Comments

The Agile Alliance Australia (A3[*1]) was formed at the Agile Australia 2009 conference with and an interim board. The goal of the interim board, as I remember it, was to setup the foundations for A3 and quickly hold another election (hence ‘interim’) to transition to a fully functioning body. The A3 did start to talk about what it should look like but it hasn’t progressed which is why I’m writing this entry.

I think the A3 should be:

  • a small group of people – until more are needed.
  • have a primary goal of co-ordinating a yearly, national conference
  • have a secondary goal of supporting local chapters.
  • have a tertiary goal of being a centralised point for resources

Supporting local chapters and acting as a centralised point were discussed at the original meeting.

Small Group

Keeping the A3 board small will keep it easy to co-ordinate and should remove some of the burocracy. The downside of a small board is that they’ll be kept pretty busy. I really don’t want to create a large, beurocratic, unwieldy beast.

A National Conference

This is a lot of effort but also has very big rewards. The benefits of having a national conference are:

  • it gives our community a place to congregate for learning and networking. Without it we are an uncollected group of local communities which would make it harded to achieve larger goals. We have a lot of very talented individuals in Australia and we should be proudly showcasing them. Agile Australia 09 and Agile 09 were two of my favourite events because it humanised my community. Sure, I’ve interacted with them over mailing lists, but meeting someone and having a face-to-face discussion is much more rewarding.
  • gives us a forum to introspect over the past year and set goals for the upcoming year for our community.
  • it is a lot of fun – surely that counts!

Support Local Chapters

The local communities have lots of advantages.

  • They are natural congregation points.
  • They *are* the agile community
  • They should already be talking about what agile is and how it is evolving.

A3 should not look to replace these groups, but should look to augment and encourage them.

Centralised Point for Resources

At the very least I think the A3 should provide a central Australian point for discussing local issues. This can be done very simply with a mailing list/google group (like this one!) and perhaps a wiki. I would see us central point for visiting and local agile enthusiasts to find out what is going on and get in touch with their local chapters. I don’t see a mailing list for this group replacing other, more authoritative lists, such as Ken Schwabers yahoo mailing list (just to pick one off the top of my head).

Money, Money, Money

It depends on how we want to run this thing but I can see money being kinda useful! The two big items are support for local chapters and running a national conference.

Have a pool of cash would allow A3 to support the local chapters so they can put on some food at a local event, hire a venue or buy other sorts of expenses (I’m not in favour of purchasing assets such as equipment, books, video cameras, etc).

The other advantage to having money is that we could properly co-ordinate a national conference. Up front money is needed to hire a venue and get the conference ball rolling and I think it is unreasonable for an individual to be out of pocket for that sort of expense (even if it *theoretically* only temporary).

How do you get money? Sponsorship? Membership? They all have their pros and cons. I’m in favour of charging a corporate membership fee (but not for individuals). This way corporates can show their support of Agile community and we get some money for it. Most likely a corporate would use their support as a way of validating to their customers they are serious about agile in Australia.  This is only slightly different to the original discussion.

If we feel that individual paid memebership is deemed useful, then I’d like it to get members a discount to the national conference.

I’m expecting this to be the most controversial part of my proposal.

What is shouldn’t be

I’d like to note what I do not thing the A3 should be:

  • only a group of large corps – I’d hate the only way to get represenation on the board to be via a large corp that doesn’t speak to individual’s interests
  • a certification body

Relationship to *the* Agile Alliance

I see A3 as an entirely separate entity to the Agile Alliance. It may be useful for both groups to have reciprocal benefits, but in their essence they are separate bodies with no jurisdictional overlap.

Why and what now?

If we don’t act now I’m worried that a non grass root group will claim to be a centralised body. I don’t want an unauthentic body being set up to dilute what “I” think an agile body should be.

Rich Durnall has setup a meeting of the Melbourne Chapter of A3 and some of these topics are coming to the surface. I’m going to be at the meeting on Tuesday to talk through my proposal. At the moment it is all booked out (120 people have signed up).


This is my vision of what I think A3 should be. I’m ready and waiting for you feedback. Let’s co-orinate the feedback over on the A3 mailing list.

*1 – I thought A3 sounded better than AAA

Agile 2009

Posted in General on September 2nd, 2009 by mark – Be the first to comment

About four months ago we realized that Agile Bench was going to be ready at around the same time as Agile 2009 so I booked my tickets and then hunkered down to get a minimum viable product (MVP) in order to collect feedback from the elite of the agile world. I was truely lucky to meet so many amazing people and attend some tremedous talks.

The keynotes from Alistair Cockburn (“I Come to Bury Agile, Not to Praise It“) and Jared Spool (“The Dawning of the Age of Experience”) were fantastic and I hope they’ve been recorded for everyone to see. I spent a lot of time learning how others deal with the entire life cycle of an agile project.

Luke Hohmann’s (Enthiosys) work in the product management space (“Prioritizing for Profit” and “Beyond features: How to listen to your customers and learn what they really need“) and Jim Highsmith’s “Advances in Release Planning” were especially exciting to me. These speakers did an amazing job of reminding the agile community that agile projects are still projects (gasp!).  Planning is often considered a dirty word to agile teams but doesn’t need to be.  When working on a project we are trying to turn assumptions into facts and we use lots of different techniques

are still project that need plans and structure. As the agile community matures it will need to embrase a lighter form of planning and strategy which will allow it to be successful at multiple levels of the organization and not just limited to the delivery groups.

As usual, the people are what makes a conference. Rich Mironov has lots to say about the Scrum Product Owner role and the organization role of a Product Manager. Mario Moreira has lots to say on Software Configuration Management (SCM). I ended up having breakfast with a bunch of people including Gerard Meszaros, author of “xUnit Test Patterns: Refactoring Test Code” who was just a generally nice guy. I was lucky enough to have lunch and hang out with with Ola Ellnestam and Henrik Kniberg (“Scrum and XP from the Trenches“) who were a bunch of fun. There are so many others Cory Foy, Jurgen Appelo, Steve Hayes, Erik Petersen, Gabino Roche, Lisa Crispin, Abby Fichtner (Hacker Chick), Craig Smith, Esther Derby and many, many more.

Is the Product Backlog an Idealog?

Posted in agile on April 10th, 2009 by mark – 6 Comments

What is a product backlog?  I think most people who are working to the agile ethos will agree with the Scrum Alliance’s Guide description as “… the master list of all functionality desired in the product.” This definition is open to interpretation.

It’s that bit about “all functionality desired” that gets me.  Does this include everything that your product owner says they want?

Approach A – All ideas live in the backlog

The most common viewpoint I’ve come across is that the product backlog is everything the product owner has expressed an interest in.  I don’t know about you, but not everything this type of backlog is estimated in points.  You know the deal, the backlog gets big quickly with an enthusiastic product owner waxing lyrically about their dreams for the system, making the cost of fleshing out all stories pretty expensive.  There is nothing wrong with the product owner doing this, after all they are telling you want the want which should be a good thing.  To deal this the barrage of information we end up with epics and themes.  Both of these concepts will be decomposed into many user stories as we discover more about their purpose, but in the mean time they are placeholders of functionality to be built “later”.  The other side affect of a long backlog, epics and themes is that your backlog is strictly prioritized at the top and loosely prioritized at the bottom.  The advantages of this style is that you have all of your ideas in one place – a complete view of what the product owner has requested.

Approach B – The backlog represents commitment

The alternative is that your backlog only contains user stories that your product owner has committed to.  Commitment means that the product owner is prepared to pay for these stories.  Paying means spending the time and effort to think through each of the stories in detail and go through the estimation process.  It also means prioritizing all of the user stories.  “Nice to haves” and “maybe one-days” don’t end up there.  Instead another bucket is needed to hold future thoughts, I call this and Idealog.  In there you put features, epics and stories that wont make it in because of resource constraints (time and money) that you want to get to but you are not not ready for yet.

I think these two views of the product backlog have evolved for projects with different drivers.

When you are project oriented

Well defined projects have a specific goal they are trying to achieve, usually withing a fixed time or price.  The projects I’ve worked on end up with all of your ideas in the backlog, i.e. approach A.  If your product owner is very enthusiastic and generates lots of features it will become quickly apparent what will roughly fit within your constraints.  This means you can have a long list of everything your product owner desires and you can draw a line through the product backlog where you run out of resources.  It doesn’t matter if items at the bottom of your product backlog are a bit fuzzy.  This is how the “Big Businesses” I’ve worked for (like banks) work.  i.e. a business case is created, you get funding to create the business case, and you satisfy the goals within your budget and wind down the project.

When you are product oriented

Sometimes though you work on a product which does not have an end.  It, like your project, has a goal but the goal is usually nebulous or grander in vision.

Many start-ups I’ve worked at have a business model that is continually evolving.  Not only do they not know what they need in two months, they want to continue development for years (if all goes well of course).  In this case your backlog looks very different.  If you add every feature your product owner has contemplated, then your product backlog can become enormous.  One company I worked in had accumulated 2,000 stories after two years (including features and bugs) – the result was to delete the entire backlog and start again.  The logic was that if it was important then it wouldn’t take long to resurface (yeah, it was out of control and shouldn’t have got to that state).  Seriously, who can manage a backlog with so many ideas in it?  This is where an Idealog comes in.

An idealog should contain high level ideas to be debated, analyzed and decided whether they show enough merit for the product owner to commit to.  Until then, let them swim around in the primodial soup until they grow legs and walk into the backlog.  The backlog contains the details which are ready to go and the Idealog contains longer term plans which may or may not be done.

Who cares about a definition anyway?

The Agile Manifesto says nothing about a product backlog.  XP talks about a release plan but nothing more.  As I’ve mentioned earlier, Scrum talks about a product backlog pretty loosely.  But really, what does it matter?  If you can deliver what your product owner needs in a way that makes them happy then your doing something right.  Choose the right approach for your needs.

Personal experience

I’ve used a product backlog in both ways and they both have merit.  I prefer using an Idealog, I think it keeps the backlog clearer but I’ve used both methods effectively.

What do you do?  What do you think?

Yellow Lab in Desktop Magazine

Posted in General on March 19th, 2009 by mark – Be the first to comment

desktop_coverMatthew Magain from Desktop magazine wrote an article about the work we are doing at Sensis in the Yellow Lab project of which I’m the technical lead. The Lab‘s purpose it to explore new business models, user experiences and technologies and the article goes on to talk about the things we’ve done.

We are honored that Desktop Magazine thought highly enough of our work to showcase us. Thanks to Matt and the whole desktop team.

Go on, try out Yellow Lab.

My WordPress install was hacked – oh no!

Posted in General on February 3rd, 2009 by mark – 3 Comments

My WordPress 2.6.1 install was hacked a few weeks ago and it was serving up advertisments for all kinds of dubious products. I noticed this because my friends were telling me that FireFox had marked my site as malware. Great. 50% of the people I polled got the error when checking out my site. I dug into the page source from the browser and found a long list of links to other peoples WordPress blogs like (I’m going to use a link to my blog instead) “<a style=”display: none” href=”http://stateofflux.com/page.php?id=discount+viagra”>Discount viagra</a>” – oh no, my site is compromised.

What happened

The attack inserts content into your WordPress database as RSS links. When your blog is rendered the RSS links are inserted into the middle of your page with CSS telling the browser to hide the display of the links. This way the malware content can be indexed by search engines but doesn’t show up to normal readers. I’m assuming with many sites compromised and all linking to each other for legitimate sites that the links would rise to the top of your search engines results – a malware networks. I’m not sure if it worked, but it is annoying.

Physical side effects

There were three side effects

  • links to malware appeared in the middle of my pages
  • iframes to malware appeared after the closing html tag on all of my pages (PHP and html)
  • a new wp_options record was created that held the RSS content. The content of this record were json(?)/serialized links to other compromised sites

The fix

I think this vunerability was cleaned up in WordPress 2.6.5, but I’m not sure as I can’t find anyone else talking about this issue.

What I did was:

  • looked at the source of my wordpress page and found the links to other sites. They are pretty obvious as there is a really, really long line with words like viagra, soma, etc… let’s assume it was ‘soma’ (and I’ve got a feeling that doesn’t mean South Of MArket)…
  • log into my database (mysqladmin or mysql client) and look in the wp_options table
> select * from wp_options where option_value like "%soma%" \G;      /* where 'soma' is the malware word I found on the page source */
  • remove the record
> delete from wp_options where option_value like "%soma%";
  • reinstalled WordPress (I just upgraded to 2.7 at DreamHost, who move the directory aside anyway)
  • reinstalled all Plugins/Themes for their original source


The problem seems to have gone away. This is going to make me much more diligent with minor upgrades to my site. This has cause me a lot of pain. I’m going to email everyone who links to my site with this exploit in the hope that we can slowly stamp it out. Please pass on the message.

Note: I didn’t document these activities as I went along, and my memory is a bit vague on this so I’m writing the solution from memory. Please add comments to clarify anything that I’ve got wrong or missed.

Dr Fail

Posted in Uncategorized on January 6th, 2009 by mark – Be the first to comment

Yes, this is real.

Removing non-english characters

Posted in General on December 14th, 2008 by mark – Be the first to comment

Like many workplaces we have lots of info stored in spreadsheets.  In my case we have a spreadsheet that we need to import into a database which, in the simple case, is pretty straight forward.  But in this spreadsheet there are non-English characters.  You know the ones, e acute (é) for café and rockdots for the hardcore Motörhead fans.  For my purposes I need to convert these into their English equivalent as I’m trying to represent user input. It should be no surprise that English speakers do not enter é when they are looking for cafes.  The process of making these changes is called transliteration and I don’t want to do it manually.

Enter iconv

iconv is an awesome piece of software which converts character strings from one character encoding to another.  iconv also has transliteration built in.  This will allow me to convert those fancy foreign “cafés” into bog standard “cafes”.

$ echo 'café numero uno' | LC_ALL=fr_FR.UTF-8 iconv -t ASCII//TRANSLIT
cafe numero uno

In this example I set the locale to be French using a shell environment variable of LC_ALL then let iconv do it’s magic.

Microsoft Excel

My source dataset is in Excel 2003 format and I need to load this spreadsheet into PostgreSql in ASCII format (even though my db is in UTF-8 – remember that I’m trying to emulate user input).  If you export you spreadsheet in CSV format you lose all that nice non-English encoding and iconv will have nothing to work with.  Instead I export as Unicode Text (_File, Save As..,  Save as type: Unicode Text (*.txt)_) and scp it up to my linux development box.  Once there I can check it’s type by issuing a:

$ file test_cases.txt
test_cases.txt: Little-endian UTF-16 Unicode English character data, with CRLF, CR line terminators

The file is now a tab delimited text file and I want a CSV (comma separated) file.

Putting it all together

The final steps are to transliterate the file to ASCII and then convert the text file to a CSV.  I’ll use sed to translate tabs (\t) into commas. This line does the trick:

LC_ALL=fr_FR.UTF-8 iconv -t ASCII//TRANSLIT -f UTF-16 test_cases.txt | sed -e 's/\t/,/g' > test_cases.csv

There are more ways to get this stuff wrong than you can point a stick at, so I’ll put a disclaimer right here to say that this works for me just fine! :)

Google and Sensis – The reports of my death are greatly exaggerated[2]

Posted in Home on November 4th, 2008 by mark – 2 Comments

These are my opinions of what is going on and not those of Sensis.

Fairfax (The Age and Sydney Morning Herald) are running a story titled Sensis concedes defeat to Google2. It’s a great headline, but it is very misleading. There are two facts in the announcement – syndication and search.


Yellow Pages Australia is syndicating it’s data. They’ve had a commercial aggreement with NineMSN for a very long time which allows Yellow Pages data to be used within the NineMSN search. The new announcement extends the syndication of Yellow data to include GoogleMaps Australia.

If you are a Yellow Pages advertiser this means your advertisement can be found in more places. Therefore the Yellow Pages ad you bought is now even more valuable. Win!

Search on Sensis.com.au

Here is a nice quote from previously mentioned FairFax2 artcile – ‘and abandon its own search engine for one powered by Google’. The site in questiohn, sensis.com.au, currently has searches of all of the sensis properties – yellow, white, trading post AND the web – a one stop shop for searching the Sensis properties. Outsourcing the web search on Sensis.com.au makes perfect sense as Internet search is not Sensis’ core competancy. If you are going to outsource your search it should be to someone who knows how to do it like Google, Yahoo or Microsoft. Sensis went with Google. Not too shocking really.

The Yellow Pages, to my knowledge, will remain powered by yellow.com.au which is build by Sensis in Melbourne.

1 The reports of my death are greatly exaggerated

2 Sensis concedes defeat to Google

Yellow Lab – Sensis innovates

Posted in Home on October 28th, 2008 by mark – 3 Comments

I’ve been at Sensis for a year now and my latest project is Yellow Lab . The Yellow Pages is a significant part of the Sensis and Telstra portfolio earning 1,273 million dollars in revenue (p34) during 2007/8 which includes both print and online revenue. Yellow Pages is a product that you only change when you are absolutely sure you’re going to make it better. Labs was created to trail ideas for Yellow Online and mitigate risk in delivering new features. The ideas we trail are product, technical and user experience related.

It’s built on Rails

This is the first public site that Sensis has built in Rails. The momentum has been building within Sensis for a while but nothing of this scale has made it out to the public. Rails was chosen because it allowed Sensis to get to market quickly. From development to deployment, Rails has been a win. We’ve had many developers rotate through the project already, all of them have picked up Ruby and Rails very quickly with more experienced devs passing on their knowledge. That’s a win for Ruby and Rails .

Microformats everywhere

The Yellow Pages has a lot of data. Every single search result, and listing, is microformatted. I’ll be writing a separate post about this very soon, but it is really exciting stuff.

Building a search engine

We put a lot of time and effort into search. It’s hard and it’s fun. It also crosses all elements of the business from product management to user experience to technology. It is certainly more complicated than performing LIKE queries on a database or installing Sphinx or Solr . Business rules, physical locality, voting and tags all need to contribute towards getting a good result.

Aggregated Listings

Yellow Pages is a directory at it’s heart which lends itself to browsing rather than searching. Let’s say you’re looking for hardware in Port Melbourne. In a print world you’d flick open the book, look for the hardware category and then thumb your way through until you find a business that works for you. Big businesses like Bunnings would list themselves under hardware, but they may also list themselves under Nurseries as they also sell plants. That’s an awesome solution to the book because Bunnings can now be found in several places throughout the book. That’s a win for the business advertising and its a win for the user as they can find what you are after more easily.

When directories put their info online you usually have a modified browse experience where you have to select a category and then you can search within a category. For example, you say your looking for a Hardware store in Port Melbourne and find that Bunnings turns up. This is the way the core site works.

We wanted change the experience of finding businesses from a directory browsing experience to an internet style search. One hitch, if you put these listings online and search for Bunnings in Port Melbourne you’ll now find two entries (actually 5 on the site), one for Hardware and one for Nurseries – and that sucks if you are looking online. So we’ve aggregated listings together that are from the same businesses using some jiggery pokery to provide a single view of a business. It’s not perfect but it makes a big difference – it definitely solves 80% of the problem.

Sweet, sweet maps

Whereis has freaking good maps, despite Google having customer mind-share on maps. You can zoom in and see buildings with names and they have map data that gets updated regularly. You can send a location right to you GPS device, but in general, it is really well tailored to the Aussie market. Besides they look much nicer than the current Yellow Pages maps and so we’ve put them on Yellow Labs .

User generated content

Dude… We are so Web 2.0 – finally. We’ve got tagging (which we index immediately in our search engine so you can find em quickly) and user ratings in the form of “WOMming”. The tech is straight forward, and the features may seem obvious but how does this fit in to being an innovation platform? Finding out how things work in the real world is hard and the questions we are asking are more social. So we’ll build it and test if and how people use it? Do we moderate tagging contect? How? Does a ‘positive’ only recommendation work? Let build something and find out.

The future starts now

We have just dropped the very first iteration of Yellow Lab. It is rough and ready. This is the beggining of the adventure, not the end. We are going to put new features in and rip out features once we’ve finished learning from a feature. Trying out a new idea, measuring its success and deciding to go forward – or to throw it away – helps the business make better decision. There is some good stuff coming down the pike. Keep coming back to the Lab on a regular basis to check out what is new as we update the site several times a month – and please send us feedback.

Australian GeoSpatial Data – Free

Posted in Home on October 19th, 2008 by mark – 6 Comments

Edit: There are notes in the comments from Tim that explain the changes for PostgreSql 8.4.  Thanks Tim!

I’ve built a couple of sites that needed geospatial data. One was a social networking site that needed a way to list people who were near other people, the other was a art web site that allowed users to upload steet art and show it on a map. I thought it would be interesting to get the basics of an Australian suburb dataset up and running in a geospatial database and do some simple queries.

Install PostgreSql and PostGIS

First thing to do is setup PostgreSql and PostGIS. I’m sure you can do this in MySQL but I haven’t done it, so leave a note in the comments if you get that up and running :). There are a few article on how to do this and it is platform specific so go and do that.

Get some Suburb data

Now we need some data. The ABS is kind enough to provide Australia broken down into suburbs and postcodes on their site. I’m going to deal with suburbs so go ahead and download the State Suburbs (SSC) 2006 Digital Boundaries in ESRI Shapefile format data cube. This data cube has every suburb in Australia defined as a Polygon (or a multipolygon) with each node defined as a latitude and longitude.

Converting it to SQL

Unzip the downloaded shapefile and you’ll get 8 files but we are only concerned with the SSC06aAUST_region.* ones. We are going to load the POA06aAUST_region data into the database but firstly we need to convert it into SQL.

shp2pgsql SSC06aAUST_region.shp suburbs -s 4283 -I -d > suburbs.sql

shp2pgsql converts the ESRI Shapefile into SQL. -I adds an index (which is very important for speed) and the -d Drop and recreates the table. The -s 4283 make sure the suburb data is defined in with the correct projection. The earth isn’t a sphere and different parts of the earth are curved slighly differently so the geo-bods came up with a whole bunch of projections. 4283 is the standardized number for the GDA 1994 projection which is the projection the suburb data comes in (you can just take a peek inside the POA06aAUST_region.prj file to see what the project is).

Create a Geo-enabled DB and load the data

createdb australia
createlang plpgsql australia
psql -f /opt/local/share/postgis/lwpostgis.sql -d australia
psql -f /opt/local/share/postgis/spatial_ref_sys.sql -d australia
psql australia < suburbs.sql

Note: The directories for the lwpostgis.sql and spatial_ref_sys will vary from system to system so you’ll have to find them on your own machine.

You will also want to create a reference table for the Australian States

create table aust_states (id integer primary key, state_name varchar, state_abbrev varchar);
insert into aust_states (id, state_name, state_abbrev) values (1, 'New South Wales', 'NSW');
insert into aust_states (id, state_name, state_abbrev) values (2, 'Victoria', 'VIC');
insert into aust_states (id, state_name, state_abbrev) values (3, 'Queensland', 'QLD');
insert into aust_states (id, state_name, state_abbrev) values (4, 'South Australia', 'SA');
insert into aust_states (id, state_name, state_abbrev) values (5, 'Western Australia', 'WA');
insert into aust_states (id, state_name, state_abbrev) values (6, 'Tasmania', 'TAS');
insert into aust_states (id, state_name, state_abbrev) values (7, 'Northern Territory', 'NT');
insert into aust_states (id, state_name, state_abbrev) values (8, 'Australian Captial Territory', 'ACT');
insert into aust_states (id, state_name, state_abbrev) values (9, 'Other Territories', 'OT');

Get some awesome answers!

Show me the polygon of Port Melbourne

select name_2006, astext(the_geom)  from suburbs where name_2006 = 'Port Melbourne';

This returns a whole bunch of lat and longs. Pretty useless really. Maybe having the center of a suburb would be more useful.

Show me the center of Port Melbourne

select name_2006, astext(centroid(the_geom))  from suburbs where name_2006 = 'Port Melbourne';

   name_2006    |                  astext
 Port Melbourne | POINT(144.921987367191 -37.8328692507562)
(1 row)

Much better!

Show me the suburbs that surround Port Melbourne

select surrounding.name_2006
    from suburbs source, suburbs surrounding
    where source.name_2006 = 'Port Melbourne'
        and touches(source.the_geom, surrounding.the_geom);

 Albert Park
 South Melbourne
 West Melbourne
(7 rows)

Here I select the suburb table twice, once to represent it as the source suburb, in this case Port Melbourne and as a destination or surrounding suburb. I then restrict my matches to only show polygons that touch the source.

Show me the suburbs that surround Port Melbourne with distances between suburbs

select surrounding.name_2006,
    From suburbs source, suburbs surrounding
    where source.name_2006 = 'Port Melbourne'
        and touches(source.the_geom, surrounding.the_geom);

    name_2006    |     distance
 Albert Park     | 3908.06472236311
 Docklands       | 2316.21021732757
 South Melbourne | 3106.68573231296
 Southbank       | 3492.93829708397
 Spotswood       |  3035.6283677131
 West Melbourne  | 2682.84381789969
 Yarraville      | 3914.04324956383
(7 rows)

The interesting part here is getting the distance between suburbs. The distance() method gets the distance between two points, which for us is the 2 center points of our suburbs. Unfortunately if you measure the distance you’ll get an answer in degrees which isn’t that useful. So you need to transform the projection from a degree (lat and long are in degrees) to a meter based projection . Australia happens to have one called the Lambert Conformal Conic projection known as number 3112. Hence:


will get the distance, in meters, betwwen two suburbs.

Show me all the suburbs named Richmond

select name_2006,
    from suburbs
    inner join aust_states on suburbs.state_2006 = aust_states.id
    where name_2006 = 'Richmond';

 name_2006 |   state_name
 Richmond  | Victoria
 Richmond  | South Australia
 Richmond  | Tasmania
(3 rows)

What’s next?

This is all very nice, but when you start geocoding data and getting lat/longs of items you can store in the db then you can do some really fun stuff. If this article generates enough interest I’ll follow up with some Ruby code and Google Maps integration.