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


Geocoding with SQL Server 2005


Geocoding with SQL Server 2005

Author
Message
zootie
zootie
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 48
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.



Allen McGuire
Allen McGuire
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 560
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
nik.hall
nik.hall
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 84
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
lima_2001
lima_2001
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: 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
Allen McGuire
Allen McGuire
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 560
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
Allen McGuire
Allen McGuire
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 560
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
lima_2001
lima_2001
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: 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?
mklausmeyer3
mklausmeyer3
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 50
Can I do this in sql server management express instead of visual studio?
mklausmeyer3
mklausmeyer3
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 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
scb
scb
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 175
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.
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