Zip Codes and Radius

That's probably the better way to do it but off the top of my head here - create a local table with all zip codes within 70 miles of Houston - you should then be able to do a select of all customers where zip <> local_zip

This site should help you determine those zips:

http://maps.huge.info/zip.htm

 

Heather Laidlaw Kraft
SEATTLE REPERTORY THEATRE

Please consider the environment before printing this e-mail

That's probably the better way to do it but off the top of my head here - create a local table with all zip codes within 70 miles of Houston - you should then be able to do a select of all customers where zip <> local_zip

This site should help you determine those zips:

http://maps.huge.info/zip.htm

 

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.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi,

In response to Heather's mutter...  Two thoughts - first, as Heather mentioned, the next release of T-Stats (Summer 2009) is planned to have a new mapping component which will allow you to render households/density you have identified in T-Stats as a map (our last two webinars show a preview of what this will look like and at least one of them is available to view).  So, while this can give you a really good look at distribution of, say, your subscribers, members, attendees-of-last night's-performance-who-purchased-tickets-on-line, etc. it doesn't actually address Matt's interest in a group of households within a particular radius.

However, we did work with another T-Stats org to come up with a way to calculate distance from your organization's "home" postal code and drop it into the Geo Area field (which is helpful not only in T-Stats but in List Manager too).  Thus, the Geo Area values are 0 - 5 miles, 5 - 10 miles, 10 - 20 miles, etc. each indicating distance from your org.

It requires that you have a table in your db which includes postal code, latitude and longitude (which, in the US, is available online through the US Census Dept for free) and some fun SQL that includes trigonometry to map the lats and longs and turn them into distances.  (And while it works nicely in T-Stats, having T-Stats is certainly not a requirement for this to work for you).

I'd be happy to share the SQL if you are interested, (while my Mom would like to believe that I can knock out trigonometry from scratch, full disclosure - I found most of the code in a book...)

 

Andrew Recinos

JCA

Hi,

In response to Heather's mutter...  Two thoughts - first, as Heather mentioned, the next release of T-Stats (Summer 2009) is planned to have a new mapping component which will allow you to render households/density you have identified in T-Stats as a map (our last two webinars show a preview of what this will look like and at least one of them is available to view).  So, while this can give you a really good look at distribution of, say, your subscribers, members, attendees-of-last night's-performance-who-purchased-tickets-on-line, etc. it doesn't actually address Matt's interest in a group of households within a particular radius.

However, we did work with another T-Stats org to come up with a way to calculate distance from your organization's "home" postal code and drop it into the Geo Area field (which is helpful not only in T-Stats but in List Manager too).  Thus, the Geo Area values are 0 - 5 miles, 5 - 10 miles, 10 - 20 miles, etc. each indicating distance from your org.

It requires that you have a table in your db which includes postal code, latitude and longitude (which, in the US, is available online through the US Census Dept for free) and some fun SQL that includes trigonometry to map the lats and longs and turn them into distances.  (And while it works nicely in T-Stats, having T-Stats is certainly not a requirement for this to work for you).

I'd be happy to share the SQL if you are interested, (while my Mom would like to believe that I can knock out trigonometry from scratch, full disclosure - I found most of the code in a book...)

 

Andrew Recinos

JCA

Heather Laidlaw Kraft
SEATTLE REPERTORY THEATRE

Please consider the environment before printing this e-mail

That's probably the better way to do it but off the top of my head here - create a local table with all zip codes within 70 miles of Houston - you should then be able to do a select of all customers where zip <> local_zip

This site should help you determine those zips:

http://maps.huge.info/zip.htm

 

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.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!


This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi Matt,

I think Chris already sent you the sample code off-line, if anyone else is interested, I've posted it to my profile...

 

Thanks!

Andrew

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.

Hi Andrew,

Thank you for the script it worked great.  We ended up buying a zip code database that was more up to date than the one from the census bureau.  At the moment we are using this information in a report that displays number of households by geo area.

Thanks again!

Jon

Hi Andrew,

I was interested in getting my hands on this code as well -- can't seem to find it in your profile.

Does anyone have a recommended resource for obtaining up-to-date ZIP code data? I think if you tried hard enough, you could get it from the Google Maps API, but that's a bit beyond me at this moment.

Aha -- found it, Andrew.

Matt, I'm pretty sure anything using the Google Maps API would have to be periodically imported -- I believe they keep track of usage and will ask you to pay for access if it seems like you're integrating it as a core part of your app.

I know that there are several commercial options for obtaining ZIP code data, but I just was able to find the publicly-accessible database of latitude and longitudes published by the US Census Bureau:

https://www.census.gov/geo/maps-data/data/gazetteer2013.html

At the bottom of the page content is an item for "ZIP Code Tabulation Areas" which could theoretically be imported into SQL Server for you to run Andrew's code on.

Hmm. I suppose that’s logical, if we assume that POB-only ZIP codes aren’t tracked by the census bureau because they have no physical location. (This may or may not be true.) You could probably count up and/or down until you get a match and use the nearest codes as an approximation.

Unknown said:

Does anyone have a recommended resource for obtaining up-to-date ZIP code data? I think if you tried hard enough, you could get it from the Google Maps API, but that's a bit beyond me at this moment.

Re: Zip code data, I recommend

http://www.zip-codes.com/

Inexpensive, and enables us to update our TR_CITYSTATE regularly.

Re: finding all patrons within a distance, has anyone else populated their geo_location column in T_ADDRESS? With that done it should be possible to do something like this trivial example:

declare @guthrie_location  geography

set @guthrie_location = (select geo_location from T_ADDRESS where customer_no = 135971 and primary_ind = 'Y') -- Guthrie Theater const record

select customer_no, street1, city, state, postal_code, geo_location.STDistance(@guthrie_location) / 1609.34 "dist_from_guth"

from T_ADDRESS a

where a.primary_ind = 'Y'

and geo_location.STDistance(@guthrie_location) / 1609.34 <= 70 -- within 70 miles