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»»

Geocoding with SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:51 AM
Points: 14, Visits: 43
You have mostly 2 options.
* Use a couple cursors (lookup how to use cursors in BOL) to iterate your source table and the return from the SP (you can dump the output of the SP into a temp table using an INSERT statement or you can use OpenRowSet)
* Use the TVF I posted on 2006/05/10 in page 3. And see my 2007/02/09 post (same page) on how to use it for multiple hits.



Post #524265
Posted Thursday, June 26, 2008 9:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:57 AM
Points: 23, Visits: 468
I personally would not use this method to do what you are trying to do. If you have a web server that can run ASP, I would simply make a database call to get the customers and their address data, then call the following function with as much or little address information as you wish:

Function GetGoogleCoordsCSV(Query)
GoogleMapKey = "your Google key"

url = "http://maps.google.com/maps/geo?output=csv&key=" & GoogleMapKey & "&q=" & Replace(Query,"#","")

set csvhttp = Server.CreateObject("Msxml2.ServerXMLHTTP")
csvhttp.open "GET", url, false
csvhttp.send ""
csvResult = Split(csvhttp.responseText, ",")

' The first number is the status code,
' the second is the accuracy,
' the third is the latitude,
' the fourth one is the longitude.
' 0 Unknown location.
' 1 Country level accuracy.
' 2 Region (state, province, prefecture, etc.) level accuracy.
' 3 Sub-region (county, municipality, etc.) level accuracy.
' 4 Town (city, village) level accuracy.
' 5 Post code (zip code) level accuracy.
' 6 Street level accuracy.
' 7 Intersection level accuracy.
' 8 Address level accuracy.
Precision = csvResult(1)
Longitude = csvResult(3)
Latitude = csvResult(2)
End Function

Then just run an update statement in the ASP to update the customer coordinates. If you need more information let me know. Here is the code for the main ASP page:

<%
strSQL = "SELECT CustomerID, Address1, City, State, Zip FROM Customer"

Set oRs = oConn.Execute(strSQL)

Do While Not oRs.EOF
CustomerID = orS("CustomerID")
Address1 = oRs("Address1")
City = oRs("City")
State = oRs("State")
Zip = oRs("Zip")
If Address1 <> "" and City <> "" Then ' no sense in geocoding blank address
Call GetGoogleCoordsCSV(Replace(Address1,"#","") & " " & City & " " & State)
oConn.Execute("UPDATE Customer SET Latitude = '" & Latitude & "', Longitude = '" & Longitude & "', Modified = GETDATE() WHERE CustomerID = " & CustomerID & "")
End If
oRs.MoveNext
Loop
%>



Kindest Regards,

Allen McGuire
madtownlounge.com
Post #524291
Posted Thursday, June 26, 2008 9:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:48 AM
Points: 2, Visits: 81
Hmm going to try all reccomendations and see what I can understand / get working.

To be honest I'm used to doing fairly simple stuff with SQL2000 and vb.net so this is all a bit scary atm and my first venture into SQL2005

I cant thank you guys enough for helping though, I will let you know how I get on...

Cheers,
Nik
Post #524303
Posted Thursday, July 31, 2008 6:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 3:12 PM
Points: 3, Visits: 42
Hello
I also enjoyed using this article and all works except that I get an error in Visual studio when I try to use the store procedure with a dynamic addressm(it just refuses to let me use the ?-mark and says I have a syntax error)

this is the sentence i use

EXEC [dbo].[SPGeocode]
@Address = ?,
@City = NULL,
@State = NULL,
@appid = N'MyyahooId'

I am trying to get the latitud/longitude for a number of addresses. I call the procedure from within a FOR each LOOP. If I use a given address it works. I also worte a standard procedure that takes a text and inserts a record. It also works with my ?-mark.

What do I need to do to be able to use a dynamic input for the Address parameter?

regards lima


Post #544806
Posted Thursday, July 31, 2008 7:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:57 AM
Points: 23, Visits: 468
If you don't have an address, don't send anything - not even a question mark. Google's API will return lat/long still. I've done it many times with just city/state pairs.


Kindest Regards,

Allen McGuire
madtownlounge.com
Post #544809
Posted Thursday, July 31, 2008 7:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:57 AM
Points: 23, Visits: 468
Hopefully I understood you correctly - if not let me know. Maybe even post the code of whatever isn't working.

Allen
http://www.madtownlounge.com
Locate Live Music



Kindest Regards,

Allen McGuire
madtownlounge.com
Post #544810
Posted Friday, August 1, 2008 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 3:12 PM
Points: 3, Visits: 42
Hello
Thanks for your mail

I did have an address and found a workaround, namely calling my procedure from another one.
I can now create Google maps based on the latitude/longitud data. I have created a reporting service report and it works fine from visual studio, But when I deploy it to my report server and call it from a browser I get an error from Google saying
"Bad Request
Your client has issued a malformed or illegal request. "

Have tried calling the google static function from reporting services? How can I avoid this error?


Post #545043
Posted Sunday, October 12, 2008 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 11, 2010 6:30 PM
Points: 8, Visits: 50
Can I do this in sql server management express instead of visual studio?
Post #584564
Posted Sunday, December 28, 2008 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 11, 2010 6:30 PM
Points: 8, Visits: 50
How do I make this work for me? Do I just paste the code as a stored procedure in my sql 2005 database?

Thank you for any help in advance.

Mikek
Post #626314
Posted Sunday, December 28, 2008 6:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 2:21 PM
Points: 55, Visits: 168
Mikek - unfortunately it is not as simple as copy/paste. If you have visual studio you can just follow the directions in the article, and check out everyone's feedback on the forums, as others have found better ways to do things. If you do not have visual studio, you can do a command line compile if you wish, but I think you will still need some parts of visual studio or .net to at least compile the c#.

After it's compiled is when you load it into sql server - which you can do with sql server management studio.
Post #626370
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse