Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Geocode Addresses in T-SQL Expand / Collapse
Author
Message
Posted Monday, June 25, 2012 10:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:47 PM
Points: 587, Visits: 147
Yeah, I've been through this POC before and what I've discovered is that even if you have the Google Maps Premier license, you still can't use it for this sort of potential "Batch Geo-Coding" without running the risk of getting your licenses disabled. That being said, if you have a funded project, there are companies like Melissa Data or Telogys (sp) that can provide web or local implementations for you to Geocode with. Personally my experience is with Melissa Data; they will provide SSIS wrappers around their GeoCoding API, with unlimited Geo Lookups, and at least Quarterly updates of their files. Being that we are .NET savvy based on this discussion, it should be easy enough to write a CLR wrapper around their API to deliver GeoCoding from T-SQL.

Joshua T. Lewis
Post #1320735
Posted Friday, July 20, 2012 4:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 6, 2013 2:21 PM
Points: 3, Visits: 39
Don't see the sp_geoCode script?
Post #1333270
Posted Friday, July 20, 2012 6:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:54 AM
Points: 53, Visits: 417
The link to the original script is under "Resources" at the bottom of the article (http://www.sqlservercentral.com/articles/geocode/70061)

The direct link is: http://www.sqlservercentral.com/Files/Geocode.sql/6085.sql
Post #1333296
Posted Monday, July 23, 2012 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 6, 2013 2:21 PM
Points: 3, Visits: 39
Thank you!
Post #1333929
Posted Monday, October 22, 2012 10:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 10:43 PM
Points: 3, Visits: 21
hi all,
i have created the SP, mention in this topic.

when i execute the sp : EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'

i got NULL value in all the field.

am i missed any previous step ???

Thanks in advance.
Post #1375821
Posted Tuesday, October 23, 2012 1:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 12:40 AM
Points: 2, Visits: 14
Hi.. I did this for reverse geocoding. I changed the url to:
DECLARE @URL varchar(MAX)
SET @URL = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&latlng=' +
CAST(@GPSLatitude AS VARCHAR(20)) +
',' + CAST(@GPSLongitude AS VARCHAR(20))

But it shows null. What should I do to show the address for specific Lattitude and longitude using this techinique?
Post #1375869
Posted Tuesday, October 23, 2012 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:53 AM
Points: 13, Visits: 47
Hello,

I would suggest to use Google API ver. 3 (the current one) rather than ver. 2 referred in this post. Therefore, the URL should be something like:

http://maps.googleapis.com/maps/api/geocode/.....etc' - ver. 3

instead of:

http://maps.google.com/maps/api/geocode/ - ver. 2
Post #1376067
Posted Tuesday, October 23, 2012 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:53 AM
Points: 13, Visits: 47
It works on my system, I get:

33.755319 -117.867595 Santa Ana CA 92701 1234 N Main St

but I modified the code to use Google API ver. 3 instead of ver. 2 referred in the article, please see the post just above this one.
Not sure though this is the reason, did you try to execute spGeocode with other parameter variations as described in the article?

Regards,
M.R.
Post #1376093
Posted Wednesday, October 24, 2012 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 12:40 AM
Points: 2, Visits: 14
thanx for the reply. I solved it with a little modification before seeing the reply... anywz, thnx again...
Post #1376432
Posted Thursday, October 25, 2012 10:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 10:43 PM
Points: 3, Visits: 21
hi ,
i have changed api url from v2 to v3.
still getting null value after trying this : EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'



Thanks
Post #1377389
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse