Twocation! Twocation! Twocation!

Add a commentFiled under the exquisite web, typing quickly as I'm getting hungry, Monday, 15th August 2011.
twocation.com homepage

Before the weekend I had 100-150 or so, very bored Twitter followers. I now have a small army currently 666 (eek!) strong, all equally unimpressed, and all thanks to a very kind chap called David Barker and his new twocation.com twitter app.

David uses my ExCanvas World Map code to enhance his site and has very kindly given a prominent shout out to me on the main page of his site.

Thanks for the new followers, David! I'm glad you found the code useful and good luck with your thang!



SQL SELECTs across non-calendar days

Add a commentFiled under geekiness, during a productive start to the week, Monday, 8th August 2011.

I came across a little SQL problem today that supplied a small 'Eureka!' moment, for me at least. I'm sure you hardcore DBA types will mock my ignorance, but hey, we weren't born knowing this stuff. I'm using MySQL here, but the priniciple here is easily translated to your DBMS of choice.

What I am trying to do is find events, or the number of events, that happen on a particular day. This should be easy, right?

Find the number of events happening per day:

SELECT      DATE(start_date_time) AS dt, /* retrieve the date… */
            COUNT(*) AS freq             /* …and # of events for that date… */
FROM        events                       /* …from our events list */
GROUP BY    dt                           /* aggregate results by calendar days… */
ORDER BY    dt                           /* …and output in chronological order */

Find details of all the events happening today:

SELECT      *                            /* retrieve everything… */
FROM        events                       /* …from our events list… */
WHERE       DATE(start_date_time) = '2011-08-08' 
                                         /* …where the event starts today… */
ORDER BY    start_date_time              /* …and output in chronological order */

But events - and I'm talking in particular about nightlife here - can happen post-midnight and still be thought of (by the drunken masses) as the previous day. To clarify, a DJ, band or late night cinema showing kicking off at 1am on Saturday morning, is still considered by the party-goer as Friday night, right? What I need to do is get the events starting in the wee hours included in yesterday's results.

So how to do the above queries now? My Google-fu failed me; what on earth do you type in to solve this one? Thankfully, after envisaging all sorts of fugly sub-queries and JOINs, the answer was really very easy and blindingly obvious when you think about it. All we have to do is shift the timestamps backwards by an arbitary number, say four hours (4am is the new midnight!), and we can keep the same basic (read: fast) queries we had earlier.

Number of events (partygoer-friendly version):

SELECT      DATE(DATE_SUB(start_date_time, INTERVAL 4 HOUR)) AS dt,
            COUNT(*) AS freq
FROM        events
GROUP BY    dt
ORDER BY    dt
/* subtract four hours, so any event starting before 4am is 
   counted as happening on the previous day */

Today's events (partygoer-friendly version):

SELECT      *
FROM        events
WHERE       DATE(DATE_SUB(start_date_time, INTERVAL 4 HOUR)) = '2011-08-08'
ORDER BY    start_date_time

Unless anyone knows a better way?

Google London Site Clinic

5 existing comments. Add your own.Filed under geekiness, at an ungodly early hour, Wednesday, 6th April 2011.

Last night I was lucky enough to attend Google's first London Site Clinic at TechHub. This, as the name suggests, was a free-of-charge chance for webmasters to hear from the horse's mouth the do's and don'ts of increasing your website's visibility in Google Search.

After a brief run through of basic SEO, e.g. the importance of good anchor text, <title> and description meta tags, human-readable image filenames and use of the alt tag, etc., we were given an overview and brief tour of Google Webmaster Tools. While informative, much of this was quite basic and seemed aimed a little below the average attendee; indeed I don't think those running the show were quite expecting everyone to raise their hand when asked the question, "who here uses our Webmaster Tools?".

By far the most interesting part of the evening was the Google Search Quality team's appraisals of sites owned by members of the audience. It was eye-opening to see how many easily avoidable mistakes were being made by relatively established sites. It was a little disappointing that so few of these webmasters failed to show up as some excellent questions could have come out of this - come on guys, it was free!

