Stap isi

25 July 2010

#LGWN10 tweeps

Last year I knocked up a tweeps map ahead of the 2009 LG Web Network conference. The aim was to link via Twitter ahead of the event – and to muck about with Google’s maps and spreadsheets. Here’s one for this year.

I had intended to use Google Fusion Tables, a tool purpose-built to import and visualize table data online. (It made light work of this visualisation of RTA speed camera locations, ripped from an RTA PDF.) But from the get-go I couldn’t import my spreadsheet from Google Docs. Maybe the service is too beta.

Here then are the steps taken to create the #LGWN tweeps app:

1. Create a spreadsheet in Google Docs with a form to capture user input
2. In sheet 2 of the spreadsheet, import the user data with the function =ARRAYFORMULA(Sheet1!A:E) then run a function in the neighbouring columns to return latitude and longitude (with some randomisation to prevent markers stacking directly on top of each other if two tweeps are in the same location)
3. Share the spreadsheet – make the data public
4. Set up a webpage with a Google Map generated using the excellent new Google Maps API v3
5. Pull in and loop through the XML data, building markers in javascript for each tweep – I used a plugin for my CMS Textpattern but this could be handled natively by the Google Maps API

Geocoding

I chose to geocode within the spreadsheet because I was too timid to tackle the new Geocoder class in the Google Maps API.

I used the CONCATENATE function to construct a URL from the stem http://maps.google.com/maps/geo?output=csv&q= and the user’s location (suburb, town/city, country). For example, copy this URL into your address bar:

maps.google.com/maps/geo?output=csv&q=Darlinghurst,Sydney

The ImportData function runs this URL and adds the result to neighbouring columns. Note the limit of 50 ImportData functions per spreadsheet.

Twits as icons

When building the markers on the map, it was easy to grab the user’s profile picture and description from Twitter via their API. Try this URL with your screen name:

http://api.twitter.com/1/users/show.xml?screen_name=[ScreenName]

I generated a generic shadow using Google Maps Icon Shadowmaker although I didn’t anticipate some profile pictures having transparency.

Copy, paste, cross fingers

I hope this project shows that anyone with HTML, a search engine and some patience can build tools for fun and on the cheap. It’s a monkey see, monkey do approach. Not very efficient, often frustrating, but you have to get your hands dirty.

See Gabriel Svennerberg for a good introduction to the API, work off the demos at Google and find inspiration at Google Maps Mania.

And if you’re coming to LGWN10, add yourself to the map.

— b3rn   community of practice, lgwn, maps   Jul 25, 01:25 PM   #   Comment

12 February 2010

Managing your Local Business Centre listing in Google

Most visitors to your council website probably come from Google Search. Great, but have you claimed Google’s Local Business Centre listings for your council offices, venues and facilities?

I just did a quick search through Google Maps for NSW councils and their theatres, galleries, libraries, swimming pools, etc. As you’d expect, they were easily found. But few had listings that were ‘owner-verified.’ An opportunity for imparting useful information is being missed.

Some of the listings appear programmatically generated, others have been annotated by web users. The Yellow Pages occasionally comes up as the source, with some odd results. The top result for Bega Valley Shire Council lists its category as ‘Excavating & Earth Moving Contractors.’

Time to claim all the places that you administrate!

You’ll need a Google Account to manage the listings. Verification is by a PIN number that will be supplied by a talking Google bot to the telephone number you provided in the listing. This can actually be the trickiest part! Warn your customer service officers to expect the Google bot to phone – often within a minute or two of you submitting your listing. (There’s also a postcard option.)

Why bother?

The listing and place page is information rich. Not only does it locate your facility or venue for driving directions and other wayfinding, it can list opening hours, photos, videos and any other info you deem useful. Tell people you have a public toilet, free WiFi or a meeting room for hire.

The place page also invites users to rate and review the business. (Note that it also aggregates reviews from truelocal.com.au.) Stanton Library and Randwick City Council each had one positive review.

You can also add timely information to your place page. Google says “Post about events, specials and more. Example: “live music tonight at 7pm!” You have 160 characters and the information expires in 30 days or when you choose to delete. Another way to highlight a special event or service interruption?

But that’s not all. As the listing owner you have access to a dashboard that shows impressions (how many times users saw your business listing as a local search result), actions (number of clicks for more info, like driving directions, on Maps and clicks to your website) and the top search queries that led them to your listing.

Good, simple metrics.

So, go to Google Maps and search for your council and facilities. Compare with the listing for the Powerhouse Museum.

— b3rn   google, google analytics, maps   Feb 12, 12:03 AM   #   Comment [2]

17 August 2009

Putting LGWN09 tweeps on the map

Another string & sticky tape home craft session. See the result. The nice bit is that people can add themselves to the map. Here are the steps taken:

1. A Google Docs spreadsheet form collects Twitter name, suburb (optional), city/town and country from those attending the conference
2. When submitted, the form populates a Google spreadsheet
3. The spreadsheet uses functions to generate latitude, longitude, Twitter URL, Twitter profile image URL and a HTML description for each tweep
4. The sheet is set to ‘publish to the web’ as a text file – CSV format – with option ticked to ‘automatically republish when changes are made’
5. A Yahoo! Pipe transforms this data into a GeoRSS feed
6. The feed gets chucked into Google Maps and you can see which tweeps will be at the conference (and where they’re from)

Those Twitter pics…

… are generated courtesy of the SPIURL web service. It provides a static link to Twitter profile images.

Limitations

1. Locations are being dynamically geocoded via Google and returned to the spreadsheet using the ImportData function (refer to Geocoding by Google Spreadsheets). But you’re restricted to 50 of these functions per sheet – so there’s a hard limit on the number of tweeps that can be added.
2. More than one tweep may enter “Sydney, Australia” as a location – resulting in multiple markers on the same point, with only one visible and clickable. The sidebar gives access to all tweeps but that’s a consolation prize at best. A random number could probably be added to the tail of each coordinate to push markers slightly apart? Update: truncated the lat/long and added a random number – kinda works but a cluster would be better.
3. There is no error checking. “WE Believe in Community”!

— b3rn   community of practice, lgwn, maps, web teams   Aug 17, 11:04 PM   #   Comment

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   #   Comment

6 December 2008

Take my waste

When looking for local government service information, should you have to know what Local Government Area (LGA) you’re in?

Some information services would benefit from a standardised state-wide implementation.

Like information on waste collection.

Continued...

— b3rn   maps, open data   Dec 6, 05:15 PM   #   Comment

12 October 2008

Flickr & Local Govrnment

If your Council and Library service are not on Flickr, they should be.

In the first 4 weeks […] we had more views of the photos than the same photos in the entirety of last year on our own website — Seb Chan, Powerhouse Museum

He’s talking specifically about their Tyrell Collection – historic images from two of Sydney’s principal photographic studios in the late 1800s and early 1900s – in The Commons on Flickr. But the point still applies.

More people will see you and your content.

A challenge for Council websites is getting information across to the people who are not visiting us online. Many visits are motivated by a specific purpose — getting a resident parking form or the minutes of a Council meeting. But if we’re linked as a contact on Flickr (or any other social networking space) there’s always a chance we can grab some attention from their peripheral vision.

And what better way than with a photo?

That’s a good starting point to argue for US$25 a year (the cost of a Pro account) and the time required to set-up and administer your Flickr space.

Continued...

— b3rn   flickr, maps, user generated content   Oct 12, 10:50 PM   #   Comment [1]