SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Geocode Addresses in T-SQL


Geocode Addresses in T-SQL

Author
Message
DeusExDatum
DeusExDatum
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 189
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
cleonard 54659
cleonard 54659
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 39
Don't see the sp_geoCode script?
David Rueter
David Rueter
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 574
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
cleonard 54659
cleonard 54659
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 39
Thank you!
Naresh Parmar
Naresh Parmar
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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.
beetles
beetles
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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?
mr-967650
mr-967650
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
mr-967650
mr-967650
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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.
beetles
beetles
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 14
thanx for the reply. I solved it with a little modification before seeing the reply... anywz, thnx again...:-)
Naresh Parmar
Naresh Parmar
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search