Post reply

Geocoding with SQL Server 2005

  • July 31, 2008 at 7:21 pm

    #851617

    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

  • August 1, 2008 at 6:00 am

    #851802

    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?

  • October 12, 2008 at 9:07 am

    #883901

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

  • December 28, 2008 at 11:53 am

    #918176

    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

  • December 28, 2008 at 6:25 pm

    #918224

    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.

  • January 11, 2009 at 10:38 am

    #925006

    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.

  • January 11, 2009 at 8:49 pm

    #925100

    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?

  • January 17, 2009 at 10:11 am

    #928699

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

    any help would be appreciated.

  • January 17, 2009 at 10:54 am

    #928710

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

  • January 17, 2009 at 2:00 pm

    #928745

    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.

  • January 18, 2009 at 10:52 am

    #928927

    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.

  • January 18, 2009 at 11:09 am

    #928935

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

  • May 12, 2009 at 3:25 am

    #992702

    Hi,

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

    Thanks

  • December 30, 2011 at 6:42 am

    #1426925

    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.

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

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