Blog

  • How to create a ZIP code distance lookup table with 1 line of SQL

    A while back, Jobster CTO Phil Bogle blogged about some of the tricks I’ve used to do fast location queries in SQL. The link to my SQL query to generate the zip lookup table for radius searches is now dead (a cybersquatter stole my domain name and I don’t want to discuss it!). So here’s the original blog post:

    If you need to build a radius search for something on your website, then creating a zip code distance lookup table performs much better than calculating distance for every search. This monster chunk of SQL will create that table for you. It takes about 12 hours to run on a fast machine.

    insert into zip_dist2 (fromzip, tozip, dist) select z1.zip, z2.zip, ROUND((3956 * 2 * atan2(sqrt((POW(sin(((z1.lat * (atan2(1,1) * 4) / 180) – (z2.lat * (atan2(1,1) * 4) / 180))/2.0),2) + cos((z2.lat * (atan2(1,1) * 4) / 180)) * cos((z1.lat * (atan2(1,1) * 4) / 180)) * POW(sin(((z1.lon * (atan2(1,1) * 4) / 180) – (z2.lon * (atan2(1,1) * 4) / 180))/2.0),2))),sqrt(1-(POW(sin(((z1.lat * (atan2(1,1) * 4) / 180) – (z2.lat * (atan2(1,1) * 4) / 180))/2.0),2) + cos((z2.lat * (atan2(1,1) * 4) / 180)) * cos((z1.lat * (atan2(1,1) * 4) / 180)) * POW(sin(((z1.lon * (atan2(1,1) * 4) / 180) – (z2.lon * (atan2(1,1) * 4) / 180))/2.0),2))))),2) as distance from zip_data as z1, zip_data as z2 where (3956 * 2 * atan2(sqrt((POW(sin(((z1.lat * (atan2(1,1) * 4) / 180) – (z2.lat * (atan2(1,1) * 4) / 180))/2.0),2) + cos((z2.lat * (atan2(1,1) * 4) / 180)) * cos((z1.lat * (atan2(1,1) * 4) / 180)) * POW(sin(((z1.lon * (atan2(1,1) * 4) / 180) – (z2.lon * (atan2(1,1) * 4) / 180))/2.0),2))),sqrt(1-(POW(sin(((z1.lat * (atan2(1,1) * 4) / 180) – (z2.lat * (atan2(1,1) * 4) / 180))/2.0),2) + cos((z2.lat * (atan2(1,1) * 4) / 180)) * cos((z1.lat * (atan2(1,1) * 4) / 180)) * POW(sin(((z1.lon * (atan2(1,1) * 4) / 180) – (z2.lon * (atan2(1,1) * 4) / 180))/2.0),2))))) < 100 and z2.zip != z1.zip

    This generates a lookup table that contains two zip codes and their distance from each other for every zip in the USA within 100 miles of each other. It uses the Haversine formula to calculate the distance between two points on the surface of a sphere.

    I used this when designing our vertical search engine to create the lookup tables we use for our radius search. I use MySQL for this. You’re also going to need a zip_data table that contains zip codes and their respective latitudes and longitudes. You can buy this data for about $50 from the many retailers online.

  • World-wide city database and other (free) geospatial data

    The National Geospatial Intelligence Agency is one of my favorite data sources – it’s also one of my favorite names for any government agency. The agency provides a database of world-wide features which I use as a data source for Geojoey.com’s landmark search feature (top right of the screen).

    These guys are selling the equivalent data for over $300.

    For an up to date ZIP code database, you should contact USPS.gov and order it from them – which may take a while – government companies grumble grumble. Or just buy it from one of the many online sellers for about $50. Census.gov doesn’t do ZIP codes anymore, but if you don’t care about it being current, there’s an old ZIP code database available for download.

    Census.gov’s TIGER database is definitely THE source for US geospatial data. My favorite page is the cartographic boundary files they’ve extracted from the database. It has things like ZCTA’s (ZIP borders), County boundaries, etc. If you’re handy with a graphics app, you can do all kinds of fun stuff with this data.

  • Brand Authority and the Milgram Experiment

    I had a great chat with Tony Wright yesterday evening over a few drinks. Tony has a degree in psychology and is a fellow entrepreneur and we got chatting about the Milgram Experiment and its applicability to branding. Here’s a video summary:

    The experiment found that few people have the resources needed to resist authority, even when the authority figure is telling them to do something in violent opposition to their moral judgment.

    Brands carry a level of authority. Take a startup for example:

    • It’s founded by two Stanford postgrad students. +2 authority
    • It’s published on Techcrunch as the hot new thing. +1 authority.
    • It gets Angel funded by Larry Page, one of the Google founders. +7 authority
    • It gets a first round of VC funding from Sequoia. +5 authority
    • It gets published in the NY Times also as the hot new thing. +5 authority

    You don’t yet know what this brand does, but you’re already dying to become a user – simply because the brand has a ton of authority and because your Milgram susceptible brain is telling you you to obey. [Or perhaps you fall within the 35% of Milgram participants who didn’t kill the learner]

    This is the reason I signed up for Joost very early on – because it’s founded by Niklas Zennstrom, one of the Skype founders.

    I’ve seen startups do an excellent job of creating this authority using big marketing budgets and great PR firms without any product to speak of. Users wind up (to use a British expression) gagging for it and they don’t even know what the value proposition is.

    On the flip side, many great products lack the brand authority to get the user adoption they need. For companies on a tight budget run by first-time entrepreneurs, this is a challenging hurdle. I don’t think it’s insurmountable though – it simply takes more time and careful growth.

    Simply realizing that your brand has authority that needs to be grown is a great start.

  • Hard Rock – music for MTB

    Andy Sack doing 51 pushups inspired me to haul my MTB out of the closet and start cycling hills. If you’re into hard rock and need something to get your adrenal gland pumping, here’s my list:

    Godsmack – Running Blind
    The Exies
    Army of Anyone – Goodbye
    Shinedown – simple man
    Trivium – The Rising
    Tesla – into the now
    Dropping Daylight – tell me
    Incubus – Anna Molly
    Trivium – This world can’t tear us apart
    Papa Roach – Forever
    Linkin Park – what I’ve done.
    Hinder – Get stoned
    Lost prophets – wake up
    Trapt – Headstrong
    Sevendust – Black
    Seether – Remedy
    Marilyn Mansom – Coma White
    Mudvayne – Forget to remember
    Motley Crue – If I die tomorrow

  • Marketing team brainstorms Facebook app launch (vid)

    Jobster’s CEO Jason Goldberg sent me this vid this morning of their marketing team brainstorming the launch of their new Facebook application.

    I’m curious what your thoughts are on the brainstorming process and the ideas they’re throwing around.

    Full disclosure: I worked for Jobster and left about 1 year ago. I also sold a previous business, WorkZoo.com to Jobster.

  • How to make the perfect cup of coffee

    When I arrived in the US of A in 2003 I got into coffee in a big way. It took me a while to get it right. When I moved to Seattle from CA I discovered Peets coffee. They don’t keep any beans in-store for longer than 10 days so it’s incredibly fresh. You’ll notice when you grind Peets beans, none of the ground coffee sticks to the grinder. Try the same with Starbucks beans and it’s a mess. I don’t know what that means, all I know is that there’s a correlation between that effect, fresh beans and great tasting coffee.

    There’s another outfit (in case you thought this was a Peets viral marketing trick) called CoffeeFool.com. I spotted them on my Gmail ads the other day. They claim to have very fresh beans – I haven’t tried them yet.

    So here’s my recipe.

    Ingredients:

    1 French Press

    1 Pound Peet’s Sumatra whole beans (bought from a Peets outlet because they have very fresh beans)

    1 high speed coffee grinder

    3 Quarts filtered water

    1 Thick ceramic coffee cup

    Fresh heavy whipping cream

    Sugar to taste

    Method:

    Boil the water in a stove top kettle.

    Grind the beans while you wait – use a 10 second medium grind. I don’t measure my beans using a spoon – I simply cover the blades of the grinder. Don’t pour the ground beans into the french press yet.

    Once the water has boiled, fill the coffee cup with boiling water and let it stand.

    Fill the french press with boiling water WITHOUT the beans.

    Let the french press stand for 10 seconds.

    Empty the boiling water from the french press, pour the ground beans in from the grinder and fill with enough water for 1 cup.

    Stir the ground beans into the water until completely covered with water.

    Push the press down immediately because if you covered the blades of the grinder, that’s quite a lot of coffee for 1 person. You’ll get all the oils without any bitterness this way.

    Throw out the boiling water in your ceramic cup, pour in 1/2 inch of heavy whipping cream and fill with coffee from the french press.

    Add sugar to taste.

    Enjoy!

  • What it's like to be an entrepreneur

    Dave Lu, CEO of FanPop.com was part of a panel at the Churchill club recently – a previous entry has the video. I loved an analogy he made: That the leap of faith you take as an entrepreneur is a lot like a scene from Indiana Jones. Here’s the scene:

    I love what he does at the end of the clip – I’m sure there’s a metaphor there somewhere.

  • The ULTIMATE guide to linkbaiting

    TW sent me this piece of web marketing gold…

    The ULTIMATE guide to linkbaiting. Building blog content to get traffic from Digg, Reddit, delicious, etc.

  • Facebook predicted to overtake MySpace

    A friend in the UK sent me this. The number of searches for ‘facebook’ in the UK as just overtaken the number of searches for ‘myspace’. This has a history of being an excellent predictor and it’s showing that myspace is going to get beaten up by Facebook – at least in the UK market.

  • Markus Frind James Hong video panel/interview

    I found this awesome vid on Guy Kawasaki’s blog. It’s a panel session with Markus Frind, Founder, PlentyofFish.com
    and James Hong, Co-Founder, HotorNot.com and a few others. Markus Frind is my personal hero and much of the reason I have an aversion to VC money.

    This is more than an hour long, so when you’re done working tonight at 2am, crack a beer and enjoy this: