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.
For anyone who is interested, I provided a sample service interceptor during TLCC2014 that geocodes addresses using the Google Maps API on create or update from the Address resources of the REST API. This is two years old, so no guarantees on API contracts and compatibility! It uses an open source .NET wrapper for the Google Maps API and writes to a local table which can then be updated to T_ADDRESS at a later time.
http://www.tessituranetwork.com/tlcc/2014/Presentations/01-O_Integrating_Third_Party_IntegrationPluginSamples.zip
Best of luck!
-Ryan
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Thursday, February 4, 2016 2:20 PM To: Ryan Creps <rcreps@tessituranetwork.com> Subject: Re: [Tessitura Technical Forum] RE: RE: Zip Codes and Radius
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.
From: Tom Brown <bounce-tombrown3568@tessituranetwork.com> Sent: 2/4/2016 12:18:56 PM
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.
There are definitely services out there that one can query over a REST API and get the actual LAT - LON for a specific address.
What are the costs? How many can you do a day?
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 all,
Something to keep in mind, but Google's API license specifically prohibits using their geocoder for creating any content or using it outside of a map implementation. I know a lot of folks do it anyway, but their license restrictions are very clear on the subject.
From their license terms (https://developers.google.com/maps/terms#section_10) down in 10.4.c.II:
"No creation or augmentation of data sets based on Google’s Content or Services. You will not use Google’s Content or Services to create or augment your own mapping-related dataset"
There are free geocoder services out there that do allow you to save the data for your own use. The US Census has a good one with both REST and Batch capabilities: https://www.census.gov/geo/maps-data/data/geocoder.html.There's also Nomatim based on OpenStreetMap data (http://wiki.openstreetmap.org/wiki/Nominatim) with their usage restrictions here: http://wiki.openstreetmap.org/wiki/Nominatim_usage_policy. Opencagedata is also free for up to 2500 requests per day: https://geocoder.opencagedata.com/