Watch Build the Pumpjack Pro Sample App Webinar
Results 1 to 7 of 7

Thread: Code for bulk geocoding

  1. #1
    Member
    Real Name
    Barry Jacobson
    Join Date
    Aug 2009
    Posts
    148

    Default Code for bulk geocoding

    Here's some code I've used for bulk geo-coding a lot of addresses. It's basically Martin's code from the Wiki, with a typo correction and some error-checking. I found that the sample did not deal well with records that had missing address. Also since most databases will not have a field with the complete address, it adds that step. Hope this is useful.

    ------------------------


    dim cn as sql::Connection
    flag = cn.open("::Name::YOURDATABASE")
    dim sql as c
    'execute a query to get the primary key of each record you want to update
    sql = "select ID from YOURTABLE"
    cn.Execute(sql)
    rs = cn.ResultSet

    dim flag as l

    'check to see if any rows in resultset
    flag = rs.nextRow()

    delete args
    dim args as sql::Arguments
    dim sql1 as c

    ' assumes you have fields latitude and longitude in your table
    sql1 = "select address, city, state, country, postalcode from YOURTABLE where ID = :primaryKey"
    sql2 = "update YOURTABLE set latitude = :lat, longitude = :lon where ID = :primaryKey"
    dim pa as p
    dim rs2 as sql::ResultSet
    dim fulladdress as c
    dim city as c
    while flag
    args.add("primaryKey",rs.data(1))
    flag = cn.Execute(sql1,args)
    rs2 = cn.ResultSet
    fulladdress = rs2.data("address")+ "," + rs2.data("city")+ "," + rs2.data("state")+ "," + rs2.data("country")
    city = rs2.data("city")
    if city <> ""
    pa = geocode_address(fulladdress,"Google")
    args.add("lat",pa.lat)
    args.add("lon",pa.lon)
    flag = cn.execute(sql2,args)
    end if
    flag = rs.nextRow()

    end while

  2. #2
    Member
    Real Name
    Dion McCormick
    Join Date
    Jun 2011
    Location
    Austin Texas
    Posts
    124

    Default Re: Code for bulk geocoding

    Thank you for your contribution. Great code!

  3. #3
    Member
    Real Name
    Tom Peterson
    Join Date
    Apr 2008
    Location
    Phoenix, MD
    Posts
    79

    Default Re: Code for bulk geocoding

    I have a grid in a tabbedui that shows our employee home addresses, phone numbers, etc. I created an alternate view to include the Google map component so that the employees home locations could be pinned on a map based on their address. Some problems I'm having include addresses that get mapped sometimes, but not all the time, some that don't get mapped at all, and I receive the Google query limit warning. I think I need to add latitude and longitude fields to my SQL Server DB and utilize the code above...which brings me to my questions.

    1. Can someone explain where the code should be placed, and how it is run? (action button?)
    2. Which SQL Server data type would be recommended to use for fields for Longitude and Latitude?

    Thanks very much.

  4. #4
    Member
    Real Name
    Tom Peterson
    Join Date
    Apr 2008
    Location
    Phoenix, MD
    Posts
    79

    Default Re: Code for bulk geocoding

    Still waiting on any responses to my questions, but in the meantime, I came across a good site (URL below) that converts addresses to longitude/latitude, either one at a time or in a batch mode.

    http://stevemorse.org/jcal/latlon.php

  5. #5
    Member
    Real Name
    Barry Jacobson
    Join Date
    Aug 2009
    Posts
    148

    Default Re: Code for bulk geocoding

    Hi. Sorry about the slow response. I run code in two different places:

    1) As a one-time event, I ran the code above on all my existing records to update them with latitude/longitude. I created a script (from the Control Panel) and ran it from there. (I did some tests first with a copy of the real table). I don't know that it really matters much on the data type on the back end. I used strings (varchar(50), and that works fine.

    2) To set these fields for new / updated records, I have short piece of code in the BeforeValidateExistingRecords and BeforeValidateNewRecords server events. The code is below. Hope this helps.

    -------------------------

    function BeforeValidateExistingRecords as v (DataSubmitted as P, Args as p, PageVariables as p)
    with PageVariables

    dim fulladdress as C
    dim coords as P

    fulladdress = DataSubmitted.address + " " + DataSubmitted.city + ", " + DataSubmitted.state + " " + DataSubmitted.country
    coords = geocode_address(fulladdress, "Google")
    if coords
    DataSubmitted.latitude = coords.lat
    DataSubmitted.longitude = coords.lon
    end if

    end with
    end function

  6. #6
    Member
    Real Name
    Tom Peterson
    Join Date
    Apr 2008
    Location
    Phoenix, MD
    Posts
    79

    Default Re: Code for bulk geocoding

    Barry, thanks a lot. I'm looking to working with your code later today...after my day job!

  7. #7
    Member
    Real Name
    Alexis Moore
    Join Date
    May 2017
    Posts
    1

    Default Re: Code for bulk geocoding

    Same here. Found this site called https://csv2geo.com.
    I even tested it. It does appear to accept bulk addresses in either flat file or via paste. I tested the free option, bu the paid is dirt cheap from what i saw.

Similar Threads

  1. Geocoding an address
    By Steve Workings in forum Application Server Version 11 - Web/Browser Applications
    Replies: 7
    Last Post: 10-05-2011, 01:21 PM
  2. Bulk Input
    By The Mage in forum Application Server Version 10 - Web/Browser Applications
    Replies: 6
    Last Post: 02-08-2011, 05:17 PM
  3. Geocoding of Addresses
    By RTRL in forum Archived Wishlist
    Replies: 0
    Last Post: 10-11-2009, 03:13 PM
  4. Requesting Geocoding information from website
    By George Corder in forum Alpha Five Version 5
    Replies: 2
    Last Post: 09-22-2004, 07:41 AM
  5. Bulk Email?
    By Keith Hubert in forum Alpha Five Version 5
    Replies: 5
    Last Post: 03-12-2003, 04:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •