Stap isi

2 August 2009

Putting NSW Local Government on the map

I fancied a crack at Google Spreadsheets after reading some interesting posts about it this weekend. Bit lame but the best idea I had for a data source was the directory of councils at the Department of Local Government. Here’s the map I made.

Dapper

I’d hoped to import a table into Google Spreadsheets (as per this example) but the directory of councils is marked up as very basic HTML. No block level elements! Still, it’s reasonably consistent. Here’s an example:

<SPAN CLASS="txtStandardTitle"><A NAME="71"><BR><BR>The Council of the City of Botany Bay</A></SPAN><BR><BR> 
PO Box 331, Mascot NSW 1460<BR><BR> 
141-143 Coward Street, Mascot NSW 2020<BR><BR> 
<B>Phone: </B>02 9366 3666<B>   Fax: </B>02 9366 3777<B>   DX: </B>4108 Maroubra Junction<BR><BR> 
<B>EMail: </B><A HREF="mailto:council@botanybay.nsw.gov.au">council@botanybay.nsw.gov.au</A><BR><BR> 
<B>Web: </B><A HREF="http://www.botanybay.nsw.gov.au" TARGET="_top">http://www.botanybay.nsw.gov.au</A><BR><BR> 
<B>Area (sq Km): </B>22<B>   Population: </B>37813<B>   Wards: </B>3<BR><BR> 
<B>Meetings: </B>3rd Wednesday 07:00PM<BR><BR> 
<B>ABN: </B>68 646 457 075<BR><BR> 
<B>COUNCILLORS (7)</B><BR><BR> 
<B>Mayor: </B>Clr Ron Hoenig (popularly elected)<BR><BR> 
<B>Deputy: </B>Clr George Glinatsis<BR><BR> 
Clr Mark Castle, Clr Stan Kondilios, Clr Greg Mitchell, Clr Anne Slattery, Clr Brian Troy<BR><BR> 
<B>SENIOR STAFF</B><BR><BR> 
<B>General Manager: </B>Mr Peter Fitzgerald<BR><BR> 
<B>Acting Director, Operational Services: </B>Mr John Riggall<BR><BR> 
<B>Deputy General Manager: </B>Ms Lorraine Cullinane<BR><BR> 
<B>Director, Technical & Regulatory Services: </B>Mr Paul Shepherd<BR><BR> 
<B>Public Officer: </B>Ms Lorraine Cullinane<BR><BR> 
<B>Auditors: </B>Spencer Steer & Associates
<DIV ALIGN="RIGHT"><SPAN CLASS="txtSml"><A HREF="#TOP"><IMG SRC="/DLG/DLGImages/up.gif" BORDER=0>Top</A><hr size="1" width=100%></SPAN></DIV>

I put the the Dapp Factory screen scraping tool on it. Pulled out 4 fields: name, address, website and size (area, population, wards). Probably could have refined further to grab councillors, etc, but you only live once. Example of the CSV output below:

The Council of the City of Botany Bay,141-143 Coward Street Mascot NSW 2020,http://www.botanybay.nsw.gov.au,Area (sq Km): 22 Population: 37813 Wards: 3

Google Spreadsheets

The output as CSV made for easy importing into Google Spreadsheets.

You could even maintain a dynamic connection to the DLG webpage, but I chose to detach from the mothership and fix up some of the inconsistencies in the addresses. I then used Pamela Fox’s spreadsheet gadget to geocode the addresses and add columns for latitude and longitude.

You can see the spreadsheet here.

(Not sure if it’s good practice to make it editable to the world – should I?)

I had a go at building a KML file (using CONCATENATE, etc) from within Google Spreadsheets, and even had a version going with Spreadsheet Mapper 2.0.

Yahoo! Pipes

But it seemed easier to knock up a simple Pipe, grab the output as KML and whack that into Google Maps.

So the Google Spreadsheet is no longer dynamically checking the DLG webpage but any changes or additions to the Google Spreadsheet will be reflected within minutes on the map. Any ideas?

Postscript

There’s a great map with Local Government Area boundaries at The Tally Room. It’s a large file though (over 2Mb as KMZ, sneak peek).

How would you combine the two?

— b3rn   maps   Aug 2, 08:04 PM   #

Comments

no HTML, use Textile

Name

E-mail

Website

Remember

• « Previous
• Next »