Alpha Video Training
Results 1 to 17 of 17

Thread: Geocode

  1. #1
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Geocode

    Hi All

    I'm looking for a way to bulk geocode addresses stored in a dbf table using the geocode_address() function. In the table I have the usual address fields and a set of latitude and longitude fields. (Please see field names below)

    I've tested the sample code from Alphapedia and it works great, just not sure how to best implement the code into my database.

    Any suggestions on how to best use the geocode function in order to bulk generate the long and lat? Or if I can be done on either on a form or through the field rule events?

    Code:
    Svc_Add1
    Svc_Add2
    Svc_Unit
    Svc_City
    Svc_State
    Svc_Zip	
    Svc_Cntry
    Lat	
    Long

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Geocode

    I'd have to test tomorrow but given the geocode_address() function you should be able to use an update operation.

    update the lat field with something like

    geocode_address(alltrim(Svc_Add1)+", "+alltrim(Svc_City)+", "+alltrim(Svc_State), "Google").lat

  3. #3
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    Thanks Stan!

    That seems to work great.

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Geocode

    Thought it would. I assume you did the same for the long field?

    Once you have current records populated you could change the lat and long fields to calculated and make the calculation expression

    geocode_address(alltrim(Svc_Add1)+", "+alltrim(Svc_City)+", "+alltrim(Svc_State), "Google").lat
    geocode_address(alltrim(Svc_Add1)+", "+alltrim(Svc_City)+", "+alltrim(Svc_State), "Google").lon

    to have them automatically populated for new records.

  5. #5
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    Yeah...I did the same for the long field and it works great.

    I changed the lat and long fields to calculated and used your expression, however I got the below error while trying to save the field rules.

    Capture.JPG

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Geocode

    I suppose the field calculation rule doesn't have time to perform the internet connection and return a result.

    You might try a form level cansave event script to populate the fields assuming new record entry is through a form.

  7. #7
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    I'm thinking about just modifying my save button on the form to run the calculation.

    Slightly off topic....But can you perform a geographic search on the google map alternative view using a DBF? The training video shows the location search field using a SQL database, however I can not see the option on my DBF set up.

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Geocode

    I've never seen the video so I couldn't say.

  9. #9
    Member
    Real Name
    Charlain Fondren
    Join Date
    Oct 2002
    Posts
    345

    Default Re: Geocode

    I'm trying to update my database and add the latitude and longitude for the addresses using the above function and syntax suggested by Stan. It's not working for me reliably. I put the function in an update operation and ran it on a few records and it worked. Now when I try to run the exact same update operation on more records, it doesn't work anymore. I'd appreciate it very much if someone else would try this and let me know if it is working for you or if it's a problem with the function itself.

    UPDATE: Changed the service from google to nominatim and it worked perfectly, so the problem is with google. Hope this information will help someone else in the future.

    UPDATE 2: Back to square one, formula not working again :( any ideas?
    Last edited by Charlain; 04-10-2013 at 01:17 PM.

  10. #10
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    This code has also stopped working for me too.

    I've also tried using the test example provided wiki.alpha.

    Code:
    dim address as C = "70 Blanchard Rd, Burlington, MA"
    ?geocode_address(address, "Google")
    This does not seem to be working either.

    Would someone mind trying to rum this script to see if this an issue with my system or with Alpha Five?

    Thanks

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Geocode

    dim address as C = "70 Blanchard Rd, Burlington, MA"

    ?geocode_address(address, "Google")
    = error_code = 0
    error_text = ""
    lat = "42.4796040"
    lon = "-71.2032800"
    There can be only one.

  12. #12
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    539

    Default Re: Geocode

    Worked for me

    = error_code = 0
    error_text = ""
    lat = "42.4796040"
    lon = "-71.2032800"

  13. #13
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    Thanks for checking!

    The fault must be on my end.

    By any chance is this function tied in with Internet Explorer?

  14. #14
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    539

    Default Re: Geocode

    Quote Originally Posted by ADCO Service View Post

    By any chance is this function tied in with Internet Explorer?
    Most likely.

    I think Google has a limit on the number of times you can make a request per day

  15. #15
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    Have only tried to run a couple of hundred addresses in the past two days.

    Zero results have been generated.

    Thinking the issue might be related to some Anti Virus updates created by our IT company.

  16. #16
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Geocode

    It's strange that my results are blank.

    Code:
    dim address as C
    address = "70 Blanchard Rd, Burlington, MA"
    ?geocode_address(address, "Google")
    = error_code = 0
    error_text = ""
    lat = ""
    lon = ""

  17. #17
    Member
    Real Name
    Gidiel Gutierrez
    Join Date
    Aug 2013
    Location
    BC, Canada
    Posts
    9

    Default Re: Geocode

    I don't have any problem right now. But a while ago, i was using the same method with Filemaker to get geocode from Google until one day all the result was blank. I have waited couple of weeks but still blank. Finally, i have worked with XML parsing method and so far it's working, it's just too much action script for me.

    Is there any alternative way for Alpha Five to geocode from Google, XML maybe?

    Thanks!

Similar Threads

  1. Geocode search on alternative view google map
    By Islip in forum Application Server Version 11 - Web/Browser Applications
    Replies: 7
    Last Post: 03-07-2012, 04:11 PM
  2. Geocode "LOCATION" field question
    By llweed in forum Application Server Version 11 - Web/Browser Applications
    Replies: 2
    Last Post: 01-31-2012, 08:21 PM
  3. Geocode "LOCATION" field question
    By llweed in forum Alpha Five Version 11 - Desktop Applications
    Replies: 2
    Last Post: 01-22-2012, 10:08 AM

Bookmarks

Posting Permissions

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