Zip Codes and Radius

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?

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.

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/