This time I actually searched TASK and looked for any existing way to identify all constituents that live outside of a 70 mile radius of Houston. Unfortunately, nothing came up.
Would it be easier to identify all zip codes within a 70 mile radius and use a NOT IN (SELECT...) sort of thing, considering that there are a LOT less of those? Even so, how in the world would I do that? Surely, this has been addressed before...
I would appreciate any advice on the subject, Tessiturians.
Trivia Reward: Charles Manson once auditioned [unsuccessfully] for The Monkees.
Matt,
I see this is an older post but are you able to share with us your organization's plans for using this information? Probably there are dozens of uses; did your request come from marketing? Why specifically 70 miles?
************ warning salty dog story ************
I used to solve this problem with software for converting addresses to longitude and latitude. I found longitude and latitude additionally useful for appending publicly available demographic data such as 10 year census data, etc.
From longitude and latitude, a math formula can calculate the distance directly to your arts center/museum (unfortunately it's a fairly complicated geometry formula--best to ask a current high school student for help).
The result was the distance in miles to every patron's home. Sub-total patron revenue and it's all pretty sexy stuff on a scatter plot.
*************** end of back in the day ****************
SQL Server 2008 has a new data type for storing spacial information. I've been pretty excited about this (it's going to be super useful when I switch to doing wildlife surveys of, say, rattlesnakes in the high desert). Plus the Google Maps API is now available for our geocoding pleasure. The latest release returns distance and travel time.
Maybe minutes of commuting time would be of use here? How long does it take each of our patrons to get to our venue? How much revenue do we bring in per minute of commute?
In Washington DC, they've started a major two year construction project on one of the few bridges into the city. They're working off-peak hours but the result will increase non-rush hour driving times both into and out of the city.
Are our patrons still willing to make the extra commute or should we slow our marketing efforts in outlying Virginia?
(That sounds a little thin, doesn't it? Whatever the case, a commute time/revenue scatter plot definitely would look pretty neat taped in my cube. It's a matter of the right color choice.)
Warren
As it turns out, we have a group classification where membership is based on distance from our opera. National Patrons Circle is for members who've donated a certain amount in a given production year and live 70 miles or more outside of Houston.
I imported the data you referred me to into a table and used the proffered code to generate a list of customer numbers that would qualify for said membership.
What marketing and development do with this information is unknown to me, but I am very intrigued (as I'm sure marketing and development would be as well) by your examples of putting this sort of data to use...
And thank you all for your help. You made me look good here.