Geocoding with SQL Server 2005

Question

Post reply

Geocoding with SQL Server 2005

Viewing 14 posts - 46 through 58 (of 58 total)

You must be logged in to reply to this topic. Login to reply

Allen McGuire Mr or Mrs. 500 Points: 588 More actions · Answer 1

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

lima_2001 SSC Enthusiast Points: 167 More actions · Answer 2

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 SSC-Addicted Points: 404 More actions · Answer 3

Can I do this in sql server management express instead of visual studio?

mklausmeyer3 SSC-Addicted Points: 404 More actions · Answer 4

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 SSCommitted Points: 1859 More actions · Answer 5

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.

mklausmeyer3 SSC-Addicted Points: 404 More actions · Answer 6

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.

mklausmeyer3 SSC-Addicted Points: 404 More actions · Answer 7

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?

mklausmeyer3 SSC-Addicted Points: 404 More actions · Answer 8

Does anyone know how to add this stored procedure using visual studio 2008?

any help would be appreciated.

scb SSCommitted Points: 1859 More actions · Answer 9

Have you tried the steps in the article? I haven't tried with 2008 but I cannot imagine it would be much different.

mklausmeyer3 SSC-Addicted Points: 404 More actions · Answer 10

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.

scb SSCommitted Points: 1859 More actions · Answer 11

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.

mklausmeyer3 SSC-Addicted Points: 404 More actions · Answer 12

mklausmeyer3

SSC-Addicted

Points: 404

January 18, 2009 at 11:09 am

#928935

Thank you so much for the help. I'll give it a try.

juanvi SSC Rookie Points: 49 More actions · Answer 13

Hi,

How can I store the Latitude and Longitude results in a table, the stored procedure returns any value ?

Thanks

proleget Grasshopper Points: 13 More actions · Answer 14

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.