Hello,
I am looking to update the local procedure LP_UPDATE_GEOAREA and before getting started I wondering if anybody has updated this stored procedure for Canada, specifically Ontario and would be willing to share your work.
Thanks
-Dave Kelly
Dave
Are you interested just in the geo_area field or the geo_location too. I’ve done some work on the latter but based on Forward Sortation Areas (FSAs, i.e. first 3 digits of postal code) not individual addresses.
Fernando Margueirat Business Analyst The National Ballet of Canada 470 Queens Quay West Toronto, Ontario M5V 3K4 P: 416 345 9686 x453 F: 416 345 8323
http://national.ballet.ca
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Dave Kelly Sent: Monday, September 21, 2015 8:44 AM To: Fernando Margueirat <FMargueirat@national.ballet.ca> Subject: [Tessitura Technical Forum] LP_UPDATE_GEOAREA for Canada
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 Fernando,
I'm not sure what or where geo_location is all about and perhaps I should.
What I am looking to do is get specific using FSA's for Ontario and less specific for the rest of Canada and the US.
-Dave
geo_location is really interesting -- it stores a geography datatype, and is available purely for custom use.
I'm using it (in the US) for a piece of list criteria that is "Mile Radius from Theater". geo_location is populated by LP_UPDATE_GEOAREA using a local table that matches postal codes with latitude and longitude (available freely from a number of online sources). The T_KEYWORD detail then looks like this: !.geo_location.STDistance(geography::STPointFromText('POINT(-73.90297 42.03093)', 4326))/1609.344
It has occurred to me that someone could conceivably utilize the public Google Maps Geocoding API to get even more detailed coordinates per address, probably by using LP_UPDATE_GEOAREA as a way to add to a queue of requests to that API.
Unknown said: I'm using it (in the US) for a piece of list criteria that is "Mile Radius from Theater". [...]
I'm using it (in the US) for a piece of list criteria that is "Mile Radius from Theater". [...]
Same here. Being able to limit pulls to, e.g. within 50 miles of the theater, for some purposes, is something we've been using regularly.
We have done some prototype with PowerQuery using a GeoCoding REST API. There are often limits on the numbers of queries you can do per hour minute and day. However, You can do some fairly cool stuff once you have coordinates.
However more importantly to our customers the time it takes to get to our venues is important. The as the crow fly's distance can often have little with the preceived or actual transit time to our venues. In the NYC Area I have done the public transit times to BAM from most of the area zip codes using Open Trip Planner. https://codeforphilly.org/projects/otp They allowed me to use their REST API to estimate the Transit Travel time to BAM using multi-modal (Walk, Bus, Train, Subway) transit.
Hi Dave,
Not sure if you are still working on this/looking to do it, but I just did a total overhaul of ours, including FSA data within Ontario and Canada, that I am happy to share. You might need to tweak it a little for your specific location, but I think it includes most of the info you need.
Let me know.
Sheila