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

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,

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