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.