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.

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.

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,

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.

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

WHOA... I had no idea geo_location even existed in the table! I had been confused about how to properly use GEO_AREA since there would be some overlap between radii and geography, but this makes far more sense.

Is geo_location used by any part of tessitura at the moment? Perhaps it would be best put to work with a custom view (like the code you've included) and criteria builder keyword...

Unknown said:

Is geo_location used by any part of tessitura at the moment?

I don't think so. According to the v11 Table Structures doc, it is "added v11, not popuated but available for outside use." I don't think any standard Tessitura reports, objects, etc. make use of it as far as I know.

Good morning,

     I was doing some research on geo area and geo locations and can across this and wondered if anyone has picked up on this and done anything else with them since this last conversation.  I am trying to develop geo areas for the first time and then I see the geo loaction and the sample of what you both thought it could be used for and I was just wondering if eithe ryou have done anythig with this area yet?

     ---Arthur

Unknown said:

[...] I was just wondering if eithe ryou have done anythig with this area yet?

I populate the column for all addresses, and so far have used it only for general queries of "all attendees inside/outside 75 miles", and the like. It's easy to generate, and I anticipate more uses for it in time.

Thank you.  Just to be clear you are storing the Latitude and Longitude in the geo_location field within T_ADDRESS correct?

Thank you.  Just to be clear you are storing the Latitude and Longitude in the geo_location field within T_ADDRESS correct?

Unknown said:

Thank you.  Just to be clear you are storing the Latitude and Longitude in the geo_location field within T_ADDRESS correct?

Yep: "The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates."

https://msdn.microsoft.com/en-us/library/cc280766.aspx

Is anyone geocoding their addresses more accurately than a Zipcode Centroids lookup?

Or stated diffrently is anyone actual stroring the Lat-Lon of the actual address given by the customer?

If so I'd be interested in the method folks are using to do this geocoding.  What are the costs?  How many can you do a day?

I do know that there are services out there that one can query over a REST API and get the actual LAT - LON for a specific address.  Just do not know who might have worked this out at this level of detail.

Thought I'd reply with what I've ended up with since posting in this thread two years ago:

I didn't make any progress with getting detailed latitude and longitude from a service (although Tom Brown: if you look into Google's geocoding API you'll find that they have very generous limits for free geocoding requests), so I am working off of a local table containing lat and long coordinates for zip codes.

I wanted to leverage geo_location, and also wanted it to be updated automatically. So the answer was to hook into LP_UPDATE_GEOAREA, which is called whenever an address is updated. This code looks like this:

-- start out with computing geo_location:
IF @country = 1
    BEGIN;
        DECLARE @longitude VARCHAR(20),
                @latitude VARCHAR(20);

        SELECT @longitude = longitude,
            @latitude = latitude
          FROM LT_ZIP_CODE_DATA
            WHERE zip = LEFT(@postal_code, 5)

        IF @longitude IS NOT NULL
            UPDATE T_ADDRESS
                SET geo_location = geography::STPointFromText(CONCAT('POINT(', @longitude, ' ', @latitude, ')'), 4326)
                -- 4326 is like a conversion style. See sys.spatial_reference_systems
                WHERE address_no = @address_no;
    END;
-- Okay, now we'll assign geoareas.

Then with geo_location coded, I can create a "Mile Radius from Bard" T_KEYWORD entry where the Detail_Col value is this:

!.geo_location.STDistance(geography::STPointFromText('POINT(-73.90297 42.03093)', 4326))/1609.344

I still use geo_area for other stuff, but doing radius this way means I don't have to waste geo_areas for radius. Overall this solution has worked out great (and you can trivially expand or contract your radius in criteria builder), but I'm still interested in building some kind of async process that calls out to Google for REALLY accurate location data. A man can dream.