The key points that I took away from Google's Search Quality team were:

  1. Use web-sniffer.net, the LiveHTTPHeaders Firefox plugin or a similar tool to see what exactly is being returned from the server.
  2. Don't serve the same content from the non-www and www versions of your site.
  3. Do redirect the non-www version of your site to the www version (or vice-versa) of yor site using a 301 permanent redirect.
  4. Do redirect users to the equivalent page, eg. yourdomain.com/example should redirect to www.yourdomain.com/example, not just www.yourdomain.com
  5. Deliver the correct HTTP response: 404 pages should be displayed with a 404 response code, not a 200 (aka. a "soft 404").
  6. Keep the organisation and structure of your site as logical and as straightforward as it can be.
  7. Structure your content with appropriate use of headlines, using the range of <h1> to <h6>. A handy tip here was to Google Search for cache:yourdomain.com, then click the text-only version link in the top-right corner see non-styled page layout. Bang go my days of loading up Lynx to achieve the same result.
  8. Make full use of <title> and description meta tags on every page. This was the broken record point of the evening; the panel couldn't stress it enough. Do not duplicate these across pages on your site.
  9. The keywords meta tag is dead.
  10. Use canonical links to distinguish the original article in situations where content is duplicated.
  11. Use a robots.txt file.
  12. Use an XML Sitemap.
  13. Be aware of page load speeds, i.e. including numerous external .js and .css files is going to slow down browser rendering times and therefore the user's experience.
  14. Ultimately, make full use of Google Webmaster Tools, be part of their community and read their blog.

As pretty much everything covered during the evening is freely available in Google's SEO Starter Guide PDF, many of the Tweeters present found the content way too simple and were disappointed they weren't being given any SEO golden nuggets. While I too may have been frustrated if I were a hardcore SEO professional, I feel the point the team were trying to make was that these are simple tasks anyone can get right and site owners should concentrate on getting all these basics down before even thinking about delving into the "nitty gritty" details.

Yes, perhaps Google could have set greater expectation before the talk in terms of difficulty level or target audience, but the number of basic, "schoolboy errors" made by the critqued sites (plus this one you are reading now) suggest that perhaps all these advanced SEO tricks and tips are a complete waste of time and money if we don't get the foundations right first.

Most encouragingly from my point of view as someone so passionate about User Interface and User Experinece, the overall message was that if you look after your user, Google will look after you.

p.s. If the guy from Epilepsy Action wants any advice about fixing that hotlinking problem, do feel free to ask.

A very merry 3G Router to you all

2 existing comments. Add your own.Filed under geekiness, in a tidy house, Monday, 31st January 2011.

Finally it seems our house move is just about complete.

They say moving house and changing jobs are among the most stressful things a person can do. I can assure you that doing both at the same time - especially with a baby - is not to be sniffed at.

Just to add a little more frustration into the equation, thanks partly to moving over the Christmas and New Year holidays, we were to be without internet in our new home for over three weeks.

The often slow, semi-reliable, one-user-at-a-time 3G USB dongle I own was going to have to suffice. How were we to survive? *shudder*

That is until of course I desperately Googled the magic words "3G router" to see if such a thing even existed. The results were better than that: for a mere £20 plus VAT, the "TP-Link TL-MR3220" router which - via it's USB slot - was going to allow me to plug in my existing dongle and broadcast it's signal via wifi to all the devices in the house.

My expectations were, frankly, rock bottom low but for 20 notes, I was willing to take the plunge.

So did it work? Amazingly, yes. And it was fantastic. We were setup and broadcasting within minutes.

Seriously, if ever you find yourself in a position where you might need one of these, just get one. Where I feared I would be forced to use it solely to check for new emails, I was able to surf normally, to the point where I almost regret having a landline and "regular" broadband installed in the new place.

p.s. I bought mine from broadbandbuyer.co.uk (I'm not affiliated with them in any way). It turned up safe and sound exactly when they said it would.

Joy to the World

Add a commentFiled under geekiness, way too early, Monday, 20th December 2010.
Joy to the World

A few days ago I was contacted by James, a developer who wanted a little help with my JavaScript world map code that I created at the start of 2009. Luckily, he overcame the hiccup, finished his project and sent me the link for all to see.

There's something quite philanthropic about seeing your work put towards a good cause, especially when it is done in such an imaginitive way. Here is the christmas e-card James created on behalf of bread.org, Joy to the World.

If I don't see you before, Merry Christmas everyone.

Jon Combe