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 Sunday, January 11, 2009 10:38 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
Thank you for the help. I only have vwd 2008 and it wont work by following the article. I guess I'll have to purchase visual studio to get the stored procedure in my database unless someone knows of another way.
Thank you for the help.
Post #634361
Posted Sunday, January 11, 2009 8:49 PM
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 compile this using visual studio 2008 express? If not does any one know what I can use if I don't have visual studio 2005?
Post #634462
Posted Saturday, January 17, 2009 10:11 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
Does anyone know how to add this stored procedure using visual studio 2008?

any help would be appreciated.
Post #638719
Posted Saturday, January 17, 2009 10:54 AM
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
Have you tried the steps in the article? I haven't tried with 2008 but I cannot imagine it would be much different.
Post #638730
Posted Saturday, January 17, 2009 2:00 PM
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
Yes, I have tried but when I open a new solution there are no templates except "blank solution". I was hoping someone has created this stored procedure with visual studio 2008 and knew what to do next.

Thank you for any help.
Post #638770
Posted Sunday, January 18, 2009 10:52 AM
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
If you go to File, New Project, then you should have various options, including database projects, business intelligence projects, etc. If you do not see these options, stick in the SQL Server 2008 CD, and install Workstation Components, and make sure Business Intelligence Development Studio (BIDS) or whatever they're calling it now is installed. This will install the appropriate project types. Then Start, All Programs, Microsoft SQL Server, BIDS.
Post #638977
Posted Sunday, January 18, 2009 11:09 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
Thank you so much for the help. I'll give it a try.
Post #638986
Posted Tuesday, May 12, 2009 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 15, 2009 3:11 AM
Points: 1, Visits: 26
Hi,
How can I store the Latitude and Longitude results in a table, the stored procedure returns any value ?

Thanks
Post #714801
Posted Friday, December 30, 2011 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 11:52 AM
Points: 1, Visits: 59
Allen McGuire (6/26/2008)
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
%>


thanks for the code! works great! is it possible to do the same in aspx, c#? i'm not really familiar with .net.
Post #1228337
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse