|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Tuesday, May 12, 2009 9:46 AM
Points: 14,
Visits: 40
|
|
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.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, March 08, 2010 1:46 PM
Points: 19,
Visits: 150
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 01, 2009 2:32 AM
Points: 2,
Visits: 53
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 08, 2009 10:59 AM
Points: 2,
Visits: 37
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, March 08, 2010 1:46 PM
Points: 19,
Visits: 150
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, March 08, 2010 1:46 PM
Points: 19,
Visits: 150
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 08, 2009 10:59 AM
Points: 2,
Visits: 37
|
|
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?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, December 21, 2009 8:56 PM
Points: 8,
Visits: 43
|
|
| Can I do this in sql server management express instead of visual studio?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, December 21, 2009 8:56 PM
Points: 8,
Visits: 43
|
|
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
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Friday, August 21, 2009 12:15 PM
Points: 55,
Visits: 138
|
|
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.
|
|
|
|
